Opening a Userform based on Vlookup Variable

[lnmpurdue15]

Hi Everyone.

I need some assistance and I am hoping this group can help.

Overview: I am creating an interactive document when first opened, a user will see a userform with a variety of themes with checkboxes. For the themes they want, they select a checkbox and then depending on the checkbox(es) selected, the VBA code will open the next userform which allows them to select applicable questions.

I have created all the userforms and a command button on the main menu. What I want is when the command button is clicked, the VBA code will identify which secondary userform it needs to open next (I have 16 different userforms) and open it for the user to select questions; however, I am unable to get the secondary userform to open. If I hard code the userform name.show it will open but when I try to open it from a variable string VBA errors out with a compile error, invalid qualifier.

Here is the code I have developed so far:

 Private Sub CommandButton2_Click()

 Dim Ret As String

 Unload Main_Menu

 Sheets("Themes").Activate

 range("A3:A22").Select
 Selection.TextToColumns Destination:=range("A3"), DataType:=xlDelimited, _ 'This section is converting checkbox values (True/False) from the main userform (i.e. the themes) into text for the vlookup to work.
 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
 Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
 :=Array(1, 2), TrailingMinusNumbers:=True

 Ret = Application.VLookup("True", Sheets("Themes").range("$A$3:$C$20"), 3, False) ' the 3rd column over has the associated userform name that I am trying to open

 Ret.Show

I know the first part of the code is working as I want it. If I do a message box and the Ret value, I get the appropriate userform name. So what I don't know is what I need to do to get the identified variable to open the next userform.

Is there a simple way without having to list all the userform options? I tried a select case and that didn't want to work either.

Any suggestions?

 

[RoryA]

Welcome to the forum.

Try this:

Userforms.Add(Ret).Show

 

[lnmpurdue15]

RoryA,

I just tried it and got a runtime error 424 Object required

 

[lnmpurdue15]

User error when I just responded! That totally worked! Thank you soooooo much!

 

[RoryA]

Glad to help!