Userform Variable

[Barbu]

Hello,

I'm using a userform with 2 commandButtons ("Corp1"and "Corp2"), each one calls a different macro.
what I need it's declare the name of the commandButtons that was clicked as a public variable, because I need that word latter for the report header.

That's what I'm trying:

'in a general module'
Public Corps as Integer

'userform code'
Private Sub Corp1_Click()
Corps = Me.Corp1.Value
Call Report_Corp1
Unload Me
End
End Sub

Private Sub Corp2_Click()
Corps = Me.Corp2.Value
Call Report_Corp2
Unload Me
End
End Sub

but I get a type mismatch error, any help will be very appreciate
Thanks !!!

 

[repairman615]

Hello,

I believe the command buttons value are boolean, True or False.

Try something like:

Public myStr As String
Private Sub CommandButton1_Click()
myStr = Me.CommandButton1.Name
MsgBox myStr
End Sub
Private Sub CommandButton2_Click()
myStr = Me.CommandButton2.Name
MsgBox myStr
End Sub

 

[Jerry Sullivan]

Hi Barbu,

As Jeff showed, you can pass the CommandButton's Name as to a Public String Variable.

Be aware though, that generally it's better to use alternatives to Public Variables, if possible.

Are your Reprt_Corp1 and Report_Corp2 functions doing essentially the same thing with different parameters (like the Header)?

If so, consider re-writing those into one function that takes the Button Name a parameter. This would simplify maintenance of your code- especially if you have a lot of buttons.

Private Sub Corp1_Click()
    Call Report_Corp(Me.Corp1.Name)
    Unload Me
End Sub

Private Sub Corp2_Click()
    Call Report_Corp(Me.Corp2.Name)
    Unload Me
End Sub

Public Function Report_Corp(strName As String)
    'your function using Calling Button's Name as Variable
    '..... 
End Function

If you can't simplify the various functions into a single Report_Corp furnction, because each one's actions are unique for each button, then it would seem there is no reason to "pass" the calling button's name- you could just write that into the called function.

Private Sub Corp1_Click()
    Call Report_Corp1
    Unload Me
End Sub

Public Function Report_Corp1()
    'your function using "Corp1" as a Constant
    '.....for example....
    Sheets("Sheet1").Range("A1")="Corp1"
    '....
End Function

 

[Barbu]

I'm sorry Jerry but I'm not following you very well. I have been working with Excel VBA just for a few weeks (and I don't have any other language background) so, there are alot of things I don't understand yet, like, why do you say that generally it's better to use alternatives to Public Variables, if possible ??

And about my code, I don't think I can re-write the funtions because even when both are generated from the same database, they do different things.

Thank you very much, I really apreciate your help !!!

 

[Barbu]

Thanks Jeff it works !!!

 

[repairman615]

Glad to help!

 

[Danielone]

Hi,
I would be more than happy if you could help me.

I have to run a macro where, at a certain point, I ask the user to set the variables "capital" and "time".
Here the code for the Userform:

Private Sub CommandButton1_Click()

capital = UserForm1.TextBox1.Value
temp = UserForm1.TextBox2.Value

Me.Hide

End Sub


In the main code of the macro I have just written:

UserForm1.Show

But is not working. VBA can't read the variables' values, so the routine does not work.
I really do not know what to do.
I am stuck on this stupid thing and cannot go ahead.
If you could help me I would be pleased.
Thank you!
D