Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input validation using VBA
I am running two input validation rules, so I am running one using the input
validation feature in Excel, and one using VBA. I have a couple of questions regarding the VBA run one. I am using the following code and it is working fine, but I want to make a couple of changes. Instead of have the limit as the number 7 I would like to use numbers from the spreadsheet. The numbers are dynamic so can't be hard coded into the VBA script, and the numbers are in the adjacent cells (B6:B15). So for example I want C7 to be greater than B6. Can any one offer any advice? My code is below. Thank you. R. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C6:C15" On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < 7 Then MsgBox "Invalid value, correct it" End If End With End If ws_exit: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input validation using VBA
Try this
from If .Value < 7 Then to If .Value < .offset(0,1).value Then this will compare the number against the cell immediately to the right in colun C. "VoxBox-Richard" wrote: I am running two input validation rules, so I am running one using the input validation feature in Excel, and one using VBA. I have a couple of questions regarding the VBA run one. I am using the following code and it is working fine, but I want to make a couple of changes. Instead of have the limit as the number 7 I would like to use numbers from the spreadsheet. The numbers are dynamic so can't be hard coded into the VBA script, and the numbers are in the adjacent cells (B6:B15). So for example I want C7 to be greater than B6. Can any one offer any advice? My code is below. Thank you. R. Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "C6:C15" On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value < 7 Then MsgBox "Invalid value, correct it" End If End With End If ws_exit: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is input validation? | Excel Discussion (Misc queries) | |||
Input Validation using VBA | Excel Programming | |||
Data Input Validation | Excel Discussion (Misc queries) | |||
Help with VBA Input Validation | Excel Programming | |||
validation of input in textbox | Excel Programming |