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

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