If Then Else statements resulting in multiple errors

[basicallyawesome]

I am relatively new to VBA, which I've been using to solve basic problems and automate reasonably complex tasks for my workplace. My most complex one (so far!) is actually two documents: a data source housed in Excel and then the merge letter in Word. Rigging the Word document to do what I want it to was fairly easy, but I seem to have hit a wall with Excel.

While I understand IF statements as they pertain to formulas, I have yet to write one that actually works in VBA, and I think I require 2. Unfortunately I have no "real world" example because my work is protected under privacy legislation and confidentiality agreements, but the first one is basically:

If [Combobox].value = "Blurb2" then
Show.[B2Userform]
Elseif [Combobox].value = "Blurb3" then Show.[B3Userform]
...and so on, through roughly 18 "blurbs". Per various posts on here and other Excel/VBA forums, the code looks right but when I go to run it, I either get End If Without Block If or Else If Without If errors. I am completely lost on what I'm doing wrong, I've read basically everything and I still can't fix my problem.

The second one I need is more complex, and this didn't bring up any errors but also didn't do what I needed it to. It did... nothing.
If [Combobox].value = "Blurb2" then
[MainUserform].Hide
Elseif [Combobox].value = "Blurb3" then [MainUserform].Hide
...and again so on through the same roughly 18 "blurbs". The more I think about this one, the more I wonder if I truly need it. Will having the main user form in the background of the additional info/blurb userforms use a lot of memory overall?

If it makes a difference, both are supposed to be triggered by command buttons.

Thank you so much for any help you can provide! I've been reading this forum for a while now and have found it an invaluable resource.

 

[Norie]

I think you'd probably be better of with a Select Case statement for your first issue.

Select Case Combobox.Value
    Case "Blurb2"  
        B2Userform.Show
    Case "Blurb3
        B3Userform.Show

    ' etc

End Select

Not quite sure there's much difference between the 1st and 2nd issue, the only 'extra' part is hiding the main form.

Select Case Combobox.Value
    Case "Blurb2"  
        MainUserform.Hide 
        B2Userform.Show
    Case "Blurb3
        MainUserform.Hide
        B3Userform.Show

    ' etc

End Select

 

[My Aswer Is This]

I would try this:
You should see from this how to do your second part:

Private Sub CommandButton1_Click()
'Modified  7/10/2018  5:39:15 PM  EDT
If ComboBox1.Value = "George" Then
George.Show
ElseIf ComboBox1.Value = "Bob" Then Bob.Show
End If
End Sub

I would wonder why you need 18 different Userforms.

Are you sure you need to use 18 different Userform?
Maybe you would like to share with us what your ultimate goal here is.
I have done a lot with Userforms and never needed more then one.

Have you ever used UserForm Multipages

This could provide a way for you to have what appears as 18 Userforms but what is really only one Userform with 18 different ways to view it.

Sort of like having one Workbook with 18 different sheets.

You I assume would never have 18 different Workbooks with just one sheet per workbook.

 

[basicallyawesome]

Thanks, Norie!

I would wonder why you need 18 different Userforms.

Are you sure you need to use 18 different Userform?
Maybe you would like to share with us what your ultimate goal here is.
I have done a lot with Userforms and never needed more then one.

Have you ever used UserForm Multipages

This could provide a way for you to have what appears as 18 Userforms but what is really only one Userform with 18 different ways to view it.

If I reworked it as you suggest, how would I trigger the different pages based on what blurb was selected?

 

[My Aswer Is This]

When using Userforms with multipages as I suggested.
You would show the main userform

And then just like having many sheets in one workbook
You do not hide and show userforms.

You normally do not want to hide and unhide every sheet in your workbook correct? You just click on the sheet you want to see at the moment.
The Userform you want to work on you select from the Multipage Tab. It's not really new userform but it can look just like one.
So if you want to see sheet5 you would normally just click on sheets 5 tab

The same way with Useform Multipages

Think of the Userform as a workbook
You want to work on sheet 5 you click on the sheet 5 tab

This is the same with Userform with multipages.

You have one Userform with many pages.

Want to see and work on a certain Mutipage tab click on the Multipage Tab.

I suggest you create a New Userform look and you will see a control named Multipage

Put this control on your userform and you should see how it works,

You can make the Multipage as large as you want.
Put the controls you want on each Multipage Tab

Then you will see how things work.

Get back with me if you have more questions.
You will see this is a nice thing

A lot easier then creating 18 Userforms.

 

[basicallyawesome]

But if I don't want to have a hide/show scenerio and just want to trigger the tab... could i do that? I'm trying to make this as fool proof as possible. If they have to click a tab to access the correct blurb fill-in, they're going to say it's too many steps/too complex to show people new to the task and it's going to die on the vine. (Sad but true story.) Could I use something like the case coding Norie put above (which has been awesomely helpful, btw) and then something like Multipage.Value = 1? Like...

   Select Case Combobox.Value
Case "Blurb2"  
        Multipage.Value = 1
    Case "Blurb3
      Multipage.Value = 2

Sorry if this is messy, this is my first time posting code on a forum.

 

[My Aswer Is This]

To begin with have you setup a Userform with Mutipages?

If not set one up and see if something like this this would work for you.
If so then we can work from there. I'm not saying you need to do this.
I was just making a suggestion.

You may not like that Ideal.
And if you already have 18 Userforms all designed the way you want then changing over to Multipages would maybe be more work then you want to deal with.

 

[My Aswer Is This]

If you do decide to use Multipage put this script in your Combobox
This assumes your Userform is name UserForm1.
Modify name if needed.

Private Sub ComboBox1_Change()
'Modified  7/11/2018  11:00:57 AM  EDT
    With UserForm1
        Select Case ComboBox1.Value
            Case "Blurb1"
                .MultiPage1.Value = 0
                .Show
            Case "Blurb2"
                .MultiPage1.Value = 1
                .Show
            Case "Blurb3"
                .MultiPage1.Value = 2
                .Show
        End Select
    End With
End Sub

 

[Norie]

If you have a combobox populated with Blurb1, Blurb2 etc then you might not even need Select Case or If/End If to select the correct tab.

Private Sub ComboBox1_Change()
Dim idx As Long

    idx = ComboBox1.ListIndex

    If idx = -1 Then Exit Sub ' nothing selected/entered from list in combobox

    Load UserForm1

    UserForm1.MultiPage.Value = idx

    UserForm1.Show

End Sub

 

[My Aswer Is This]

Norie had a nice plan.
But I tested it and it had a error.

UserForm1.MultiPage.Value = idx
​

Must be:

UserForm1.MultiPage1.Value = idx
​

See I added a 1
Here is Norie's script modified.

Private Sub ComboBox1_Change()
'Modified  7/11/2018  2:22:48 PM  EDT
Dim idx As Long
idx = ComboBox1.ListIndex
    If idx = -1 Then Exit Sub ' nothing selected/entered from list in combobox
        Load UserForm1
        UserForm1.MultiPage1.Value = idx
        UserForm1.Show
End Sub

Great ideal Norie.