Get variable value in userform from Class module

jacko2401

New Member
Joined
Aug 24, 2011
Messages
35
I want to monitor when a value changes of several combo buttons on a user form. I have set this up using a class module to capture all the comboboxes on the userform. I have the following code in a class module called “ModComboClass”:
Code:
Public WithEvents ComboGroup As MSForms.ComboBox

Public Sub ComboGroup_Change()
        If strEditMode = "Yes" Then 'editing a fixture mode
            CmdCancel.Enabled = True
        End If

End Sub
In the class module I am monitoring when a combo box value in the user form is changed, check the strEditMode variable value and update the CmdCancel button if strEditMode = “Yes”. The strEditMode variable is stored in the user form and changes depending on changes completed in the form. This value changes correctly from tests completed within the user form. However, when I go through the code step by step in the class module, it never picks up the value of strEditMode from the userform. I have added a debug.print strEditMode line of code before the value of strEditMode is checked in the class module but it comes back with nothing as if it checks but cannot obtain the value. Are there any restrictions within the class module that stops it obtaining the value of the strEditMode variable in the user form? Any help will be greatly appreciated.

The code for the code module “Userform1” is:


Code:
Dim strEditMode As String 'Confirm if in edit mode
Dim cCombo As Controls
Dim ModCombos() As New ModComboClass
Dim ModCombosCount As Integer
 
Private Sub UserForm_Initialize()
For Each cCombo In Me.Controls
        If cCombo.Name Like "CboM_*" Then
            ModCombosCount = ModCombosCount + 1 ‘Add 1 to no of comboboxes count
            ReDim Preserve ModCombos(1 To ModCombosCount) ‘Reset ModCombo array
            Set ModCombos(ModCombosCount).ComboGroup = cCombo ‘Add combobox to ComboGroup in class module
        End If
Next cCombo
stEditMode = “No”

End Sub
 
Private Sub Cbo_Date_Change()
[Code added here for changes required within the form]
strEditMode = “Yes”

End Sub
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi there,

Is this a typo?

Code:
Dim strEditMode As String 'Confirm if in edit mode
Dim cCombo As Controls
Dim ModCombos() As New ModComboClass
Dim ModCombosCount As Integer

Private Sub UserForm_Initialize()
For Each cCombo In Me.Controls
        If cCombo.Name Like "CboM_*" Then
            ModCombosCount = ModCombosCount + 1 ‘Add 1 to no of comboboxes count
            ReDim Preserve ModCombos(1 To ModCombosCount) ‘Reset ModCombo array
            Set ModCombos(ModCombosCount).ComboGroup = cCombo ‘Add combobox to ComboGroup in class module
        End If
Next cCombo
[B][COLOR=#ff0000]stEditMode = “No”[/COLOR][/B]

End Sub

Private Sub Cbo_Date_Change()
[Code added here for changes required within the form]
strEditMode = “Yes”

End Sub
My bet is Yes, and that you do not have Option Explicit at the top of your various modules.

I am not sure what strEditMode is to do, but an alternative would be to set a reference to the Form in the class instance(s), and include a Public Property Let/Get in the Form's module, to allow setting/retrieving the value of strEditMode.

Mark
 

jacko2401

New Member
Joined
Aug 24, 2011
Messages
35
Have you tried moving the declaration of strEditMode to a General module?
Changed the declaration to Module1 and but had to declare it as public, which seems to do the job on initial tests. Thank you so much

Code:
Public strEditMode As String
Do you know why this would make a significant different in terms of making a variable available to a userform and class module? Looks like declaring as public in a general code module changes the scope to the whole project while declaring at the top of a user form (even as public) does not.
 

jacko2401

New Member
Joined
Aug 24, 2011
Messages
35
Hi GTO, yes, this was a typo in this post but was correct in my actual code, sorry. How would I code the Public Property Let/Get option as opposed to the solution of declaring the variable as public in a general module that Andrew suggested and seems to works?

What would the best way be?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Public variables have to be declared in a General module, not a Class module. UserForms and Microsoft Excel Object modules are Class modules.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Public variables have to be declared in a General module, not a Class module. UserForms and Microsoft Excel Object modules are Class modules.
Hi Andrew,

Well... might I respectfully point out that this is not dead-on accurate? Public variables can exist and be referenced (from other modules) in a Class or Object module. If this were not so, the OP's:

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">WithEvents</SPAN> ComboGroup <SPAN style="color:#00007F">As</SPAN> MSForms.ComboBox</FONT>

...would not work. The limitation is of course that an instance of the class/module must be "alive" when the variable's value/object/whatever is set/retrieved.

Mark
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Hi GTO, yes, this was a typo in this post but was correct in my actual code, sorry. How would I code the Public Property Let/Get option as opposed to the solution of declaring the variable as public in a general module that Andrew suggested and seems to works?

What would the best way be?
Last question first: VBA is language and logic. Whilst certainly one may argue merits of a certain "methodology philosophy", I tend to stay away from declaring one way to be the "best". Andrew's suggestion of using a Public variable in a Standard Module is, as far as I am concerned, fine. If you start writing projects with a ton o' code in them, then overuse of Public variables can become nuisance causing.

Anyways, I'm only good for about another 20 minutes (it's very late here), but could you tell me where this control is? CmdCancel.Enabled = True

