Format number based on input value

[l1011driver]

Thank you in advance for any help. I'm a novice so no help is too remedial for me.

I have a worksheet in which I'd like to format a cell such that the number entered is formatted based on the input value.

If the entered value is between 2500 and 3500 I would like for Excel to return that number with a decimal inserted to two decimal places. For example, if I enter "3015" I'd like Excel to return the value "30.15". If I enter "3000" I'd like Excel to return the value "30.00" (as opposed to "30").

If the entered value is between 700 and 1500 I would like for Excel to return that number. For example, if I enter "1025" I'd like Excel to return the value "1025". If I enter "987" I'd like Excel to return the value "987".

The entered numbers will be only positive integers in these number ranges. Numbers outside these ranges would be rejected. This would apply to only one cell in one worksheet.

Thanks in advance for any help anyone might be.

Sincerely,

l1011driver

 

[Peter_SSs]

However, when I enter a number between 700 and 1500 it returns as xxx.00. For example, if I enter 985 it returns as 985.00.

Ah yes, if you enter a 2500-3500 number Jack's code sets the cell format to display the 2 decimal places. If you then enter a 700-1500 his code does not reset the number format to remove those decimal places. I'll leave that to him to offer the appropriate amendment.

In the meantime, here is an alternative code you may wish to consider. If you do, you need to remove the other code as you cannot have two Worksheet_Change codes.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("BJ31")) Is Nothing Then
    Application.EnableEvents = False
    With Range("BJ31")
      Select Case True
        Case .Value >= 700 And .Value <= 1500
          .NumberFormat = "#,##0"
        Case .Value >= 2500 And .Value <= 3500
          .NumberFormat = "0.00"
          .Value = .Value / 100
        Case Else
          If Len(.Value) Then
            MsgBox "Invalid entry, try again"
            .Select
            .Value = vbNullString
          End If
      End Select
    End With
    Application.EnableEvents = True
  End If
End Sub

How do I get around have to F5 the Application.EnableEvents every time? If I exit out of the file and open it back up it doesn't work again. If I do the F5 deal then it works until I close the file again.

I'm not sure what would be causing that. It doesn't appear to be anything in Jack's code that could cause that. Perhaps you should check the other macro code(s) that you said you have in case they are disabling events.

 

[l1011driver]

Dear Jack,

I did find code to disable events in another macro. I changed that. Your code is great. Now everything works perfect...until I protect the worksheet.

This is the same worksheet in which I enter the 4-digit number we've been working on. I protect this sheet because there are only certain cells in which I want the user to be able to enter data. I used cell formatting to lock and unlock the cells I want. Most of these unlocked cells are merged cells because of the length of the data that needs to be entered.

As I said, when the worksheet is unprotected everything works perfectly. When I protect the worksheet and attempt to enter the 4-digit number I get an error message dialogue box with the message "Unable to set the NumberFormat property of the Range class". I click on "Debug" and the screen returns to where I've inserted your code. The following code is highlighted in yellow:

.NumberFormat = "0.00"

When I enter a number between 700 and 1500 I get the same response, except the code highlighted in yellow is:

.NumberFormat = "###0" (I deleted your thousands separator comma as I just want a 4 digit number)

Again, unprotected it works exactly as I want it to.

Any ideas? Is there any other way to allow a user to only select certain cells on a worksheet besides using cell formatting and then protecting the worksheet? There's a bunch of cells scattered all over. It wouldn't be as simple as selecting a range of cells to unlock.

Thanks again for all your help.

Sincerely,

Mark

 

[Peter_SSs]

Dear Jack,

For the moment I'll assume that you are actually addressing me, not Jack.

