BudsSpud
08-18-2021, 05:13 PM
Hoping someone can help! First time doing Excel macros, have copied logic from the web and it worked fine however I need to expand it. Here goes ...
Spreadsheet column E has data validation from a list of 5 text values. If E is "Misrouted", I want to prompt for text in F and G. If E is "Other", I want to prompt for text in H.
What I copied from the web got me to prompt for text in F. I haven't had success prompting for text in G and H.
What I am attempting is below. I receive a Do without Loop error when I step into the line highlighted.
Any guidance is appreciated! And yes, I am a newbie to macros ... :yes
Thanks in advance.
++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If
Dim com As String
Dim comm1 As String
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("E2:E5000"))
If isect Is Nothing Then
ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter team in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Team to Route to?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter reason for team in " & Target.Offset(0, 2).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Why?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
ElseIf Target.Value = "Other" Then
com = "Enter Other details in " & Target.Offset(0, 3).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Other details", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1
Else
Target.Offset(0, 1).Value = "" 'Remove this line if not desired
End If
End Sub
Spreadsheet column E has data validation from a list of 5 text values. If E is "Misrouted", I want to prompt for text in F and G. If E is "Other", I want to prompt for text in H.
What I copied from the web got me to prompt for text in F. I haven't had success prompting for text in G and H.
What I am attempting is below. I receive a Do without Loop error when I step into the line highlighted.
Any guidance is appreciated! And yes, I am a newbie to macros ... :yes
Thanks in advance.
++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If
Dim com As String
Dim comm1 As String
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("E2:E5000"))
If isect Is Nothing Then
ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter team in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Team to Route to?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter reason for team in " & Target.Offset(0, 2).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Why?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
ElseIf Target.Value = "Other" Then
com = "Enter Other details in " & Target.Offset(0, 3).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Other details", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1
Else
Target.Offset(0, 1).Value = "" 'Remove this line if not desired
End If
End Sub