I have a drop down box that user chooses from this in turn place a 1, 2, 3 in
L11. At this point I want the code below to run placing the result in G6.
However, I have to physically go to cell L11, F2, enter to make it update.
How do I get G6 to update automatically after selection of choose box.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$11" And Target.Count = 1 Then
If (Target.Value) = "1" Then
Range("G6") = "Cat"
End If
End If
If Target.Address = "$L$11" And Target.Count = 1 Then
If (Target.Value) = "2" Then
Range("G6") = "Dog"
End If
End If
If Target.Address = "$L$11" And Target.Count = 1 Then
If (Target.Value) = "3" Then
Range("G6") = "Fish"
End If
End If
End Sub

Hi,

Don't use the worksheet change event use the dropdown change event instead.
Right click it and use this simplified code

Sub DropDown1_Change()
Select Case Sheets("Sheet1").Range("L11")
Case Is = 1
Range("G6") = "Cat"
Case Is = 2
Range("G6") = "Dog"
Case Is = 3
Range("G6") = "Fish"
End Select
End Sub

Mike

"deeds" wrote:

I have a drop down box that user chooses from this in turn place a 1, 2, 3 in
L11. At this point I want the code below to run placing the result in G6.
However, I have to physically go to cell L11, F2, enter to make it update.
How do I get G6 to update automatically after selection of choose box.

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$11" And Target.Count = 1 Then
If (Target.Value) = "1" Then
Range("G6") = "Cat"
End If
End If
If Target.Address = "$L$11" And Target.Count = 1 Then
If (Target.Value) = "2" Then
Range("G6") = "Dog"
End If
End If
If Target.Address = "$L$11" And Target.Count = 1 Then
If (Target.Value) = "3" Then
Range("G6") = "Fish"
End If
End If
End Sub

the macro is being called twice because when you change G6 is causes a
2nd event of the macro to occur. disableEvent should solve the problem.
I also made some improvements to the code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$11" And Target.Count = 1 Then
Application.EnableEvents = False
Select Case Target.Value

Case 1:
Range("G6") = "Cat"
Case 2:
Range("G6") = "Dog"
Case 3:
Range("G6") = "Fish"
End Select
Application.EnableEvents = False
End If
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149424