Just in reading your code, I don't see where the 'parent' form is to the combo box, so I'm not sure how you are changing the Enabled property.

Mark
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
...How would I code the Public Property Let/Get option as opposed to the solution of declaring the variable as public in a general module that Andrew suggested and seems to works?
Off to the rack, and a bit of 'air coding', but by example:

Userform Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Dim</SPAN> strEditMode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> <SPAN style="color:#007F00">'Confirm if in edit mode</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cCombo <SPAN style="color:#00007F">As</SPAN> Control<br><SPAN style="color:#00007F">Dim</SPAN> ModCombos() <SPAN style="color:#00007F">As</SPAN> ModComboClass<br><SPAN style="color:#00007F">Dim</SPAN> ModCombosCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>   <br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Property</SPAN> <SPAN style="color:#00007F">Let</SPAN> EditMode(em <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br>  strEditMode = em<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Property</SPAN><br>  <SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Property</SPAN> <SPAN style="color:#00007F">Get</SPAN> EditMode() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    EditMode = strEditMode<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Property</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>  <br>  For <SPAN style="color:#00007F">Each</SPAN> cCombo <SPAN style="color:#00007F">In</SPAN> Me.Controls<br>          <SPAN style="color:#00007F">If</SPAN> cCombo.Name <SPAN style="color:#00007F">Like</SPAN> "CboM_*" <SPAN style="color:#00007F">Then</SPAN><br>              ModCombosCount = ModCombosCount + 1 <SPAN style="color:#007F00">'Add 1 to no of comboboxes count</SPAN><br>              <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> ModCombos(1 <SPAN style="color:#00007F">To</SPAN> ModCombosCount) <SPAN style="color:#007F00">'Reset ModCombo array</SPAN><br>              <SPAN style="color:#00007F">Set</SPAN> ModCombos(ModCombosCount) = <SPAN style="color:#00007F">New</SPAN> ModComboClass<br>              <SPAN style="color:#00007F">Set</SPAN> ModCombos(ModCombosCount).ComboGroup = cCombo <SPAN style="color:#007F00">'Add combobox to ComboGroup in class module</SPAN><br>              <SPAN style="color:#00007F">Set</SPAN> ModCombos(ModCombosCount).ParentForm = Me<br>          <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">Next</SPAN> cCombo<br>  strEditMode = "No"<br>  <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> </FONT>

Class Module:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>  <br><SPAN style="color:#00007F">Private</SPAN> oForm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">WithEvents</SPAN> ComboGroup <SPAN style="color:#00007F">As</SPAN> MSForms.ComboBox<br>  <br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Property</SPAN> <SPAN style="color:#00007F">Set</SPAN> ParentForm(pf <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)<br>  <SPAN style="color:#00007F">Set</SPAN> oForm = pf<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Property</SPAN><br>  <SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Property</SPAN> <SPAN style="color:#00007F">Get</SPAN> ParentForm() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> ParentForm = oForm<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Property</SPAN><br>  <br><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboGroup_Change()<br>        <SPAN style="color:#00007F">If</SPAN> ParentForm.EditMode = "Yes" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#007F00">'editing a fixture mode</SPAN><br>            MsgBox "CmdCancel.Enabled = True"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Please note that I got away from auto-instancing the Class. See Classes In VBA

Hope that helps a little,

Mark
 

Forum statistics

Threads
1,085,329
Messages
5,382,993
Members
401,812
Latest member
emelyan1379

Some videos you may like

This Week's Hot Topics

Top