Helllllllllpppp Problem with macros and lists.

Diane_E_Thomas · Apr 19th, 2002, 07:42 AM

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.

Audiojoe · Apr 19th, 2002, 07:50 AM

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

Diane_E_Thomas · Apr 19th, 2002, 08:00 AM

Audiojoe, THANKYOU you are a life saver, I can stop banging my head against this brick wall now!

Diane_E_Thomas · Apr 19th, 2002, 08:10 AM

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!

Mudface · Apr 19th, 2002, 08:33 AM

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.

Diane_E_Thomas · Apr 19th, 2002, 08:56 AM

It doesn't seem to run at all now....

Audiojoe · Apr 19th, 2002, 08:59 AM

Is it still in break in VBA? Try going in and pressing the stop button, then try it again

Diane_E_Thomas · Apr 19th, 2002, 09:13 AM

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!)

Mudface · Apr 19th, 2002, 09:18 AM

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.

Diane_E_Thomas · Apr 19th, 2002, 09:36 AM

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!!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 07:42 AM	#1
Diane_E_Thomas New Member Join Date: Apr 2002 Posts: 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.

Apr 19th, 2002, 07:50 AM	#2
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	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

Apr 19th, 2002, 08:00 AM	#3
Diane_E_Thomas New Member Join Date: Apr 2002 Posts: 17	Audiojoe, THANKYOU you are a life saver, I can stop banging my head against this brick wall now!

Apr 19th, 2002, 08:10 AM	#4
Diane_E_Thomas New Member Join Date: Apr 2002 Posts: 17	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!

Apr 19th, 2002, 08:33 AM	#5
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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.

Apr 19th, 2002, 08:56 AM	#6
Diane_E_Thomas New Member Join Date: Apr 2002 Posts: 17	It doesn't seem to run at all now....

Apr 19th, 2002, 08:59 AM	#7
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Is it still in break in VBA? Try going in and pressing the stop button, then try it again

Apr 19th, 2002, 09:13 AM	#8
Diane_E_Thomas New Member Join Date: Apr 2002 Posts: 17	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!)

Apr 19th, 2002, 09:18 AM	#9
Mudface MrExcel MVP Join Date: Feb 2002 Location: Sunny, spring-like Hull Posts: 3,339	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.

Apr 19th, 2002, 09:36 AM	#10
Diane_E_Thomas New Member Join Date: Apr 2002 Posts: 17	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!!