VBA: Dynamically created textboxes - can't access the .tag property.

[MrPloppy]

Hi all -

I've created a number of dynamic textboxes on a form, and created the "_change" function within a separate class so I can have one common function to handle them all (UpdateTotals). Despite me trying everything, I cannot access the .tag property of the textbox. I can see it in the Watch window, but .tag property isn't even in the drop down list. This is weird.

Here's where I set up the dynamic textboxes:

    Dim MarksLabel As Control
   
    GlobalMarkCount = GlobalMarkCount + 1
    Set MarksLabel = Frame1.Controls.Add("forms.textbox.1")

    With MarksLabel
        .Text = Mark
        .Width = 42
        .Height = 24
        .Font.Name = "Calibri"
        .Font.Size = 14
        .Name = "MarksLabel_" & PupilLocation & "_" & QuestionLocation
        .Left = MarkLeftMargin + QuestionLocation * (.Width + 1)
        .top = TopBorder + 3 + (PupilLocation - 1) * 25
        .Tag = "M" & PupilLocation & " " & QuestionLocation
        .TabIndex = GlobalMarkCount
    End With
   
    ReDim Preserve cmdArray(1 To GlobalMarkCount)
    Set cmdArray(GlobalMarkCount).CmdEvents = MarksLabel
   
    Set MarksLabel = Nothing

cmdArray is declared globally as:

Dim cmdArray() As New Class1

And Class1 is:

Option Explicit

Public WithEvents CmdEvents As MSForms.TextBox

Private Sub CmdEvents_Change()
    AssessmentMarkForm.UpdateTotals (CmdEvents.Tag)
End Sub

The error is a Type Mismatch on the above UpdateTotals call (the sub is expecting a string, so should be good). As I say, if I step through and put a watch on CmdEvents.Tag, its there and is correct. I've even tried explicity casting it using CStr, but no joy.

If I begin typing "CmdEvents." in the editor, TAG doesn't come up on the drop down box., despite it being an MSForm.TextBox type. As an aside, I cannot access the .name property either.

Can anyone shed any light?

Many thanks,

MrP.

 

[GWteB]

Hi MrPloppy, welcome to MrExcel.

The code you provided isn't complete ....

The error is a Type Mismatch on the above UpdateTotals call

... so it's hard to determine what's causing the issue you're having.

As it works for me I would suggest to upload your workbook to a public file sharing facility like DropBox , GoogleDrive or WeTransfer and to post the link over here.

FYI, the MSForms.Control object is a generic object and has several properties, such as Name and Tag, and some methods (Move, SetFocus & ZOrder). All derived objects such as TextBox therefore inherit these properties and methods. For some reason unknown to me, only control specific properties and methods appear in the intellisense of the VBE.

 

[MrPloppy]

Much obliged, GWteB!

Unfortunately I can't upload the workbook as it contains sensitive information. The call to UpdateTotals is made from CmdEvents_Change function above. The function UpdateTotals is this:

Public Sub UpdateTotals(Optional ByVal Location As String = "")

<snip>

End Sub

I don't think the code inside is relevant, but let me know if you want to see it.

As far as I'm concerned, the .tag component is a string, and should be accepted as a parameter into UpdateTotals. I agree with you and understand that the Control contains all those properties, which makes me more confused why it's not there! Thanks for the pointer on the VBE not providing all properties - weird, isn't it?

 

[MrPloppy]

Ah ha! I have it!

While the debugger said the error was on the UpdateTotals call:

Private Sub CmdEvents_Change()
    AssessmentMarkForm.UpdateTotals (CmdEvents.Tag)
End Sub

..it was actually within UpdateTotals itself. I only found this by stepping through. For some reason the debugger didn't highlight the actual issue, but returned back to the calling function.

I've fixed the (very stupid) mistake, and it all works. Phew! That got me for a good day, that error!

Thanks again for your help.

MrP.

 

[GWteB]

Glad it's sorted & thanks for the feedback

..it was actually within UpdateTotals itself

I was convinced of that too, which is why I asked for that code. Because experience shows that there is often still something missing, I suggested uploading the workbook right away.

For some reason the debugger didn't highlight the actual issue, but returned back to the calling function.

A UserForm module is a Class module and by default the debugger of the VBE doesn't break in such a module on exceptions occurring within such a module.
You can influence this behavior of the VBE. Go to:
Menu > Tools > Options > General tab > in the Error Trapping section enable this option: "Break in Class module". Done.

 

[MrPloppy]

Another great tip! Many thanks again for your generous help! ??