Code refering to multiple cells

**wmaxwell** · 12-04-2012, 08:48 PM

This thread had exactly what I needed to run the macro from a drop down list.

http://www.excelforum.com/excel-prog...down-menu.html

But I can not get it to work on multiple lists on the same sheet as it is locked to the one cell, any ideas or what changes to the code I need to make? I would need it to run on 4 dif cells.

Thanks,

**HaHoBe** · 12-05-2012, 02:06 AM

Hi, wmaxwell,

Dropdowns hold A, B and C as option, cells are B2, B4, C3, and C7 for the code to check, code goes behind the Worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2,B4,C3,C7")) Is Nothing Then
  Select Case Target.Value
    Case "A"    'First Drop Down Item
      Call MacroA
    Case "B"    'Second Drop Down Item
      Call MacroB
    Case "C"    'Third Drop Down Item
      Call MacroC
  End Select
End If
End Sub

Ciao,
Holger

**wmaxwell** · 12-05-2012, 12:37 PM

Couldn't get this to work either.

This is the code that I have in there now,

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Addx As String
Dim Rng As Range

Addx = Target.Validation.Formula1
Set Rng = Range(Right(Addx, Len(Addx) - 1))

If Target.Address = "$D$2" Then
Select Case Target.Value
Case Is = Rng.Cells(1, 1) 'First Drop Down Item
Call SurfaceHole
Case Is = Rng.Cells(2, 1) 'Second Drop Down Item
Call FlocWater
Case Is = Rng.Cells(3, 1) 'Third Drop Down Item
Call PacPolymer
End Select
End If

End Sub

Works perfect on cell D2 but I need it to work on cells D28, D54, and D80.

Thanks for the help.

**HaHoBe** · 12-05-2012, 01:28 PM

Hi, wmaxwell,

did you try the code I suggested? The code you want to apply needs a list anywhere to rely on that for Data/Valdiation:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Addx As String
Dim Rng As Range

If Not Intersect(Target, Range("D2,D28,D54,D80")) Is Nothing Then
  Addx = Target.Validation.Formula1
  Set Rng = Range(Right(Addx, Len(Addx) - 1))
  Select Case Target.Value
    Case Is = Rng.Cells(1, 1) 'First Drop Down Item
      Call SurfaceHole
    Case Is = Rng.Cells(2, 1) 'Second Drop Down Item
      Call FlocWater
    Case Is = Rng.Cells(3, 1) 'Third Drop Down Item
      Call PacPolymer
  End Select
End If

End Sub

Sub SurfaceHole()
MsgBox "SurfaceHole"
End Sub

Sub FlocWater()
MsgBox "FlocWater"
End Sub

Sub PacPolymer()
MsgBox "PacPolymer"
End Sub

Ciao,
Holger

**wmaxwell** · 12-05-2012, 01:44 PM

Hey Man, I did try the first one but couldn't get it to work.

This last one work perfectly after I deleted this

Sub SurfaceHole()
MsgBox "SurfaceHole"
End Sub

Sub FlocWater()
MsgBox "FlocWater"
End Sub

Sub PacPolymer()
MsgBox "PacPolymer"
End Sub

Thanks!!