Choose box, put cell contents, update
 

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

Choose box, put cell contents, update
 

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

Choose box, put cell contents, update
 

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