Hello,
I am trying to create a shared document that will have multiple users. There are 10 columns, 7 have data validation drop down lists and 3 are manual entry. Each manual entry column information is dependent on the data validation list in the column directly to it's left. I am trying to write the code that produces a pop up box requiring the manual entry information to be typed when the information in the data validation list is selected. I have been successful when applying it to just one of the data columns and manual entry columns. When I try to expand the code to the second data validation list and manual entry column, I receive an error. I have pasted the code below. All the column numbers, ranges, and subsequent data entry list values are present and correct. My question is, how do I apply this to multiple columns that are independent of each other? I am new to coding. Thanks!
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
Set isect = Application.Intersect(Target, Range("C3:C225"))
If isect Is Nothing Then
Else
If Target.Value = "1st" Or Target.Value = "2nd" Or Target.Value = "3rd" Then
com = "Enter comment in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1
Else
Target.Offset(0, 1).Value = ""
End If
End If
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If
Dim com As String
Dim comm1 As String
Set isect = Application.Intersect(Target, Range("H3:H225"))
If isect Is Nothing Then
Else
If Target.Value = "Stainless" Or Target.Value = "Nickel" Or Target.Value = "Mild Steel" Then
com = "Enter comment in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1
Else
Target.Offset(0, 1).Value = ""
End If
End If
End Sub
I am trying to create a shared document that will have multiple users. There are 10 columns, 7 have data validation drop down lists and 3 are manual entry. Each manual entry column information is dependent on the data validation list in the column directly to it's left. I am trying to write the code that produces a pop up box requiring the manual entry information to be typed when the information in the data validation list is selected. I have been successful when applying it to just one of the data columns and manual entry columns. When I try to expand the code to the second data validation list and manual entry column, I receive an error. I have pasted the code below. All the column numbers, ranges, and subsequent data entry list values are present and correct. My question is, how do I apply this to multiple columns that are independent of each other? I am new to coding. Thanks!
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
Set isect = Application.Intersect(Target, Range("C3:C225"))
If isect Is Nothing Then
Else
If Target.Value = "1st" Or Target.Value = "2nd" Or Target.Value = "3rd" Then
com = "Enter comment in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1
Else
Target.Offset(0, 1).Value = ""
End If
End If
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If
Dim com As String
Dim comm1 As String
Set isect = Application.Intersect(Target, Range("H3:H225"))
If isect Is Nothing Then
Else
If Target.Value = "Stainless" Or Target.Value = "Nickel" Or Target.Value = "Mild Steel" Then
com = "Enter comment in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1
Else
Target.Offset(0, 1).Value = ""
End If
End If
End Sub