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

 

[JackDanIce]

So the format issue can be fixed within the code, but it does seem like you're placing the code into the correct object (yes I meant sheet objects), which works for me on my test spreadsheet, so can't accurately tell why it's not working for you.

Here's the code modified to show 2 decimal places:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim blerr As Boolean
    
    If LenB(Target.value) = 0 Then Exit Sub
    
    If Not Intersect(Target, Range("A1:A10")) Is Nothing And Target.Cells.count = 1 Then
                
        If Target.value >= 2500 And Target.value <= 3500 Then
            Application.EnableEvents = False
            With Target
                .value = Target.value * 0.01
                .NumberFormat = "#.00"
            End With
            Application.EnableEvents = True
            Exit Sub
        End If
        
        If Target.value < 700 Then
            blerr = True
        ElseIf Target.value > 3500 Then
            blerr = True
        ElseIf Target.value > 1500 And Target.value < 2500 Then
            blerr = True
        End If
        
        If blerr Then
            MsgBox "Invalid entry, try again"
        End If
        
    End If

End Sub

 

[Peter_SSs]

I'm a bit confused.

1. You have asked about "formatting" a cell. Formatting a cell (number) does not change the underlying number, it just displays it a certain way. So when you enter 3015 in a cell do you want that cell ..
a) to actually still contain 3015 but have it look like 30.15, or
b) to be changed to the new number 30.15?

In other words, if that cell is A1 and we later use this in another cell
=A1+2
do we expect to get 3017 or 32.15?



2.

Also, if you type in a number that's divisible by 100, like you typed in 26, ...

a) 26 is not divisible by 100
b) 26 is outside the ranges you said would be the only ranges allowed 700-1500 and 2500-3500

 

[l1011driver]

Hi Peter,

Thank you as well for your assistance.

I apologize for confusing you. What I was so clumsily trying to say is that if you type in a number divisible by 100, such as 2600, I don't want Excel to return "26". I want it to return "26.00" and change the value of the input to that output number. So in the scenario you describe I would want A1 + 2 to equal 32.15.

Appreciate your considering my problem.

Mark

 

[l1011driver]

Hi JackDanIce,

Well, I'm glad to know I pasted the code in the proper place. I did paste your new code you sent above in the same place. It just doesn't seem to work. It's like Excel is not finding the macro. When I enter numbers now I get no response at all. It's no different that entering a number into a blank cell.

When I enter a number outside of the defined range I don't get an error message either. That further reinforces my feeling that for some reason it's not seeing the macro.

I gotta be doing something wrong.

Thanks for your help.

Mark

 

[Peter_SSs]

.. if you type in a number divisible by 100, such as 2600, I don't want Excel to return "26". I want it to return "26.00" and change the value of the input to that output number. So in the scenario you describe I would want A1 + 2 to equal 32.15.

Then I think Jack's code does what you want.

When I enter numbers now I get no response at all.

1. Do you have macros enabled?

2. If you do, it is possible that your 'Events' have become disabled. In the Visual Basic window use the menu to Insert -> Module. Copy/Paste the code below into that new module, place your cursor anywhere within those 3 lines of code and press F5 to run it. Now go back to your sheet and try entering a number again.

Sub EE()
  Application.EnableEvents = True
End Sub

3. You mentioned that you altered the code to use the range appropriate for your sheet. Please post that altered line of code back here in the thread so that we ..
a) Can check in case there is a problem with it
b) Know what range you are dealing with

 

[l1011driver]

Hi Peter,

Thanks again for your help. I did as you suggested with your 3 lines of code. I was confident that macros were enabled because I have macro that turns lower case letters into all caps that runs just fine, but I did as you suggested anyway.

I changed Jack's code as follows:

If Not Intersect(Target, Range("BJ31:BL31")) Is Nothing And Target.Cells.count = 1 Then


The cell range is the one and only thing I changed in Jack's code. That range is a set of 3 merged cells.

I will try to describe what happens:

1. I go to the module where I entered your 3 lines of code and press F5 as you described.
2. I return to the worksheet and enter "3000" in the cell.
3. I press "Enter". The screen display returns to the module containing your 3 lines of code and an error message dialogue box appears.
The message says "Unable to set the NumberFormat property of the Range class"
4. I click on "debug". The screen comes up showing my Excel objects sheet where I have Jack's code pasted. The line of Jack's code reading:

.NumberFormat = "#.00"

is highlighted in yellow.

5. I return to the worksheet where I entered "3000" and it just shows "30".

Now, if I do the same process as described above but I instead enter a number not divisible by 100, such as 2995, I get all the same
error messages but upon returning to my worksheet the cell will show "29.95" just like I want it to.

If I enter a number such as 3000 on my worksheet without first pressing F5 on your 3 lines of code nothing happens. The number that I enter
just remains as if you were entering a number in a blank cell.

This is the closest I've came to getting it to work. I hope this helps you troubleshoot. I can't thank you enough for your time.






Mark

 

[Peter_SSs]

That range is a set of 3 merged cells.

That is critical! Merged cells and vba do not sit well together.
You may be interested to read some other views on this.

Try

If Not Intersect(Target, Range("BJ31")) Is Nothing And Target.Cells.Count = 1 Then

 

[JackDanIce]

@Peter_SSs those pesky darn merged cells!

 

[l1011driver]

Dear Jack and Peter,

I did as you said and now a number between 2500 and 3500 works exactly as I want it to. Even 3000 returns as 30.00. It's perfect!

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. For numbers between 700 and 1500 I want them to retain their entered value, i.e. if I enter 985 I want Excel to return it as 985.

The error message works perfectly, too!

I can't believe how well it works! You guys are amazing!

Thanks so much...

Mark

 

[l1011driver]

Another question:

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.

If we can solve this and the numbers between 700 and 1500 that'll wrap this one up.

Thanks again for your help!

Sincerely,

Mark