View Full Version : Solved: Max 2 decimals allowed in cell
mikeburg
07-08-2009, 12:33 PM
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
07-08-2009, 01:49 PM
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
07-08-2009, 11:11 PM
& return cursor back to data cell.
Thanks for your help. mikeburg
add this line
Target.Activate
after
.Value = ""
Bob Phillips
07-09-2009, 01:45 AM
add this line
Target.Activate
after
.Value = ""
As I don't move from Target, I don't see that as necessary
mdmackillop
07-09-2009, 02:18 AM
Hi Bob,
If the OP uses "Move Selection after Enter" as I do, then the return line is required.
anandbohra
07-09-2009, 02:28 AM
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
07-09-2009, 02:28 AM
Oh yeah, I always forget that as I turn it off. I can't understand why anyone could possibly want that.
mikeburg
07-12-2009, 03:30 PM
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
07-13-2009, 01:03 PM
Opps, why do I get the message box when I enter 609.33?
I can't figure why. Can someone help? mikeburg
mdmackillop
07-13-2009, 01:33 PM
No obvious reason. Can you post a sample workbook?
mikeburg
07-13-2009, 03:16 PM
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
07-13-2009, 03:28 PM
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
07-14-2009, 11:18 AM
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
07-14-2009, 11:54 AM
Can you post the whole workbook so that we can see it?
mikeburg
07-14-2009, 02:41 PM
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
07-14-2009, 03:05 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.