Userform controlled variables within a macro

Question

Morning Guys,

I have ran into a small roadblock with my project. I'm new to VBA and am trying my best to 'learn by doing' but I cannot seem to get my head around macro/userform interactions.

I have a userform with one textbox and 9 checkboxes. This is supposed to show the userform, allow the user to dictate a sheet name, and (from a list of 9 users) select which is active or not (true or false).

In my main sub, I just have a

Allocator.show

command, as you may have guessed, allocator is my userform name.

Then I've sort of just been trying things so I don't know how right the rest of the userform code is;

Private Sub cbGo_Click()

Unload Allocator

End Sub

Private Sub cboxAlison_Click()

If Me.cboxAlison.Value = True Then
    AlisonYN = True
        Else
    AlisonYN = False
End If

End Sub

Private Sub cboxBeverly_Click()

If Me.cboxBeverly.Value = True Then
    BevelyYN = True
        Else
    BevelyYN = False
End If

End Sub

Private Sub cboxCallum_Click()

If Me.cboxCallum.Value = True Then
    CallumYN = True
        Else
    CallumYN = False
End If

End Sub

Private Sub cboxEllen_Click()

If Me.cboxEllen.Value = True Then
    EllenYN = True
        Else
    EllenYN = False
End If

End Sub

Private Sub cboxGeoff_Click()

If Me.cboxGeoff.Value = True Then
    GeoffYN = True
        Else
    GeoffYN = False
End If

End Sub

Private Sub cboxJames_Click()

If Me.cboxJames.Value = True Then
    JamesYN = True
        Else
    JamesYN = False
End If

End Sub

Private Sub cboxLouise_Click()

If Me.cboxLouise.Value = True Then
    LouiseYN = True
        Else
    LouiseYN = False
End If

End Sub

Private Sub cboxMick_Click()

If Me.cboxMick.Value = True Then
    MickYN = True
        Else
    MickYN = False
End If

End Sub

Private Sub cboxTammy_Click()

If Me.cboxTammy.Value = True Then
    TammyYN = True
        Else
    TammyYN = False
End If

End Sub

Private Sub tbRPName_Change()

End Sub

Private Sub UserForm_Initialize()

Dim GeoffYN, TammyYN, CallumYN, JamesYN, MickYN, AlisonYN, BeverlyYN, LouiseYN, EllenYN As Boolean
Dim RP_Name As String

Me.cboxGeoff.Value = True


Me.cboxTammy.Value = True


Me.cboxCallum.Value = True


Me.cboxJames.Value = True


Me.cboxMick.Value = False


Me.cboxAlison.Value = False


Me.cboxBeverly.Value = False


Me.cboxLouise.Value = False


Me.cboxEllen.Value = False

Me.tbRPName = ""

End Sub

All of the named user variables (xxxxYN) are public in my main module.

These are the variables I want to pull back into my main macro as true or false following the user checking the desired boxes, along with the name as a string, and then continue running the original macro.

Any help would be greatly appreciated, I seem to be taking myself round in circles at the moment!

PS if it helps, my userform looks like this;

UserForm

Cheers,

Callum

Do you get an error message or something? Where is your exact problem? Are your public variables empty? Also, I would use all these ifs in on the cb_GO Button — gizlmo
My variables are all empty, apologies, probably should have let you all know the problem. If I put my IFs inside the cbGo button can I leave the private subs for each of the cbox[nameYN] empty? — CMWells
Actually, all of my variables are empty bar one, whose value doesn't change in accordance with the check boxes on the userform. — CMWells

user3598756 user3598756 · Accepted Answer · 2016-05-09T15:33:48

You wrote "All of the named user variables (xxxxYN) are public in my main module." But we see them declared in userform's Sub UserForm_Initialize, too:

Private Sub UserForm_Initialize()

Dim GeoffYN, TammyYN, CallumYN, JamesYN, MickYN, AlisonYN, BeverlyYN, LouiseYN, EllenYN As Boolean
Dim RP_Name As Stringn
...

even if you declared the same variables as Public in any module, the Userform variables hide their Public namsakes so any Userform setting is not "seen" in other modules

so you'd better remove the Userform dimming statement of the "namesakes" and leave only the Public one

moreover in such a declaration statement as you used, every single variable not explicitly associated with a specific type is implicitly associated to a Variant type

so in the main module you should use a "dimming" statement like follows:

