Second time posting here...

I have a main Sub in Excel VBA that uses a couple of Userforms, and from the main Sub I need to call another Sub that uses the same forms. The main Sub needs to tell the other Sub which form to control.

The main Sub already shows the form by using myForm.show and then changes things like for instance myForm.label1 = "Hello". That's all very easy. Where I'm getting stuck right now is passing the name to the Sub and having the Sub use it correctly. Here's a simplified sample of the code that I wrote:

Private Sub Mainsub()
   myForm.show
   myForm.label1 = "Hello"
   call otherSub("myForm")
End Sub

Private Sub otherSub(frm as String)
   Dim objForm as Object
   Set objForm = UserForms.Add(frm)
   objForm.label1 = "Byebye"
End Sub

When I do this, I don't get any errors, but the label on the form doesn't change and debugging with F8 is also useless in this case.

CodePudding user response：

As an alternative, you can use CallByName to get the form object by name.

Function Form(Name As String) As Object
  Set Form = CallByName(UserForms, "Add", VbMethod, Name)
End Function

Usage: (including how to reference the controls on the form by name)

Sub Test()
  With Form("UserForm1")
    .Caption = "This is the form title"
    .Controls("Label1").Caption = "Byebye"
    .Show
  End With
End Sub

CodePudding user response：

I am not sure I understand the question correctly, but here are some pointers.

1. Forms are objects

What you are doing is using the default instance of myForm, which should be avoided. Instead, it's better to new-up the form, like this:

dim frm as myForm ' Assuming myForm is the name of the form in the name field of the properties.
set frm = new myForm ' Now you have an instance of myForm, which you can pass along to other subs.
frm.label1 = "Hello"
frm.show

othersub frm


end Sub
public sub otherSub(byref frm as myForm)
frm.label1 = "byebye"
frm.show
end sub

2. What to do if you have myForm1 and myForm2, i.e. two different forms that have different types, and you want to be able to pass one or the other to OtherSub, and change the label.

What I would do in that case is create an interface with a changeLabel(byval lbl as string) method.

To do that, you need to create a class module named ILabelModifiable (for example) where you would declare:

public sub changelabel(byval lbl as string)
public sub show() ' You need this method to be able to show the form.

Then, in each of myForm1 and myForm2, you would add, at the top of the code-behind: Implements ILabelModifiable and in the body of the form, create the following sub

private sub ILabelModifiable_changelabel(byval lbl as string)
    me.label1 = lbl
end

private sub ILabelModifiable_show()
    me.show
end  

Once you've done this, the main code becomes:

dim frm as iLabelModifiable 
set frm = new myForm1 

frm.changelabel "Hello"
frm.show

othersub frm

end Sub

public sub otherSub(byref frm as ILabelModifiable)
frm.changelabel "byebye"
frm.show
end sub

An added bonus of this method is that your code does not need to know what the label is actually called. It could be called label1 in Myform1 and label2 in myForm2, it would not matter, the code only calls changeLabel.

3. Miscellaneous

In your example you do not control for what the user does, e.g. you don't check if the user has clicked OK, or has cancelled either via the cancel button or by clicking the 'X' on the top right of the window. I assume that this is because you wanted to keep the example as to the point as possible, but just in case, do not forget to do this. Note that if the form is destroyed in firstSub (e.g. because you have set it to nothing instead of hiding it when the user clicked on a cancel button or the 'X'), the otherSub will error out.