Userforms and Command Buttons - How to link code to buttons

[jardenp]

I'm new to forms, so I have two basic questions that I can't find the answer to.

1) I have a form Report_All_Specific that asks users if they want to run a report for all employees or specific employees. The form consists of a question and two command buttons named All_Active and Specific. I know how to call the form, but where can I put my code that corresponds to each button? I'd rather not have the buttons call separate subs if possible--I'd rather just have the code in the original sub. The logic would be something like:

Sub EE_Reports

Report_All_Specific.Show

If All_Active is clicked then

action 1​

If Specific is clicked

action 2​

End command button-related code
remainder of macro code (that executes no matter which button is clicked)
End Sub

and 2) I want to have a form with three command buttons (say CB1, CB2, and CB3). If CB1 is clicked, variable X is set to 1, if CB2 then X is set to 2, and if CB3 then X is set to 3. How do I do this and would that code go in the Sub that calls the form or in the form itself?

Thanks!

Josh in IN

 

[davesexcel]

Do you mean how to get the code into the command button? Double click the command button and you will be in the buttons click event.

 

[jardenp]

Thanks! I have a large portion of code I want to execute regardless of which button is clicked. Is there a way to get out of the private sub and back to the regular sub where I called the form from in the first place? (In my example it would be getting back into Sub EE_Reports)

Thanks again

 

[davesexcel]

You should be able to just call the macro when you click the button.

Private Sub CommandButton2_Click()
MyMacro 'calls a macro named MyMacro
End Sub

 

[jardenp]

When I assign a value to a variable in the button's Private Sub, it doesn't carry over to other subs. Is there something I can do to make the variables usable in all macros in the project?

 

[jsmath22]

You have to make the variable public. On the line above the Private Sub declaration put Dim var_name As Type then you should be able to see it from the rest of the code. However, if the code stops running between the calls, variables are not saved (so if you call Macro1 set x=5 and then the code executes End Sub, that x=5 is lost). I use a hidden worksheet to store variables that are set during one portion of code and being used by another portion if the code stops.

-Me

 

[jardenp]

OK. I follow that. Thanks!