Solved: Max 2 decimals allowed in cell

[mikeburg]

Need help with sheet change event VBA that when a number is entered in range F71 thru O74, no more than two decimals will be allowed. In other words

586.36 allowed
583.00 allowed
586.366 not allowed
586.0336 not allowed
etc.

When an unallowed number is entered, have msgbox to warn of 2 decimal max, erase the unallowed number, & return cursor back to data cell.

Thanks for your help. mikeburg

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F71:O74" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If Not Int(.Value * 100) = .Value * 100 Then

MsgBox "Value can have max of 2 dec. places"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[anandbohra]

& return cursor back to data cell.

Thanks for your help. mikeburg

add this line

Target.Activate

after

.Value = ""

[Bob Phillips]

add this line

Target.Activate

after

.Value = ""

As I don't move from Target, I don't see that as necessary

[mdmackillop]

Hi Bob,
If the OP uses "Move Selection after Enter" as I do, then the return line is required.

[anandbohra]

Hi Bob,
If the OP uses "Move Selection after Enter" as I do, then the return line is required.

same in my case that's why I suggested above line.
:yes

[Bob Phillips]

Oh yeah, I always forget that as I turn it off. I can't understand why anyone could possibly want that.

[mikeburg]

Thanks a million. You guys are a life saver to those of us who are still learning &/or not able to work with VBA often enough to quickly master it!

By the way, can someone help me mark this thread as solved?

mikeburg

[mikeburg]

Opps, why do I get the message box when I enter 609.33?

I can't figure why. Can someone help? mikeburg

[mdmackillop]

No obvious reason. Can you post a sample workbook?

[mikeburg]

I've never attached an excel file before, hope this works. However, it's a simple blank sheet with the sheet code copied to it from this thread.

I get the same problem of getting the message using .07, .14, .28, .29, .55, .56, .57, .58, 1.09, etc.

I am using XP (2002) version of excel.

Thanks for help. mikeburg

1344

[mdmackillop]

It's a basic problem in that 0.7 is not seen as exact.
Add the line

Range("K10") = Int(.Value * 100) - .Value * 100

and for 0.07 you get -8.88178419700125E-16

so try

If Abs(Int(.Value * 100) - .Value * 100) > 10 ^ -12 Then

[mikeburg]

Thanks for the additional VBA. It works on .07, .14, .28, .55, .56. It still does the same thing on .29, .57, .58, 609.33, & possible other numbers I have not tested.

Any other ideas? Thanks a bunch. mikeburg

[Bob Phillips]

Can you post the whole workbook so that we can see it?

[mikeburg]

It's already posted in this thread on my reply yesterday at 5:16 PM.

However, I think I have found my answer. I used:

If Len(Target) - Application.Find(".", Target) > 2 Then

Do you see anything wrong with this? I wish I was as good as you guys with VBA. mikeburg

[Bob Phillips]

Try this



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "F71:O74" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If .Value * 100 - Int(.Value * 100) > 0.000001 Then

MsgBox "Value can have max of 2 dec. places"
.Value = ""
.Activate
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub