2

I have a number of userforms with static controls on them. I have created a Class Module to apply event handlers to a set of texboxes I am dynamically adding to the userform. When I specifically address a userform from the Class Module I can update the value in the static controls...

IAFStep2c.Controls("chkOptIn").Value = True

But when I try to update the controls with their userform's name in a variable it doesn't work...

Dim oUserFOrm As Object
Dim formName As String
formName = "IAFStep2c"
Set oUserFOrm = UserForms.Add(formName)
oUserFOrm.Controls("chkOptIn").Value = True

I can read the value and other properties of the controls, just not update them. Can someone offer a solution to this?

EDIT

I created another spreadsheet with the class below, same result. TextBox1 and 3 get updated, textBox2 does not.

Sub doStuff()
'MsgBox ("ping")
Dim oUserFOrm As Object
Dim formName As String
formName = "frmTest"
Set oUserFOrm = UserForms.Add(formName)
frmTest.Controls("TextBox1").Value = oUserFOrm.Controls("TextBox2").Name
oUserFOrm.Controls("TextBox2").Value = "PING"
frmTest.Controls("TextBox3").Value = oUserFOrm.Controls("TextBox2").TextAlign

End Sub

James Gray
  • 71
  • 4
  • Why someone downvoted this question? It's properly formulated! – Maciej Los Apr 13 '16 at 05:54
  • I can't reproduce such of behaviour. What version of MS Excel? What's error message? Have you tried to export userform, delete it and import it again? Can you show your class definition? – Maciej Los Apr 13 '16 at 05:58
  • Excel 2013. There is no error message, it just fails to update the field and continues on as if nothing happened. Not tried export/import of the form, I'm new to this so any suggestions will be appreciated. – James Gray Apr 13 '16 at 06:04
  • `UserForms.Add(formName)` will **Add** the UserForm as a **new** UserForm to memory. It takes the UserForm from designer with properties and values provided in designer. It will **not** get an already shown UserForm. – Axel Richter Apr 13 '16 at 07:01
  • Thanks Axel, that makes sense. Still haven't found how to get an already shown userform :( – James Gray Apr 13 '16 at 08:26
  • Please show how the UserForm will be shown. Why you can't assign the UserForm to a variable before it is shown (`formName = "frmTest" : Set oUserFOrm = UserForms.Add(formName)`) and then show it via `oUserFOrm.Show`? – Axel Richter Apr 13 '16 at 09:05
  • We need to see a class definition to be able to reproduce your issue. – Maciej Los Apr 13 '16 at 17:01
  • Axel, I think you have the answer. I was just getting excel to open the forms on file opening but if I do it as you suggest I'll have a handle on it to use. Thanks! – James Gray Apr 13 '16 at 23:08

1 Answers1

1

This is how I do it:

UserForm1.Controls("Textbox1").Text = "123"

At least it works by me, when I am trying it. Office 2010.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Using the form name directly works for me as well, it's when I'm trying to get the form name out of a variable that it's not happening – James Gray Apr 13 '16 at 08:24
  • Ok. So the idea is to create a form and to manipulate a textbox in a userform which is just created? However, take a look at this answer here, probably you would find what you need: http://stackoverflow.com/questions/11519345/creating-form-programmatically-in-the-module-using-vba – Vityata Apr 13 '16 at 08:55