Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
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 -- Regards Frank Kabel Frankfurt, Germany I'm very new to VBA, but have had a bit of programming experience in the past. I'm working with Excel 2000. What I want to do is associate an imbedded image with each item in a drop down list so that when an item is selected from the list the corresponding image is displayed at a specified location. The way I have it set up is with all images used being located in the same worksheet off of the viewable/print area. When the macro runs, the apporpriate image is copied and pasted to the 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 valid data. Image1, Image2, and Image3 are embedded in the worksheet. The code works fine when the macro is run, but it need the macro to run automatically when a new selection is made - -ie, the macro needs to run when the contents of cell C2 are changed- . Any suggestions? Any other vital info I need to post that I have neglected to mention? My thanks for any assistance or consideration rendered! --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
Check out the Select Case statement I just added and if that doesn'
work tell me what error you're getting. - Piku -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
It just doesn't do anything. There's no error message or anything, th
pic just doesn't change -- Message posted from http://www.ExcelForum.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompting a macro to run on change of cell content
I'm not an export on this, but wouldn't cell C2 be column 3, row 2, not
Column 2? Sue "pikus " wrote in message ... In "ThisWorkbook" Module: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Column = 2 And Target.Row = 2 Then 'Do all that other stuff... End If End Sub Is that clear? - Pikus --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Cell Content | Excel Discussion (Misc queries) | |||
Change format of cell content | Excel Discussion (Misc queries) | |||
how to change a cell color based on its content using macro? | New Users to Excel | |||
Excel Macro Save CSV Cell without Prompting | Excel Discussion (Misc queries) | |||
Change picture according to cell content | Excel Programming |