VBA - Can not seem to address dynamic checkbox?

[NessPJ]

I have a question about Dynamic Objects on my Userform.

I use this code to generate a list of checkboxes (and output their generated name to a cell). Now i would like to use a piece of code inside my UserForm_Activate module to select one of these Checkboxes
after it has been generated. For some reason Excel is not letting me and i keep getting an Error 'Object required'.
Can anyone please help me and tell me if i am doing something wrong?

This is the code i use for generating the dynamic checkboxes:

Option Explicit


Public MutDatum, MutTijd, MutWeek, MutDag As String
Public MutDienst, MutTechnieker, MutTRow, MutTBox, MutActiviteit, MutTijdEenheden, MutOmschrijving, MutVervolgActie, MutVervolgActieCode As String


Private Sub UserForm_Activate()

Dim OmschrijvingWaarde, LastRowTechnieker, LastRowActiviteit, LastRowTag As String
Dim RegelnrRij, RegelnrGevonden As String

Dim curColumn       As Long
Dim i               As Long
Dim chkBox          As MSForms.CheckBox
Dim Optionbutton    As MSForms.Optionbutton

LastRowTechnieker = ThisWorkbook.Sheets("Masterdata").Range("B31").End(xlUp).Row

InputBox RegelnrRij


'Lijst met Technieker Checkboxes wordt dynamisch opgebouwd.


curColumn = 3 'Set your column index here


For i = 5 To LastRowTechnieker
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & (i - 4))
    chkBox.Caption = ThisWorkbook.Sheets("Masterdata").Cells(i, curColumn).Value
    chkBox.Left = 12
    chkBox.Top = 8 + ((i - 4) * 16)
    chkBox.GroupName = "Technieker"
    ThisWorkbook.Sheets("Masterdata").Range("E" & i) = chkBox.Name
Next i


i = 0

            MutTechnieker = ThisWorkbook.Sheets("Data Invoer").Range("AD" & RegelnrRij)
            MutTRow = Zoeken(MutTechnieker, "Masterdata", "C31", "C")
            MutTBox = ThisWorkbook.Sheets("Masterdata").Range("E" & MutTRow).Value
            MsgBox Me.MutTBox.Value


End Sub

Public Function Zoeken(ZoekWaarde, ZoekSheet, LaatsteCellVoorZoeken, KolomVoorZoeken As String)


'De volgende routine creeërt een functie en zoekt een ingegeven string van onderen naar boven in de tabel (zodat de meest recente wordt gevonden).
'Vervolgens wordt het Regelnummer (Rownumber) teruggemeld als gevonden waarde.


Dim StringRowNumber As Integer, LaatsteCellZoekVeld, code As String


    LaatsteCellZoekVeld = ThisWorkbook.Sheets(ZoekSheet).Range(LaatsteCellVoorZoeken, ThisWorkbook.Sheets(ZoekSheet).Range(LaatsteCellVoorZoeken).End(xlUp)).Row
      
    If ZoekWaarde = "" Then GoTo StopMetZoeken
    
    For StringRowNumber = LaatsteCellZoekVeld To 1 Step -1
         code = ThisWorkbook.Sheets(ZoekSheet).Range(KolomVoorZoeken & StringRowNumber)
         If code = ZoekWaarde Then
         Zoeken = StringRowNumber
            Exit For
         End If
    Next
  
StopMetZoeken:


End Function

 

[HackSlash]

You set MuTBox using ".value" which makes it a string. Then you call Me.MutTBox.Value but ".value" is not a member of string type.

Replace Me.MutTBox.Value
With MutTBox

 

[HackSlash]

NOTE: You cannot declare multiple variables as one type as you have done here. I used to do this until I learned that it wasn't working when I got a type mismatch.

Each variable needs it's own type. The ones that don't will default to type "VARIANT" which wastes memory

If you want these all to be strings:

Public MutDatum, MutTijd, MutWeek, MutDag As String

You must declare like this

Public MutDatum As String, MutTijd As String, MutWeek As String, MutDag As String

 

[NessPJ]

NOTE: You cannot declare multiple variables as one type as you have done here. I used to do this until I learned that it wasn't working when I got a type mismatch.

Each variable needs it's own type. The ones that don't will default to type "VARIANT" which wastes memory

If you want these all to be strings:

Public MutDatum, MutTijd, MutWeek, MutDag As String

You must declare like this

Public MutDatum As String, MutTijd As String, MutWeek As String, MutDag As String

Thanks a lot for your reply!

Do you mean i can not declare multiple variables as one type, when they are public? or always?
I have only started to use public variables in this routine for the first time...
(I have never had problems with this inside routines tbh).

 

[HackSlash]

I mean that your variables are variants and not strings. This is true for any declaration (dim, shared, public, private). Usually it will work because you can run VBA with every variable defined as a variant or object. It won't help you understand VB and it will cause problems if you ever start to use VB.NET

You already use Option Explicit so it sounds like you want to control your variables.

 

[NessPJ]

Alright, i changed my variables as well.

Now when i do

MsgBox MutTBox

It reports "Checkbox_6" which is the name of the object i want to select.
If i then try

Registratie.MutTBox.Select       '(or SetFocus)

I instantly get an error message saying Invalid Qualifier.
How would i go about actually selecting the checkbox of which i retreived the name?
I tried declaring MutTBox both as Variant and Object.

 

[HackSlash]

Now you are attempting to use the String as an object again. If you want MutTBox to be an object you can't put a string in it. You can't go back and forth from using it as a string and an object. You also can't put an object in to a cell. So if you must put the value in to a cell as a String then it will forever be a string. If you could continue to use your "chkBox" object then you wouldn't have this problem.

A good rule of thumb is to use the same method for get that you used to set. So, let's look at how you took an object name and put it in a string:

Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & (i - 4))

So the object is "Me.Controls" so you would need to call the control by an object that accepts a string for the name like this:

 Me.Controls(MutTBox)

I can't test this because your code doesn't compile for me at all. I can't tell if your object is actually "Me.Controls" or "Forms.CheckBox" or "Me.Controls.Forms.CheckBox" but you need to get the object and use the string as it's name.

 

[NessPJ]

Thanks for all the help and explanations (I'm self taught and i usually try to do things the best way i can, but sometimes i just get stuck).

I added the following code to do what you suggested and it works!

Dim MutTchkBox      As MSForms.CheckBox

            Set MutTchkBox = Me.Controls(MutTBox)
            MutTchkBox.Value = True

Thanks a lot!