Dynamic VBA UserForm controls not updating

[ChrisM]

This is a tough one! Spent all day on it. I am creating a VBA userform with a couple controls at run time. Everything works fine, but I CANNOT get the controls to reflect changes, as in:

Textbox1.Text = "old"
gets updated to
Textbox1.Text = "new"

Graphically, the control still says "old", but if I do a debug.print the value returned is "new". It's like somehow I need to force a refresh or paint or something (I already tried userform.repaint)

Would appreciate any thoughts on this. All code is run from a VBA Module and uses the MSForms.Dll reference:

Private Sub LoadMultiForm()
    Dim TempForm  'As VBComponent
    Dim Lbl1 As MSForms.Label
    
'   Hide VBE window to prevent screen flashing
    Application.VBE.MainWindow.Visible = False

'   Create the UserForm
    Set TempForm = Application.VBE.ActiveVBProject.VBComponents.Add(3)
    With TempForm
        .Properties("Height") = 303
        .Properties("Width") = 651
        .Properties("Name") = "NewForm"
        .Properties("Caption") = "Update Table Links"
    End With
    
    'add header labels
    Set Lbl1 = TempForm.Designer.Controls.Add("Forms.Label.1")
    With Lbl1
        .Left = 6
        .Top = 34
        .Height = 18
        .Width = 126
        .BorderStyle = fmBorderStyleSingle
        .Caption = " Local Table"
    End With

...
...
..
'   Show the form
    VBA.UserForms.Add(TempForm.Name).Show

That's the simple version of the code. Now try changing the caption of Label1:

NewForm.Label1.Caption = "test"
I've also tried: NewForm!Label1.Caption too

The ONLY way I could get the caption to physically change was to put this line of code above the VBA.UserForms...Show line at the end of my code. Putting the code anywhere else, in the Module, in the UserForm Module (as it's own Sub and tried in an Event procedure), has no visual effect (though again, if you debug.print the value really is changing)

My last idea is to try to hide and unhide the entire form, hoping that would force a refresh, but I'm getting some modal window errors I haven't quite worked through yet.

Has anyone ran across this??

 

[Jaafar Tribak]

Try this and see if it works for you : (changes in Blue)

Private Sub LoadMultiForm()

    Dim TempForm  'As VBComponent
    Dim Lbl1 As MSForms.Label
  
'   Hide VBE window to prevent screen flashing
    Application.VBE.MainWindow.Visible = False

'   Create the UserForm
    Set TempForm = Application.VBE.ActiveVBProject.VBComponents.Add(3)
    With TempForm
        .Properties("Height") = 303
        .Properties("Width") = 651
        .Properties("Name") = "NewForm"
        .Properties("Caption") = "Update Table Links"
    End With
    
    'add header labels
    Set Lbl1 = TempForm.Designer.Controls.Add("Forms.Label.1")
    With Lbl1
        [B][COLOR=#0000ff].Name = "MyLabel"[/COLOR][/B]
        .Left = 6
        .Top = 34
        .Height = 18
        .Width = 126
        .BorderStyle = fmBorderStyleSingle
        .Caption = " Local Table"
    End With
'...
'...
'..
'   Show the form
    VBA.UserForms.Add(TempForm.Name).Show [B][COLOR=#0000ff]vbModeless[/COLOR][/B]


[B][COLOR=#0000cd]VBA.UserForms(0).Controls("MyLabel").Caption = "test"[/COLOR][/B]

End Sub

I personally would avoid at all costs using the designer class to create a userform and its controls

 

[Kyle123]

You shouldn't be adding them like this as Jaafar mentions, you should be using the Controls.Add method of the userform/form

 

[ChrisM]

Thanks Jaafar, I didn't get much time to work on this today, but I did try loading the form with vbModeless flag and nothing happens, the form won't load, the screen flashes for a second but that's it, no errors or anything.

I'll try your alternate way to assign the caption when I'm back at work next week.

Kyle, since the form and controls are added at run-time, I'd have to add the controls.add code programatically to the userform's Initialize event, I can give that a shot as well on Monday and see if it works any better. The controls do work fine now, and even respond to a Keypress event, they just won't graphically change.

 

[Kyle123]

Why do you need to add the code at runtime. It's not good practice to add code at runtime, what are you actually trying to do? Adding controls doesn't normally require dynamic coding

 

[ChrisM]

I'm making a userform-based macro for some coworkers, and for ease of installing the macro in their databases I was trying to limit the overhead to just importing a single Module. Then they just need to launch my code by running one of Access' Macros. If I build the form at design time they'll need to import two modules, so I was just trying to simplify that piece of it. And the form is very simple, just 4 labels and a listbox.

Hmm, maybe I could write all the code in a UserForm module, that might work. If I can use an Access Macro "Run Code" command to do a UserForm.Show then put my code in the Intialize event that could work. I'll give that a shot.

 

[ChrisM]

Well it's Monday so I got back to working on this. Tried Jafaar's line:

VBA.UserForms(0).Controls("MyLabel").Caption = "test"

and it had no effect. It must have something to do with the order of code execution. I've figured out how to get around it. I put the code to update the control in to the userform's code, using VBE tools. The example below allows me to refresh the display of a listbox after a keypress event:

....
Set TempForm = Application.VBE.ActiveVBProject.VBComponents.Add(3)
....
Set NewList = TempForm.Designer.Controls.Add("Forms.ListBox.1")
    With NewList
        .Name = "List1"
.....
.....
With TempForm.CodeModule
        X = .CountOfLines
        .InsertLines X + 5, "Private Sub List1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)"
        .InsertLines X + 6, "Select Case KeyAscii"
        .InsertLines X + 7, "Case 108 'l"
        .InsertLines X + 8, "UpdateTable List1.ListIndex"
        .InsertLines X + 9, "   UpdateList1"                              <------------------------------- This is what I added. calls the sub below 
        .InsertLines X + 10, "Case 100 'd"
        .InsertLines X + 11, "UpdateDatabase"
        .InsertLines X + 12, "End Select"
        .InsertLines X + 13, "End Sub"

        .InsertLines X + 14, "Public Sub UpdateList1"        <------putting the sub here, in the VBE of the userform itself seems to do the trick
        .InsertLines X + 15, "List1.Clear"
        .InsertLines X + 16, "List1.Column = alocaltables"
        .InsertLines X + 17, "End Sub"

I know this is hacked up and ugly, but it appears to be working, and meets my goal of keeping all the code in a single code module.