Passing variables between procedures

[cs1ctp]

Hi,

I have a series of buttons that when clicked, open the same user form. There is a link label on the user form that when clicked displays a particular tab in the spreadsheet depending on the button that was pressed. The code for the buttons is in Sheet1(Code), the Code for the user form is in UserForm1(Code). Basically I would like to know how I can set a variable when a button is clicked and then pass this to the UserForm1(Code) so that when the link is clicked the correct tab is opened on the spreadsheet dependent on the button that was pressed.

This is the Sub attached to the button

Private Sub CommandButton1_Click()
variable = 1
End Sub

Private Sub CommandButton2_Click()
variable = 2
End Sub

Then in a seperate location this is the Sub for the label.

Private Sub Label5_Click()
If variable = 1 Then ...
Else 
If variable = 2 Then ...
End If
End Sub

What is the code for passing a variable between these two procedures?

Many thanks in advance for your help,

Chris

 

[Norie]

Chris

To use this variable in both procedures you would have to declare it as Public, you couldn't 'pass' the variable.

What are you actually doing with the userform?

 

[KenCriss]

I think you just have to declare the item as Public like this

Public RowNum As Long

 

[cs1ctp]

Hi,

Basically I have a graphical calendar on a spreadsheet with buttons on various dates referring to contracts that we have. When any of the buttons are pressed, a UserForm is opened, but displays the different contracts depending on what button was pressed. Then when a link is clicked on the UserForm, appropriate tab on the spreadsheet is opened depending on the button that was initially clicked displaying the contract details.

Is it a case of declaring a global variable that is initiated when a button is clicked, then calling that variable when the link label on the userform is clicked. If so do I need to make all the procedures that call that variable public.

Another approach I considered was to return a value when a button is clicked and pass this to the code for the userform, then when the link is clicked, the value returned is simply called and the appropriate tab opened based on that variable. Hope this makes sense.

Not really sure of the approach to take and a little unsure of how to implement.

Thanks for taking the time to answer this query, your help is very much appreciated.

Regards,

Chris

 

[Norie]

Chris

Have you tried our suggestion?

Just put code like Ken posted in a standard module.

By the way I'm still not quite following what you are doing.

 

[cs1ctp]

KenCriss

Where do I define the Public variable?? is it outside the procedures and in that case, do I have to make all procedures that use it public??

Also not sure if I understand how the variable will be passed across from two different code windows!! eg: Sheet1(Code) and UserForm1(Code)

Thanks for your help with this

Chris

 

[cs1ctp]

Am in the middle of trying the suggestion, but not sure about locations, see previous post. For further understanding, maybe this describes issue better:

Spreadsheet contains:
Button1
Button2
Button3
Button4

Click Event for all buttons opens up UserForm1

LinkLabel on UserForm1 then links to appropriate tab on spreadsheet e.g.
If Button1 clicked Then
Open Sheet-Jan
Else
If Button2 clicked Then
Open Sheet-Feb
Else
If ...... etc. etc.

So to summarise, if button 1 is pressed, a variable is set, then this is somehow passed to UserForm1(Code) so that when the label on UserForm1 is clicked, correct tab is opened on spreadsheet based on variable initiated when button was clicked.

Hope this makes more sense, I will continue to play around with Public variable.

Thanks for your help guys,

Chris

 

[Norie]

Chris

Why not just have >1 button on the userform?

 

[cs1ctp]

Sorry, not sure if I understand, if I have >1 buttons I will still not know what button on the spreadsheet was initially pressed to activate the UserForm!! Hence will not be able to go to the correct tab.

I think the Public Variable will work, it's just I am not sure where to declare it so both procedures can use it, i.e. One part of the code is part of the code for Microsoft Excel Objects, the other comes under Forms in the Project Explorer.

Anyway, I will continue to play around, to see what I can do.

Thanks,

Chris

 

[cs1ctp]

Hold up ... I think I've solved it!! Basically just needed to declare the variable in Module1 ... now I can use the variable in both procedures!!

Frustration Averted!!

Thanks for your help

Chris