Multiple commands in VBA solution

RyGuy30

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,160
In which columns are the 3 manual entry columns?
 

RyGuy30

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,160
Replace your current macro with this one:
VBA Code:
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
 
Solution

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
610
Office Version
  1. 365
Platform
  1. Windows
Replace String1, String2 and String3 by the itens of Data Validation list from column I.
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,276
Messages
5,600,686
Members
414,400
Latest member
Damocles2021

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top