Helllllllllpppp Problem with macros and lists.

Diane_E_Thomas

New Member
Joined
Apr 18, 2002
Messages
17
Please can someone help me.

I have created a list of 14 things using data validation/ lists, but now I want to asign a macro to each of the 14 options, so that when I select an option from my drop down list a macro will automatically run, and if I chose another option, another macro would run. Perhaps I need a different way of creating my list? Any advice would be greatly appreciated.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Well hello,

Try this, right click on the sheet tab that your answer appears on, and select View Code. Now paste this in:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Worksheets("sheet1").Range("b1").Value = "a" Then
Run ("whateveryourmacroiscalled")
End If

End Sub

This code says that whenever "a" appears in B1 of Sheet 1 then it runs the macro. So you can just tailor this to suit your requirements

Hope it helps

Audiojoe
Whisky and loving, and wild, wild women
 
Upvote 0
Me again......

The code seems to be running in a loop. How do I get it to only do the code once. It must be to do with getting it to count or something... My visual basic is not good!
 
Upvote 0
You would probably need to change the first line to: -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

so it doesn't keep running.
 
Upvote 0
Nope... I've exited VBA and it still doesn't run. Can I not use an if, then, elseif? to say if the result is already there then dont continue to do it? (if so so how I do that, my attempt didnt work!)
 
Upvote 0
Diane, Audiojoe's original code would run whenever you changed cells. My amendment will only run if you actually change the value of a cell. Try changing it to something different, exiting the cell, then change it back to the value which would run your macro.
 
Upvote 0
Hi Guys,

Thanks for your help, It is still not working properly (no doubt my ineptness rather than your advice!) What happens now is that if I change my option in the drop down menu, nothing happens, but if I change of of the parameters which feed into my macro, then the code works, but continuosly, whether I have the selection in the first line or not! I have copied exactly what I have writen below in case it helps!

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Worksheets("E. Surrey (Peak)").Range("o9").Value = "Triangular" Then
Run ("macro2")
End If
End Sub

MACRO 2

Range("O17").Select
ActiveCell.FormulaR1C1 = "=RiskTriang(R[-7]C,R[-6]C,R[-5]C)"
Range("O18").Select
End Sub


(the risktriang comes from @risk, excel add in which does monte carlo analysis)

Cheers!!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top