Running Macros based on a variable

adovelikeboy

New Member
Joined
Aug 1, 2010
Messages
9
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

New Member
Joined
Aug 1, 2010
Messages
9
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

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,438
Messages
5,511,353
Members
408,843
Latest member
ruannpreger

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top