-
I am having problems developing an application in VBA for Excel 97. I have a macro I call when a user clicks on a command button on the worksheet, but I cannot get it to pass on values to the macro. I'm trying to do it something like this:
Private Sub commandbutton1_click()
Dim a As Integer
Dim b as Integer
If optionbutton1.value = True Then
a=1, b=0
End If
If optionbutton2.value = True Then
a=0,b=1
End If
Call MainMacro(ByVal a,ByVal b)
End Sub
Public Sub Mainmacro(x as Integer, y as Integer)
etc
No matter whether I use Call Mainmacro(1,0) or whatever I can't seem to get the variables x and y to hold onto their values in the Mainmacro. They either come through undefined or both as zero, and so my macro won't work. I'm using the two variables because an option button on the form means the macro has to be called in one of two ways - & they're so similiar it's a waste of space to name two different macros. What am I doing wrong?
-
Hi galpinm,
Just wondering, do you have a and b declared as global in a module?
Global a as integer
Global b as integer
If not, they need to be so they can keep whatever values are assigned to them and be able to pass those on to other functions or subs.
Added in edit:
I ran your code and had to make some changes.
Here is the code as it ran for me:
Module 1 Declarations
Global a As Integer
Global b As Integer
Place the following in the module
Sub Mainmacro(x As Integer, y As Integer)
'only used to verify it's working
msg = " x is equal to = " & x & " And y is equal to = " & y
MsgBox msg, vbInformation, "End"
End Sub
Procedures:
Private Sub CommandButton1_Click()
If OptionButton1.Value = True Then
a = 1
b = 0
End If
If OptionButton2.Value = True Then
a = 0
b = 1
End If
Call Mainmacro(a, b)
End Sub
Please let us know if that works.
JazzBass
------------------
21 yr old beginner VB Programmer
VB 6 Professional @ Home
VB 3 Professional @ the Office
[This message has been edited by JazzBass (edited 12-02-1999).]
