excel using an if statement with a list?

May 4, 2009 at 04:22:23
Specs: Windows XP
I am trying to make an if statement that accomplishes something like, If b1=3 then a2=a1 else pick choice from the data validation list(drop down list) that is from d1 to d5. Thanks

See More: excel using an if statement with a list?

Report •


#1
May 4, 2009 at 07:34:07
The only way I can think of to make it automatic is to monitor B1 with a worksheet change macro as shown below.

For a slighly less automatic method, you could include an =A1 in your list, so the user could choose that value when B1 = 3.

Anyway, here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
  If Target.Address = "$B$1" Then
'If B1 = 3, delete A2 validation list and set A2 = A1
   If Target = 3 Then
     With Range("A2").Validation
        .Delete
     End With
     Range("A2") = Range("A1")
'Else clear A2 and add Data Validation list.
   Else
     Range("A2") = ""
      With Range("A2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$D$1:$D$5"
        .IgnoreBlank = True
        .InCellDropdown = True
      End With
     Range("A2").Activate
   End If
  End If
 Application.EnableEvents = True
End Sub


Report •

#2
May 6, 2009 at 05:12:38
THANKS IT WORKED GREAT

Report •

#3
May 6, 2009 at 05:40:58
But now I realized a problem when appling to the actual use. I am trying to use the above code around 164 times with slight variations each time, but only the first use is responsive on the sheet

An example of what I am trying to accomplish is:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$B$1" Then
'If B1 = 3, delete A2 validation list and set A2 = A1
If Target = 3 Then
With Range("A2").Validation
.Delete
End With
Range("A2") = Range("A1")
'Else clear A2 and add Data Validation list.
Else
Range("A2") = ""
With Range("A2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$D$1:$D$5"
.IgnoreBlank = True
.InCellDropdown = True
End With
Range("A2").Activate
End If
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change2(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$B$2" Then
'If B2 = 3, delete A3 validation list and set A3 = A2
If Target = 3 Then
With Range("A3").Validation
.Delete
End With
Range("A3") = Range("A2")
'Else clear A3 and add Data Validation list.
Else
Range("A3") = ""
With Range("A3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$D$1:$D$5"
.IgnoreBlank = True
.InCellDropdown = True
End With
Range("A3").Activate
End If
End If
Application.EnableEvents = True
End Sub


Report •

Related Solutions

#4
May 6, 2009 at 08:04:33
You can only have one Worksheet_Change macro per sheet, but that macro can do different things depending on the change.

For example,

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
  If Target.Address = "$B$1" Then
   'Do whatever you need to do if the change occurred in B1
  End If
  If Target.Address = "$B$2" Then
   'Do whatever you need to do if the change occurred in B2
  End If
  If Target.Address = "$B$3" Then
   'Do whatever you need to do if the change occurred in B3
  End If
 Application.EnableEvents = True
End Sub

Now considering that you are looking to do this "164 times with slight variations each time" I would look for something "common" between all changes to condense the code instead of simply repeating the original code "164 times with slight variations each time".

If the 2 examples you gave are indicative of a pattern i.e. A change in Column B means do something to Column A, offset by 1 row, then the code below might work.

If there is no pattern then you may very well have to repeat the code sections "164 times with slight variations each time".

One more note before I offer the new code:

If a Worksheet_Change macro makes a change to a worksheet, the macro may fire again because the worksheet changed again. That is why you need to set EnableEvents to False at the start of the marco and reset it to True before exiting. Now, since none of us are perfect, while we are testing our code, it is very possible for EnableEvents to get set to False and then have the code fail. Since EnableEvents doesn't get reset to True, the code won't fire when you make a change to the worksheet. You'll pull your hair out trying to fix your code, when the real issue is that EnableEvents is set to False. For that reason it helps to keep this little macro available to reset EnableEvents to True if required:

Sub ResetEvents()
 Application.EnableEvents = True
End Sub

Here's the code that will fire on any change to Column B:

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Fire on change to Column 2 only
  If Target.Column = 2 Then
'Capture Target row
    rwNum = Target.Row
'If Target = 3, delete validation list and set (A & Row + 1) = (A & Row)
   If Target = 3 Then
     With Range("A" & rwNum + 1).Validation
        .Delete
     End With
     Range("A" & rwNum + 1) = Range("A" & rwNum)
'Else clear (A & Row + 1) and add Data Validation list.
   Else
     Range("A" & rwNum + 1) = ""
      With Range("A" & rwNum + 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$D$1:$D$5"
        .IgnoreBlank = True
        .InCellDropdown = True
      End With
     Range("A" & rwNum + 1).Activate
   End If
  End If
 Application.EnableEvents = True
End Sub


Report •

#5
May 6, 2009 at 08:33:33
what if i need to apply it to 2 seperate columns that do the same thing once to column A from column B like above but also from Column F to effect Column G? Thanks

Nevermind, I figured it out thank you so much


Report •

#6
July 7, 2009 at 20:48:09
I am trying to make an if statement that accomplishes
something like, If c1=b1 then d1 = pick choice from the data
validation list(drop down list) that is from e1 to e5. else pick
choice from the data validation list(drop down list) that is from f1
to f5.

Report •


Ask Question