Running Macros based on a variable

[adovelikeboy]

Please pardon me if these seem like foolish queries, I've been using Excel for years, but the cat knows as much about VBA as I do. My issue:

I am constructing a series of macros to format a complex input form that I'm designing to price projects. The macros hide various rows and worksheets if they aren't relevant to a particular project. I want the person using the form to be able to select a number of project deliverables from 1 to 6. Based on that choice I want to run the appropriate Macro that will hide the irrelevant data.

I need to make this as user-friendly as possible. Ideally, the user would select a value, 1 through 6, from a drop-down list and the corresponding macro would run. Is this possible?

Thanks in advance

Colm

 

[PA HS Teacher]

You could use a Select Case Statement.

For example, assuming you have a drop down (data validation) in cell A1.

You could put the following in the worksheet change event.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Target <> Range("A1") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value<br>       <SPAN style="color:#00007F">Case</SPAN> 1: <SPAN style="color:#00007F">Call</SPAN> Macro1<br>       <SPAN style="color:#00007F">Case</SPAN> 2: <SPAN style="color:#00007F">Call</SPAN> Macro2<br>       <SPAN style="color:#007F00">' etc</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Macro1()<br>   <SPAN style="color:#007F00">' Your Code 1 Here</SPAN><br>   MsgBox "hi 1"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Macro2()<br>   <SPAN style="color:#007F00">' Your Code 2 Here</SPAN><br>   MsgBox "hi 2"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#007F00">' Etc.</SPAN><br></FONT>

 

[adovelikeboy]

Thanks. That seemed to do it (once I learned what a Worksheet change was - as I said, my VBA knowledge is scant).

Do you know if I can have more than one set of instructions within the worksheet change? I.e. a second set referring a different cell value and a different set of macros?

 

[PA HS Teacher]

You can use If statements.

e.g.
If Target=Range("A1") then
Same Code as before.
End if

If Target = Range("A2") then
Similar Code to before
End If