Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
I have a cell that contains a drop down list. I want the macro to run if the
list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
Static prevValue
Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
Try this...
Option Explicit Private m_vChangeValue As String Private Sub Worksheet_Change(ByVal Target As Range) Dim wksFrom As Worksheet Dim wksTo As Worksheet If Target.Address = "$K$34" Then Set wksFrom = Sheets("Model Allocation Inputs") Set wksTo = Sheets("60-40 Charts") Select Case Target.Value Case "" Exit Sub Case "60/40" If Target.Value < m_vChangeValue Then wksFrom.Range("D25:D37").Copy wksTo.Range("F39").Paste Link:=True End If Case "80/20" If Target.Value < m_vChangeValue Then wksFrom.Range("E25:E37").Copy wksTo.Range("F39").Paste Link:=True End If End Select End If m_vChangeValue = Target.Value Set wksFrom = Nothing Set wksTo = Nothing End Sub This code needs to be pasted into the sheet where you want to update Cell k34. Right Click on the sheet. Select View Code. Paste this into the code window... I am not running Excel 2003 so I do not have the link property here at work, but this should do what you need it to do. HTH "Piwo" wrote: I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
Bob, Thanks for your help. I changed the code and it seems to run ok when I
chose to run the macro with a specific command. however, I want it to run automatically when the value in k34 changes. Can you help with this? Thanks "Bob Phillips" wrote: Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
Not tested, but this should work
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("K34")) Is Nothing Then With Target If .Value < prevValue Then Select Case .Value Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = .Value End If End With Me.Select End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
How does the value in K34 change? Is the cell manually edited, is it
updated by a dde link, is it filled with a formula and thus calculated? -- Regards, Tom Ogilvy "Piwo" wrote in message ... Bob, Thanks for your help. I changed the code and it seems to run ok when I chose to run the macro with a specific command. however, I want it to run automatically when the value in k34 changes. Can you help with this? Thanks "Bob Phillips" wrote: Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
Tom,
the cell value is based on a selection from a drop down list. Thanks "Tom Ogilvy" wrote: How does the value in K34 change? Is the cell manually edited, is it updated by a dde link, is it filled with a formula and thus calculated? -- Regards, Tom Ogilvy "Piwo" wrote in message ... Bob, Thanks for your help. I changed the code and it seems to run ok when I chose to run the macro with a specific command. however, I want it to run automatically when the value in k34 changes. Can you help with this? Thanks "Bob Phillips" wrote: Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
I get the following message: ambiguous name detected Worksheet_change
"Bob Phillips" wrote: Not tested, but this should work Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("K34")) Is Nothing Then With Target If .Value < prevValue Then Select Case .Value Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = .Value End If End With Me.Select End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
The macro as it currently exists...
Sub Copy_Model() ' ' Copy_Model Macro ' ' Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("K34")) Is Nothing Then With Target If .Value < prevValue Then Select Case .Value Case "" Exit Sub Case "20/80" Sheets("Model Allocation Inputs").Select Range("Model20_80").Select Selection.Copy Sheets("Model Chart").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "40/60" Sheets("Model Allocation Inputs").Select Range("Model40_60").Select Selection.Copy Sheets("Model Chart").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "60/40" Sheets("Model Allocation Inputs").Select Range("Model60_40").Select Selection.Copy Sheets("Model Chart").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("Model80_20").Select Selection.Copy Sheets("Model Chart").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "100/0" Sheets("Model Allocation Inputs").Select Range("Model100_0").Select Selection.Copy Sheets("Model Chart").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Select Else prevValue = .Value End If End With Me.Select End If ws_exit: Application.EnableEvents = True End Sub Option Explicit Private m_vChangeValue As String "Bob Phillips" wrote: Not tested, but this should work Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("K34")) Is Nothing Then With Target If .Value < prevValue Then Select Case .Value Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = .Value End If End With Me.Select End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
That's because you can only have one.
-- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I get the following message: ambiguous name detected Worksheet_change "Bob Phillips" wrote: Not tested, but this should work Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Static prevValue On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("K34")) Is Nothing Then With Target If .Value < prevValue Then Select Case .Value Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = .Value End If End With Me.Select End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
Guess you already have your answer.
-- Regards, Tom Ogilvy "Piwo" wrote in message ... Tom, the cell value is based on a selection from a drop down list. Thanks "Tom Ogilvy" wrote: How does the value in K34 change? Is the cell manually edited, is it updated by a dde link, is it filled with a formula and thus calculated? -- Regards, Tom Ogilvy "Piwo" wrote in message ... Bob, Thanks for your help. I changed the code and it seems to run ok when I chose to run the macro with a specific command. however, I want it to run automatically when the value in k34 changes. Can you help with this? Thanks "Bob Phillips" wrote: Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
list + macros continued
Actually Tom, I havent figured this out yet.
"Tom Ogilvy" wrote: Guess you already have your answer. -- Regards, Tom Ogilvy "Piwo" wrote in message ... Tom, the cell value is based on a selection from a drop down list. Thanks "Tom Ogilvy" wrote: How does the value in K34 change? Is the cell manually edited, is it updated by a dde link, is it filled with a formula and thus calculated? -- Regards, Tom Ogilvy "Piwo" wrote in message ... Bob, Thanks for your help. I changed the code and it seems to run ok when I chose to run the macro with a specific command. however, I want it to run automatically when the value in k34 changes. Can you help with this? Thanks "Bob Phillips" wrote: Static prevValue Dim model As String Range("k34").Select model = ActiveCell.Value If model < prevValue Then Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select Else prevValue = model End If -- HTH RP (remove nothere from the email address if mailing direct) "Piwo" wrote in message ... I have a cell that contains a drop down list. I want the macro to run if the list selection changes. For example: I currently selected 60/40 from the list. If I select 60/40 again, there is obviously no need to re run the macro. however, if I change the selection to 80/20 I would want to macro to run. Clear? Thanks Dim model As String Range("k34").Select model = ActiveCell.Value Select Case model Case "" Exit Sub Case "60/40" Sheets("Model Allocation Inputs").Select Range("D25:D37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True Case "80/20" Sheets("Model Allocation Inputs").Select Range("e25:e37").Select Selection.Copy Sheets("60-40 Charts").Select Range("F39").Select ActiveSheet.Paste Link:=True End Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average With < Continued | Excel Worksheet Functions | |||
If then statement continued! | Excel Discussion (Misc queries) | |||
continued help needed | Excel Programming | |||
Macros not appearing in the Tools Macro Macros list | Excel Programming | |||
List the Macros that can be executed from Tools-Macros | Excel Programming |