Solved: UserForms

[wnazzaro]

I have some code that creates a UserForm. In the Code Module for this form I have a change event for a text box that calls another sub to handle the event (don't really want to code everything and then put it into the code for this form with .InsertLine Count + 1 = "Line of Code" infront of every line). My problem is, I need to know which text box is creating the change event, but because the form is newly created, the error I get is:

Run-tiem error '91':
Object variable or With block variable not set

UserForm1.ActiveControl.Name works fine if the Form exists in the Project, but not for a form created by code.

Maybe I should create a form that resides in the Project all the time and just edit it instead of creating one dynamically, but I imagine there is a way to work with this form, I just don't know how. As always, your help has been amazing and is greatly appreciated.

Bill (where's the tearing my hair out Smilie [Smily]?)

[Killian]

This is the way I would do that... Does this code work with you?[VBA]Sub CreateForm()

Dim frmNewForm As Object
Dim TextBox As MSForms.TextBox
Dim LineCounter As Long

Application.VBE.MainWindow.Visible = False
Set frmNewForm = ThisWorkbook.VBProject.VBComponents.Add(3)
Set TextBox = frmNewForm.Designer.Controls.Add("Forms.TextBox.1")
With frmNewForm.CodeModule
LineCounter = .countoflines
.insertlines LineCounter + 1, "Private Sub TextBox1_Change()"
.insertlines LineCounter + 2, vbTab & "If Me.TextBox1.Text <> " & """""" & " Then TextBoxChange"
.insertlines LineCounter + 3, "End Sub"
End With

End Sub

Sub TextBoxChange()
MsgBox "Lots of code firing here"
End Sub
[/VBA]Although I don't create forms on the fly that often - there's a lot you can do in a form's initialize event to handle most situations and I tend to favour that approach if possible

[wnazzaro]

Here's what I finally did. Instead of this [VBA]'Show the form.
VBA.UserForms.Add(EditForm.Name).Show[/VBA] I changed it to this [VBA]'Show the form.
ShowMyForm[/VBA] With ShowMyForm being a Sub that Loads and Shows UserForm1. By doing this, I don't get the error I used to get that UserForm1 Object variable wasn't set.
I'll try your method and report back.

[wnazzaro]

That does work...

oops. Big Edit.

That only worked because of the change I made to show the form through a call to another sub. When I use the [VBA] 'ShowMyForm
VBA.UserForms.Add(EditForm.Name).Show[/VBA] and try to return the UserForm1.ActiveControl.Name, your code fails just like mine did.

I think the form needs to be loaded, and that can't happen in the same code that creates the form (the form doesn't exist yet to be loaded). Throwing in a call to another sub was simple and works.

I did add the line of code you wrote so that the Event wouldn't fire if the Text Box was empty.

Thanks for your help. I was banging my head on this one all day.

[Killian]

Ahh yes... I didn't consider showing the form - I assumed you'd call createform then show the new form from the calling routine
Anyways, glad you got it working

[jeng1111]

I'm sorry to post against a really old thread, I just wanted to say that this thread helped me solve me my problem today.

I was trying to "me.show" a userform inside its own initialize event, which apparently causes error 91: Object variable or with block variable not set.

Once I took that out and handled the loading and show somewhere else, I was fine.

Thanks!