VBA - Using a variable to open a form

[cmhoz]

Hi All -

I am building a system that essentially walks the user through a series of flow chart questions.

eg: Was the batch number found?

If NO then
If YES then

Depending on the answer, I want to open a certain form when they click next. I'm trying to do this by assigning the name of the form to a variable (via a lookup on a worksheet).

eg: nextform = "frmNext"

But when I then say.... nextform.show, it says I need an object. Clearly, this is because I haven't defined 'nextform' as being a form, but I can't find a way to do this??

Help?!?

 

[Trebor76]

Hi ,

As long as you have a form called 'frmNext' (check and create/change if necessary from the Visual Basic Editor) the following will do the trick:

Sub Macro2()

    Dim objNextForm As Object
    
    Set nextform = frmNext
    
    nextform.Show

End Sub

HTH

Robert

 

[cmhoz]

This is what I have at the moment...

________________________________________
Private Sub btnNext_Click()

Dim nextform As Object

mylookup = Me.lblBatchOutcome.Caption
formname = WorksheetFunction.VLookup(mylookup, Range("findnextform"), 3, False)

Set nextform = formname
nextform.Show

End Sub
________________________________________

It works fine if I type the name of the form into "Set nextform = __"

However, when I try to use 'formname' here, I get a type mismatch.

I need to be able to use the variable and not the form name itself because the form I want to open will be determined by what is in the caption on the current form.

 

[ZVI]

Hi,

It's possible - see the code below:

' Get UserForm object defined by its string name
Function Form(Name As String) As Object
  Set Form = CallByName(UserForms, "Add", VbMethod, Name)
End Function

Sub Test()
  Dim strFormName As String
  strFormName = "UserForm1"  ' <-- replace by your lookup code instead
  Form(strFormName).Show
End Sub

Regards,
Vladimir

 

[cmhoz]

Ha!!

That's what I was after... works a charm - thanks much!

 

[ZVI]

Ha!!

That's what I was after... works a charm - thanks much!

You are welcome!