Could you manually unprotect the sheet, then protect it again but as you are re-protecting it tick the box that says (Allow all users of this worksheet to Format Cells ?

 

[JackDanIce]

Been away from the board, just picking up these threads. Try this, change parts in blue to fit your needs:

Private Sub Worksheet_Change(ByVal Target As Range)  
    
    If LenB(Target.Value) = 0 Then Exit Sub
    
    If Not Intersect(Target, Range("BJ31:BL31")) Is Nothing And Target.Cells.Count = 1 Then
        
        Application.EnableEvents = False
        
        ActiveSheet.Unprotect "password"
        
        With Target
            Select Case .Value
            
                Case 700 To 1500
                    .NumberFormat = "###0"
                    
                Case 2500 To 3500
                    .Value = .Value * 0.01
                    .NumberFormat = "#.00"
                
                Case Else
                    .ClearContents
                    MsgBox "Invalid entry, please try again", vbExclamation, "Invalid Entry"
                    
            End Select
        End With
        
        ActiveSheet.Protect "password"
        
        Application.EnableEvents = True
        
    End If
    
End Sub

 

[l1011driver]

Hi Peter,

My apologies, sir. Yes, it was you I intended to address. Typing while thinking about something else is a dangerous thing.

I did as you suggested and it solved the numerical entry problem, but now the macro that I have for changing characters entered in lower case letters to caps errors out. In my worksheet I have two cells that require letters instead of numbers. I want the entered letters to be changed to all caps. The macro I've been using has worked fine until now.

Do I need a different macro for all caps?

Thanks for your help.

Jack...er...I mean...Mark (heehee)

 

[JackDanIce]

Mark, you need to appreciate, neither Peter, nor myself or anyone else reading your posts, can see your spreadsheet or your PC monitor, so it is guesswork, based only on the details you provide.

If there are other macros or events that interact, you need to provide details or at least be more specific.

Characters entered in lower case letters to caps errors out. In my worksheet I have two cells that require letters instead of numbers. I want the entered letters to be changed to all caps. The macro I've been using has worked fine until now.

What macro?
What is the code?
Where is the code (in a module, a worksheet event trigger)?
What two cells and and what are their addresses?
Are the two cells merged?
Is it code that you can't insert into this code or unsure how to?

As Peter points out, you can only have a single Worksheet_Change event in a sheet module so I'd infer you'll need to merge the two pieces of code, but it's a guess only.

 

[l1011driver]

Hi Jack,

Thanks for this. I'll try it and maybe it'll solve my all caps issue, too.

Standby...

Mark

 

[l1011driver]

Jack and Peter,

Understand, and I do appreciate that fact. It's one thing that amazes me how you guys are able to come up with fixes for this without ever having seen it. Up until now I've never tried to manipulate the numeric output of a cell like this, so this is number thing is a new deal. I've gotta be honest I'm a bit perplexed at how it all interacts, and it never occurred to me that two separate events requiring two completely different types of outputs (letters and numbers) would need to. I know so little about VBA in Excel that I don't even know what I don't know.

I sincerely appreciate the effort both of you have devoted to me and if I can make anything easier I'd be delighted to.

Thanks for your gentle guidance. It's much appreciated.

Yours,

Mark

 

[l1011driver]

Hi Jack and Peter,

Here is my code for upper case letters. I can't seem to find a way to paste it in a "code window" like you guys do so the presentation isn't as nice. I'm calling each
"routine" in this thing a macro. It seems there may be more than one term that applies. I confess ignorance.

This code is in module 1 of my workbook. The name of the worksheet that both the number entries we've been working on, and now the all caps, appears in is named
"A340-500 W&B". This is the only worksheet the pilots will work off of. There are two cells that the pilot is to enter the 4-letter airport identifier of the departure airport and the destination airport. Each of these cells consists of merged cells. The cell addresses on the worksheet are: AF10:AK10 (merged together) for the departure airport and AO10:AT10 (merged together) for the destination airport.

The error I mentioned in the uppercase letter macro(?) occurs in a line of the ChangeCell() subroutine. The line:

x.Value = UCase(x.Value)

is highlighted in yellow.

I hope this is complete enough information to be of the most help to you. If you need any more just let me know.

Sincerely,

Mark

I imagine the all caps code could be inserted into the code for the numbers that you guys have graciously provided to me since both events occur on the same worksheet as a result of operator input, but I wouldn't have any idea how to begin to do it.


Sub auto_open()

'Run the macro UppercaseCell any time an entry is made in the Departure and Destination cells in A340-500 W&B worksheet.

ThisWorkbook.Worksheets("A340-500 W&B").OnEntry = "UppercaseCell"


End Sub

Sub UppercaseCell()

ActiveSheet.Unprotect Password = "jobowen518"

Dim KeyCells As String

' Define which cells should trigger the ChangeCell macro.

KeyCells = "AF10:AO10"

' If the Activecell is one of the key cells, call the ChangeCell macro.

If Not Application.Intersect(ActiveCell, Range(KeyCells)) _
Is Nothing Then ChangeCell

ActiveSheet.Protect Password = "jobowen518"

End Sub

Sub ChangeCell()

Dim Cell As Object

For Each x In Range("AF10:AO10")

' Change the text in the range to uppercase letters.

x.Value = UCase(x.Value)

Next

End Sub

 

[JackDanIce]

I believe this is the only code you need in worksheet object Sheets("A340-500 "&B") and so you shouldn't need procedures UppercaseCell and ChangeCell (again based on details provided):

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim x As Long
    
    Application.EnableEvents = False
        
    ActiveSheet.Unprotect "jobowen518"
    
    If LenB(Target.Value) = 0 Then Exit Sub
    
    'Format numbers in Range("BJ31:BL31")
    If Not Intersect(Target, Range("BJ31:BL31")) Is Nothing And Target.Cells.Count = 1 Then
        
        With Target
            Select Case .Value
            
                Case 700 To 1500
                    .NumberFormat = "###0"
                    
                Case 2500 To 3500
                    .Value = .Value * 0.01
                    .NumberFormat = "#.00"
                
                Case Else
                    .ClearContents
                    MsgBox "Invalid entry, please try again", vbExclamation, "Invalid Entry"
                    
            End Select
        End With
        
    End If
        
    'Change AF10 to AO10 to upper case
    For x = Range("AF10").colulmn To Range("AO10").Column
        Cells(10, x).Value = UCase(Cells(10, x).Value)
    Next x
        
    ActiveSheet.Protect "jobowen518"
        
    Application.EnableEvents = True
            
End Sub