How to pass a variable to or Call a sub routine in an Excel User Form? 2

[SBDev]

Hi Dev Community,

NOTE (I have also posted this under the Excel Forum, so if you have already read or responed to this post in the Excel Form please don't waste your time)

I have been trying to locate a way to pass a variable or call a sub routine of an Excel 2002 VBA Userform, and so far i've been stifled.

The functionality that I seek is as follows: Using a macro(s) to call a single UserForm routine. When this single routine is called from different Excel cells I will to test a case and have the function operate in different ways relative to the case statenents.

Can you list members provide me with ideas on how I can or if I can achieve this type of funtionality?

Your thoughts are much appreciated.

Truly,
Steve

 

[rmikesmith]

Steve,

I'm sorry, but I don't quite understand what you are asking. Do you want the Userform to behave differently (show different labels, edit boxes, button choices, etc.) depending on which is the active cell when it's displayed? You can do all of this in the Userform's Initialize event handler. Event handlers for various controls on the Userform can also make changes to the form, call sub procedures, etc. Please post more detail and I will try to supply some example code.

Regards,
Mike

 

[SBDev]

Hi Mike (and the Forum)

Mike thank you for your post! I will attempt to clarify my needs' with the code.

Since my post last evening I was able to get the application functioning somewhat in the way I desire. The current functionality - as illustrated below - makes use of a global/Public variable that is set in the macro (please refer to macro code below)

------------
Macro Code (attached to graphic on Excel sheet)
------------
Public gWhichCalendar

Sub Calendar_Click()
'gWhichCalendar set to 1 = Date range in Column A
Calendar.gWhichCalendar = 1
Calendar.Show
End Sub
------------
End Macro Code
-------------

In the Macro Code above gWhichCalendar is set to 1, thus in the UserForm Code below, the case evaluates the first item. There are distinct Macros attached to several different graphics that are placed on the Excel sheet. In each case the gWhichCalendar is unique.

--------------------
Calendar UserForm Code
--------------------

' gWhichCalendar is the global variable where its' value
' will be evaluated in a case statement and as a result
' will place the date in the appropriate Cell within Excel

Public gWhichCalendar As Integer
----------------------------------------------
Private Sub CalendarIcon_Click()
thisDate = DateValue(TxtSelectedDate.value)

Select Case gWhichCalendar
Case 1
' Increment the Cell in Column A6 and insert
' the date
With Worksheets(1).Range("$A$6&quot.End(xlDown)
.Offset(1, 0).value = thisDate
End With
Case 2
' Insert the Start Date in J4
Worksheets(1).Range("J4&quot = thisDate
Case 3
' Insert the End Date in L4
Worksheets(1).Range("L4&quot = thisDate
End Select

Calendar.Hide

End Sub
--------------------
End Calendar UserForm Code
--------------------

When I inquired about calling a function, which I believe would be a better implementation it would go something like the following example:

-----------
Call function in macro
-----------

Sub Calendar_Click()
'do something (currently which I don't know how to)
' to call a UserForm function. like:
Call CalendarIcon_Click(1)
Calendar.Show
End Sub

Public Function CalendarIcon_Click(Value)
thisDate = DateValue(TxtSelectedDate.value)

Select Case Value
Case 1
With Worksheets(1).Range("$A$6&quot.End(xlDown)
.Offset(1, 0).value = thisDate
End With
Case 2
Worksheets(1).Range("J4&quot = thisDate
Case 3
Worksheets(1).Range("L4&quot = thisDate
End Select

Calendar.Hide

End Function

I hope the items above help clarify what I am attempting to do. As I have figured out a way to use a Global I have a less preferable solution, especially because the global value are not set using the Macros if you don't pick a date once the Calendar UserForm is open. Your thoughts on calling procedures, functions or executing methods contained within the VBA UserForm but being called from a Macro would be appreciated.

Truly,
Steve

 

[RobBroekhuis]

One approach might be to have a hidden control on your userform (say, a textbox called MyCalendar). Then in any other code you can say Calendar.MyCalendar=2.
On your userform code, you can have a change event

sub MyCalendar_Change()
....
end sub

that would get triggered when the other code assigns the value. Does that sound like a reasonable approach?
Rob

 

[SBDev]

Hi Rob (and Forum)

Thanks for the post Rob!

Yes your divergent approach may work. however, I don't see how this helps me communciate between the Excel Macro and the UserForm. In my scenario the user is click on a graphic that is contained with a cell of Excel. This graphic has a Macro assigned to it that currently opens the Calendar UserForm.

You did give me another idea; What about use a remote cell on the sheet that acts as a variable and have the Graphic Macro set the cell's value and then open the UserForm? Then the appropriate user form control can get the value of that Cell and then process it in the case statement.

Let me know if I was off-base on your idea.

Truly,
Steve

 

[RobBroekhuis]

Your remote cell would fill the same purpose as my non-visible userform control. Overall, the two approaches will have the same functionality. I like keeping the value with the userform, but that's a matter of style.
Rob

 

[rmikesmith]

Steve,

I agree with Rob in that his solution seems more self-contained. It doesn't require involving your worksheet which could theoretically result in a "collision" with user-entered data (I realize this is unlikely in practice, except in the largest of spreadsheets).

Another thought: Set the Userform's Tag property from each individual graphic-activated macro using a unique identifier then have the CalendarIcon_Click procedure read this and feed it to the Select Case construct.

HTH,
Mike

 

[RobBroekhuis]

Absolutely! Use the tag if you don't need to have an event triggered based on the value changing. I initially was under the impression that you wanted to trigger something within the userform as soon as the calendar value changed.
Rob

 

[SBDev]

Hi Rob & Mike,

Thank you both for your valuable input! I most like your idea of the UserForm's Tag property, so that is what I have gone with.

Rob I also now understand the functionality of the hidden text field on the UserForm and I too agree that it would keep a 'more self-contained' result - far less likely that non-technical users will mess with the VBA as opposed to the Worksheet.

The Tag property is very similar to the use of a global only it is static in scope, which I like a whole lot more.

Although my functionality has know been served, I still wonder if a UserForm's function can be directly called externally? My guess is that when virus writers started sending macro viruses was the day that function calls through macros were no longer allowed.

Thanks again!
Steve

 

[rmikesmith]

Steve,

From what I can tell, a procedure in a standard code module can call event procedures in a Userform; those of the Userform itself & those of controls on the Userform. This appears to work whether or not the Userform is loaded.

To make this work, you must change the Private keyword to Public.


Regards,
Mike

 

[RobBroekhuis]

Alternatively, you can often trigger an event externally without explicitly calling the event handler. For example, when automating a normally user-driven process, I often use a macro to set some controls on a userform and then

UserForm.OKButton=true

to launch the OKButton_Click event which handles the actual processing done by the UserForm.
Rob

 

[SBDev]

Hi Mike,

Thanks changing the Private keyword to Public did the trick. When in the macro, I also had to reference the Userform when calling the procedure like: Calendar.changeValue

Thank you, case closed

Truly,
Steve

 

[SBDev]

Hi Rob,

That is a cool idea driving the user-driven process with a macro call. Thank you for this idea. Wow using VBA for only a week now and I have learned a tonne.

Thanks.
Steve