Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet validation
Is there a way to set column "D" on a sheet to only accept (or automatically
round) a value in 0.50 increments? Examples: 25.5 < ok 100 < ok 18.50 < ok 0.5 < ok 25.25 < invalid 35.3 < invalid How can I do this? Thanks much in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet validation
Try this
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then With Target If IsNumeric(.Value) Then .Value = Round(.Value / 0.5, 0) * 0.5 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. -- HTH RP (remove nothere from the email address if mailing direct) "quartz" wrote in message ... Is there a way to set column "D" on a sheet to only accept (or automatically round) a value in 0.50 increments? Examples: 25.5 < ok 100 < ok 18.50 < ok 0.5 < ok 25.25 < invalid 35.3 < invalid How can I do this? Thanks much in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spreadsheet validation
"quartz" wrote in message ... Is there a way to set column "D" on a sheet to only accept (or automatically round) a value in 0.50 increments? Examples: 25.5 < ok 100 < ok 18.50 < ok 0.5 < ok 25.25 < invalid 35.3 < invalid How can I do this? Thanks much in advance I don't know how to do this on the entire column, but I can show you how to do this for a sinle cell like D1 1) Select cell D1 2) Open Data|Validation 3) Select Settings and Custom 4) Enter this =IF(OR(0 = D1 - INT(D1), 0.5 = D1-INT(D1)), True, false) Unfortunately, I have the Swedish version of Excel so my translations may not be correct. This function will work with negative values as well. /Fredrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation On A Cells With Data From A Different Spreadsheet | Excel Discussion (Misc queries) | |||
Validation - removal of over an entire spreadsheet | Excel Discussion (Misc queries) | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming |