Worksheet_SelectionChange

RichardU

Board Regular
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not ActiveCell = Range("I11") Then
VendorLength = Len(Range("I11").Value)
If VendorLength = 0 Then Exit Sub
If VendorLength <> 6 Then
MsgBox Prompt:="Vendor Number must be 6 digits long!!!", Buttons:=vbOKOnly, Title:="Vendor Number"
Range("I11").Value = ""
Range("I11").Select
End If
If VendorLength = 6 Then
CharacterStart = InStr(1, Range("I11").Value, 8, 1)
If CharacterStart = 1 Then Exit Sub
MsgBox Prompt:="The Vendor Number must start with an 8" & Chr(13) & "e.g." & Chr(13) & "800001", Buttons:=vbOKOnly, Title:="Vendor Number"
Range("I11").Value = ""
Range("I11").Select
End If
End If

If Not ActiveCell = Range("I12") Then
BankAccountNumber = Range("I12").Value
BankAccountNumberLength = Len(BankAccountNumber)
If BankAccountNumberLength = 0 Then Exit Sub
If BankAccountNumberLength <> 8 Then
MsgBox Prompt:="Bank Account Number must be 8 digits long!!!", Buttons:=vbOKOnly, Title:="Bank Account Number"
Range("I12").Value = ""
Range("I12").Select
End If
End If

End Sub``````

I am using this code but i want it to work if the user selects any other cell, at the moment it does work for the vendor number but the bank account number only works if they click in cell I11 not in every other cell???

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Lewiy

Well-known Member
I think that the Worksheet_Change event would be more suitable, but either way, give this a try:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Len(Target.Value) <> 6 And Len(Target.Value) <> 0 Then
MsgBox "Vendor number must be 6 digits long"
Range("I11") = ""
Range("I11").Select
End If
If Left(Target.Value, 1) <> "8" Then
Range("I11") = ""
Range("I11").Select
End If
End If
If Len(Target.Value) <> 8 And Len(Target.Value) <> 0 Then
MsgBox "Bank Account number must be 8 digits long"
Range("I11") = ""
Range("I11").Select
End If
End If
End Sub``````

Lewiy

Well-known Member
Incidentally, the reason that the bank account part of your code was not working is because it never reached that section of the code due to the "Exit Sub" statements above it which would always fire in the event that the Bank Account section was required, thus stopping the macro before it got there.

Replies
5
Views
598
Replies
0
Views
205
Replies
15
Views
742
Replies
2
Views
1K
Replies
0
Views
854

1,190,677
Messages
5,982,211
Members
439,768
Latest member
loukrs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back