Need to refer to userforms via variable

[broker]

I googled for hours, but could find no way to refer to a User-defined userform via a variable in Excel VBA.

I created several forms in an Excel 2010 VBA project, and there is some code common to all. In order for them to share the code, I would need to referring to the currently displayed form via a variable. I tried Userforms("formName"), or mso.Userforms("formName"), or VBA.UserForms("formName"). I also tried, when calling a shared code module from within the form, to code as follows:
Dim frm as userform
Set frm = Me

Nothing worked! Does anyone know a solution for this dilemma?

Thank you, all.

 

[Norie]

Why do you need variables for this?

If you have sub/functions with code that refers to a userform you could actually pass the userform object to the sub/functions.

Can you post some examples of the code the userforms are sharing?

 

[broker]

Thank you, Norie. I'm a relative VBA newbie, who has jumped in headfirst with a large project. I want to unload the Menu Form when using frm3, because they both need alot of memory; I can keep the Menu in the background while frm3 is showing, so that frm3 can access data from the Menu's fields. I also have textboxes and combo boxes on alll three, with similar functionality but different data. I appreciate your help. I've build some skeleton code. Thank you again for taking the time to respond.



Here’s my Workbook module.
Private Sub Workbook_BeforeClose(Cancel AsBoolean)
DisplayStandardCommandMenus
End Sub
Private Sub Workbook_Open()
DisplaySpecialCommandMenus
frmMenu.ShowvbModal
End Sub

Here’s my global module.
Public gPrevBorders%, gNewBorders%
Public gFormName$, gMsg$, gCtrlName$(5)
Public gColor As Variant

Public Sub gMsgAndBorders(Optional msg$ =vbNullString, Optional vColor = vbBlue, _
OptionalnewBorders% = 0)
'Each form has a label named"lblMessage"
'Thismodule first clears previous message label and unhighlights all fields whichpreviously had been highlighted.
'It then display a new message in the targetform's message label and highlights form fields with new erroneous entries,whose names had been stored in global array gCtrlName$(5).
'This module may be called from frm2, whichis in the process of unloading, to frmMenu, which frm2 has just loaded.

Dim i%
Dimu As UserForm
Set u = UserForms(gFormName$) 'Set u =VBA.UserForms(gFormName$)
Ifu.lblMessage <> vbNullString Then
u.lblMessage= vbNullString
IfgPrevBorders% > 0 Then
For i% = 1 TogPrevBorders%
u.Controls(gCtrlName$(i%)).BorderStyle= fmBorderStyleNone
Nexti%
gPrevBorders% = 0
End If
End If
Ifmsg$ <> vbNullString Then
u.lblMessage= msg$: .ulblMessage.ForeColor = vColor
EndIf
IfnewBorders% > 0 Then
Fori% = newBorders% To 1 Step -1
With u.Controls(gCtrlName$(i%))
.BorderStyle= fmBorderStyleSingle
.BorderColor= vbRed
.SetFocus
EndWith
Nexti%
gPrevBorders%= newBorders%
EndIf
End Sub
Public SubgIgnoreSpecialCharacters(KeyAscii As msforms.ReturnInteger)
IfKeyAscii < 32 Or (KeyAscii > 126 And KeyAscii <> 160) Then KeyAscii= 0
End Sub
Public Sub gFileOpenError(msg$)
DimuForm As Object, bLoadedMainForm As Boolean
MsgBox(msg$)
IfuForm.Name = "frm2" Or uForm.Name = "frm3" Then
frmMenu.ShowvbModal
UnloaduForm
gMsgAndBordersmsg$
Else
MsgBoxmsg$
Unload frmMenu
EndIf
ThisWorkbook.Closesavechanges:=False
End Sub




Here is one class module, instantiated from all forms.

Public WithEvents gClsTXT1 Asmsforms.TextBox
Private Sub gClsTXT1_KeyPress(ByValKeyAscii As msforms.ReturnInteger)
CallgIgnoreSpecialCharacters(KeyAscii)
CallgMsgAndBorders
End Sub
Private Sub gClsTXT1_MouseDown(ByVal ButtonAs Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
CallgMsgAndBorders
End Sub

Here is another class module, instantiated from all forms.

Public WithEvents gClsCBOAs msforms.TextBox
Private Sub gClsCBO_KeyPress(ByVal KeyAsciiAs msforms.ReturnInteger)
CallgMsgAndBorders
End Sub
Private Sub gClsCBO_MouseDown(ByVal ButtonAs Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
CallgMsgAndBorders
End Sub

This is code in frmMenu.
Private Sub UserForm_Initialize()
gFormName$= "frmMenu"
gInstantiateClassesMe
gMsgAndBordersmMsg$, mColor, mNewBorders%
End Sub
Private Sub CallForm2()
frm2.ShowvbModal ‘keep frmMenu in the background
End Sub
Private Sub CallForm3()
frm3.ShowvbModal
UnloadMe
End Sub
Private Sub UserForm_Terminate()
ThisWorkbook.Close
End Sub

This is code in frm2.

Private Sub UserForm_Initialize()
'keepfrmMenu in background, so can access its fields from frm2.
gFormName$= "frm2"
gInstantiateClassesMe
gMsgAndBordersgMsg$, gColor, gNewBorders%
End Sub

This is code in frm3.

Private Sub UserForm_Initialize()
gFormName$= "frm3"
gInstantiateClassesMe
gMsgAndBordersmMsg$, mColor, mNewBorders%
End Sub
Private Sub UserForm_Terminate()
frmMenu.ShowvbModal
End Sub

 

[broker]

Thank you, again, Norie. I've been working on this in my spare time. I applied your recommendation of passing controls instead of forms. It took me a while to figure it out, but it's working quickly and properly! You were a great help.