Prompting a macro to run on change of cell content

[NuclearWookiee]

I'm very new to VBA, but have had a bit of programming experience in th
past. I'm working with Excel 2000. What I want to do is associate a
imbedded image with each item in a drop down list so that when an ite
is selected from the list the corresponding image is displayed at
specified location. The way I have it set up is with all images use
being located in the same worksheet off of the viewable/print area.
When the macro runs, the apporpriate image is copied and pasted to th
specified cell. Here is what I have now.

Sub test()
'
' test Macro
' Macro created 4/14/2004 by x
'

'
If Range("C2") = Range("A2") Then
ActiveSheet.Shapes("Image1").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If
If Range("C2") = Range("A3") Then
ActiveSheet.Shapes("Image2").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If
If Range("C2") = Range("A4") Then
ActiveSheet.Shapes("Image3").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If
End Sub

Cell C2 contains the drop down menu with the range A2:A4 as the vali
data. Image1, Image2, and Image3 are embedded in the worksheet. Th
code works fine when the macro is run, but it need the macro to ru
automatically when a new selection is made - -ie, the macro needs t
run when the contents of cell C2 are changed- .

Any suggestions? Any other vital info I need to post that I hav
neglected to mention? My thanks for any assistance or consideratio
rendered

 

[pikus]

In "ThisWorkbook" Module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)
If Target.Column = 2 And Target.Row = 2 Then
'Do all that other stuff...
End If
End Sub

Is that clear?

- Piku

 

[Frank Kabel]

Hi
you may use an event procedure for this. e.g. the worksheet_change
event. See
http://www.cpearson.com/excel/events.htm
for more details

 

[NuclearWookiee]

I'm sorry, I must be missing something. Here's how I inserted you
suggestion under ThisWorkbook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)

If Target.Column = 2 And Target.Row = 2 Then

If Range("C2") = Range("A2") Then
ActiveSheet.Shapes("Image1").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If

If Range("C2") = Range("A3") Then
ActiveSheet.Shapes("Image2").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If

If Range("C2") = Range("A4") Then
ActiveSheet.Shapes("Image3").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If

End If

End Sub.

It still doesn't automatically update when the cell content is changed
It works fine when I actually run the macro

 

[pikus]

Try this for each of your If Then statements:

If Range("C2").Value = Range("A2").Value Then
ActiveSheet.Shapes("Image1").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If

Let me know how thet works... - Piku

 

[pikus]

Or better yet:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)

If Target.Column = 2 And Target.Row = 2 Then

Select Case Range("C2").Value

Case Range("A2").Value
ActiveSheet.Shapes("Image1").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste

Case Range("A3").Value
ActiveSheet.Shapes("Image2").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste

Case Range("A4").Value
ActiveSheet.Shapes("Image3").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste

End Select

End If

End Su

 

[NuclearWookiee]

Still won't work for me. Here's what I have now, in ThisWorkBook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target A
Range)

If Target.Column = 2 And Target.Row = 2 Then

If Range("C2").Value = Range("A2").Value Then
ActiveSheet.Shapes("Image1").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If

If Range("C2").Value = Range("A3").Value Then
ActiveSheet.Shapes("Image2").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If

If Range("C2").Value = Range("A4").Value Then
ActiveSheet.Shapes("Image3").Select
Selection.Copy
Range("B10").Select
ActiveSheet.Paste
End If

End If

End Su

 

[pikus]

Check out the Select Case statement I just added and if that doesn'
work tell me what error you're getting. - Piku

 

[NuclearWookiee]

It just doesn't do anything. There's no error message or anything, th
pic just doesn't change

 

[pikus]

Check your private messages. - Piku

 

[NuclearWookiee]

Pikus, I sent it to you this morning. Thanks

 

[Sue Harsevoort]

I'm not an export on this, but wouldn't cell C2 be column 3, row 2, not
Column 2?

Sue