Hello!
I wonder if someone could advise me.
I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.
Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long
For i = 1 To 31
If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then
MsgBox "Please enter values in all required fields...", vbOKOnly, "User Error"
Target.Offset(0, -8).Activate
Else
UserForm1.Show
End If
End If
Next i
End Sub
Display More
The code works fine. However, it must loop 31 times each time a cell is selected. I'm trying to find a better way. I tried the following, but there seems to be a limit of 24 ranges.
Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect _
(Target, Range("StatPost1,StatPost2,StatPost3,StatPost4,StatPost5,StatPost6,StatPost7,StatPost8,StatPost9,StatPost10,StatPost11,StatPost12,StatPost13,StatPost14,StatPost15,StatPost16,StatPost17,StatPost18,StatPost19,StatPost20,StatPost21,StatPost22,StatPost23,StatPost24,StatPost25,StatPost26,StstPost27,StatPost28,StatPost29,StatPost30,StatPost31")) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then
MsgBox "Please enter values in all required fields...", vbOKOnly, "User Error"
Target.Offset(0, -8).Activate
Else
UserForm1.Show
End If
End If
End Sub
Display More
I would appreciate any advice given.
Thanks,
Mac