Public GeoffYN As Boolean, TammyYN As Boolean, CallumYN As Boolean, JamesYN As Boolean, MickYN As Boolean, AlisonYN As Boolean, BeverlyYN As Boolean, LouiseYN As Boolean, EllenYN As Boolean

But should all what above get you going, nevertheless I'd recommend you to switch to a "class" approach together with the use of Dictionary object, like follows

in the Allocator code pane place the following code

Option Explicit

Dim chkBoxes() As ChkBx_Class 'array of type "ChkBx_Class" which you define in a Class Module

Private Sub UserForm_Initialize()
Dim nControls As Integer, i As Integer
Dim namesArray As Variant, cbIniValues As Variant

UFInit = True

namesArray = Array("Geoff", "Tammy", "Callum", "James", "Mick", "Alison", "Beverly", "Louise", "Ellen") '<== set here the names to be associated to every CheckBox
cbIniValues = Array(True, True, True, True, False, False, False, False, False) '<== set here the initial values of checkboxes

nControls = UBound(namesArray) + 1 '<== retrieve the number of CheckBoxes you're going to consider in the Form
ReDim chkBoxes(1 To nControls) As ChkBx_Class 'redim the "ChkBx_Class" array
For i = 1 To nControls

    Set chkBoxes(i) = New ChkBx_Class 'initialize a new instance of 'ChkBoxClass' class and store it in the array i-th position
    With chkBoxes(i)
        Set .ChkBox = Me.Controls("CheckBox" & i) 'assign the correct CheckBox control to its "ChkBox" property
        .Name = namesArray(i - 1)  ' assign the Name property of the Checkbox

        .ChkBox.Value = cbIniValues(i - 1) 'set the checkbox correct initial value
         Me.Controls("Label" & i) = .Name ' set the corresponding label caption

         dealersDict.Add .Name, .ChkBox.Value ' fill the dictionary initial pair of Dealer-name/checkbox-value
    End With

Next i

Me.tbRPName.Text = ""

UFInit = False

End Sub


Private Sub cbGo_Click()

Me.Hide

End Sub

add a "Class Module" to your project

either clicking Insert-> Class Module in the VBA IDE main Ribbon menu

or right-clicking anywhere in the VBA IDE Project Window and selecting Insert -> Class Module in subsequent sub-menus

expand the "Class Module" node in the Project Window

if you don't see the Project Window you can open it by clicking View-> Project Window in the main ribbon menu, or press "Ctrl+R"

select the new Class you added (it should be some "Class1" or the likes) and change its name to "ChkBx_Class" in the Property Window "Name" textbox

if you don't see the Property Window you can open it by clicking View-> Property Window in the main ribbon menu or press "F4"

in the Class Module code pane place the following

Option Explicit

'declare class properties: they will be associated in every instance of this class.
Public WithEvents ChkBox As MSForms.CheckBox ' "ChkBox" is now a property of the class of type CheckBox. it's associated to events
Public Name As String

' events associated to ChkBox class property
Sub ChkBox_Click()
    If Not UFInit Then dealersDict.Item(Me.Name) = Me.ChkBox.Value ' set the dictionary pair of Dealer-name/checkbox-value
End Sub

edit your main sub module as follows

Option Explicit

Public dealersDict As New Scripting.Dictionary
Public UFInit As Boolean

Sub main()
myval = "io"
Dim myKey As Variant

Allocator.Show
Unload Allocator

For Each myKey In dealersDict
    MsgBox myKey & ": " & dealersDict(myKey)
Next myKey

End Sub

create a reference to Microsoft Scripting Runtime Library to use Dictionaries.

this is done by choosing Tools➜References command in the Visual Basic Editor (VBE) which pops up a dialog box in whose listbox you are to find "Microsoft Scripting Runtime" to put a check mark next and press OK.

run the main sub

whenever you need to retrieve the boolean value associated to a given name you just have to use

myBool = dealersDict(name)

where name can be:

a string literal with the wanted name ("Alison", "Mick" , ..)
a string variable whose value stores the wanted name, so that somewhere in your code you may have typed:

Dim name as string name = "Mick"

such an approach gives you a lot of flexibility, since you only have to:

set the names and their initial boolean values in those two arrays (namesArray and cbIniValues) in UserForm_Initialize
make sure you have checkboxes named after "CheckBox1", "CheckBox2", and so on as well as have labels named after "label1", "Label2", and so on
make sure that "CheckBoxX" is aligned with "LabelX"
make sure namesArray and cbIniValues have the same items number as labels and checkboxes

Userform controlled variables within a macro

3 Answers