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

MrPloppy

New Member
Joined
Feb 9, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
    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:

VBA Code:
Dim cmdArray() As New Class1

And Class1 is:

VBA Code:
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 9, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

New Member
Joined
Feb 9, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Ah ha! I have it!

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

VBA Code:
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.
 
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
Glad it's sorted & thanks for the feedback (y)

..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

New Member
Joined
Feb 9, 2022
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Another great tip! Many thanks again for your generous help! ??
 

Forum statistics

Threads
1,175,488
Messages
5,897,691
Members
434,673
Latest member
kktfc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top