Multiple commands in VBA solution

[RyGuy30]

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

 

[mumps]

In which columns are the 3 manual entry columns?

 

[RyGuy30]

Hi Mumps,
D, G, and I. Please note, in my code, I only installed two of the 3 columns. That is why you see the referenced columns as C and H, as they hold the information pertinent to columns D and I. Thank you in advance for your interest and efforts.

 

[mumps]

Replace your current macro with this one:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C:C,F:F,H:H")) Is Nothing Then Exit Sub
    Dim response As String
    Do
        response = InputBox("Please enter a value for " & Target.Offset(, 1).Address(0, 0))
        If response <> "" Then
            Target.Offset(, 1) = response
            Exit Do
        ElseIf response = "" Then
            Target.Offset(, 1).Select
            MsgBox "You must enter a value for " & Target.Offset(, 1).Address(0, 0), vbOKOnly, ""
        End If
    Loop
End Sub

 

[Osvaldo Palmeiro]

Replace String1, String2 and String3 by the itens of Data Validation list from column I.

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim comm1 As String
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, [C3:C225]) Is Nothing Then
   If Target.Value <> "1st" And Target.Value <> "2nd" And Target.Value <> "3rd" Then Exit Sub
  ElseIf Not Intersect(Target, [H3:H225]) Is Nothing Then
   If Target.Value <> "Stainless" And Target.Value <> "Nickel" And Target.Value <> "Mild Steel" Then Exit Sub
  ElseIf Not Intersect(Target, [I3:I225]) Is Nothing Then
   If Target.Value <> "String1" And Target.Value <> "String2" And Target.Value <> "String3" Then Exit Sub
  Else: Exit Sub
  End If

back:
   comm1 = InputBox("Enter comment in " & Target.Offset(0, 1).Address(0, 0))
   If comm1 = vbNullString Then
    GoTo back
   Else: Target.Offset(0, 1).Value = comm1
   End If
 
End Sub

 

[mumps]

@RyGuy30
I'm glad it worked out and thanks for the rep.