Passing variable values to a VBA form

  Les 13:42 26 May 2007

I have a small problem with a VBA program I am writing in Excel 2003.

I have two buttons on a worksheet, both of which load and show the same form but to get different data from lists on that form.

I have to pass information to the form which data that is.

Is it possible to establish a variable in the button code, just before the form is loaded and which will remain at it’s value when the form is shown and is active, eg: Which is set at 1, or 2 as appropriate. On entering the form it would then know from this value which code to run.

Help appreciated for I have never attempted this before and am unsure on the method used to achieve this.

  VoG II 13:46 26 May 2007

In a standard module declare the variable as public right at the top of the module (i.e. before any Subs), e.g.

Public MyVar as Variant

It should then be available to all modules within the workbook.

  Les 13:54 26 May 2007

Now, that is what I call a quick reply!! Thanks Vog I'll try that and post the result.

  Les 14:12 26 May 2007

Clearly I'm doing something wrong for I have no doubt that your method is correct!

The buttons are on the worksheet. The code is 'attached' to that worksheet, ie:- when I click on sheet2 in the Project list and select view code - the code appears. In the General section I have made the entry at the top as suggested - established the variable value in the code for the button just before the code loading the form.

When the form is shown I have stopped execution in the Userform Initialise with the code 'If which=0 then stop' - it does.

Any suggestions please?

  VoG II 14:44 26 May 2007

Have you tried this in a standard module? In the Visual Basic Editor Insert > Module and make the declaration there. Don't forget to delete the Public statement from the sheet's code module.

  Les 14:46 26 May 2007

I had the declaration in the wrong place! As said, I had it in the sheet2 code - moved it to what I now understand as a 'standard' module and voila!

Thanks Vog.

  Les 16:46 26 May 2007

Program now working as I intended - thanks again.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Lightwell software lets you create mobile apps without using code

Best value Mac: Which is the best £1249 Mac to buy

Comment désactiver les programmes qui s'exécutent au démarrage de Windows 10 ?