Nested If Then ElseIF keeps returning Compile Error

[Vanish29]

Hello,
I am attempting to automate Custom Views on a form.

Sub CustomViews()
'
' Designed to make the custom views automated
'
If Sh1.Range("G4") = "All Products + Metered" Then ActiveWorkbook.CustomViews("All Products + Metered").Show
ElseIf Sh1.Range("G4") = "All Products Non-Metered" Then ActiveWorkbook.CustomViews("All Products Non-Metered").Show
ElseIf Sh1.Range("G4") = "Bill Pay + S2" Then ActiveWorkbook.CustomViews("Bill Pay + S2").Show
ElseIf Sh1.Range("G4") = "Bill Pay Only" Then ActiveWorkbook.CustomViews("Bill Pay Only").Show
ElseIf Sh1.Range("G4") = "Billing + Submetered" Then ActiveWorkbook.CustomViews("Billing + Submetered").Show
ElseIf Sh1.Range("G4") = "Billing Non Submetered" Then ActiveWorkbook.CustomViews("Billing Non Submetered").Show
ElseIf Sh1.Range("G4") = "Billing + Submetered & Bill Pay" Then ActiveWorkbook.CustomViews("Billing + Submetered").Show
ElseIf Sh1.Range("G4") = "Billing + Non Submetered & Bill Pay" Then ActiveWorkbook.CustomViews("Billing Non Submetered").Show
Else: ActiveWorkbook.CustomViews("All Products + Metered").Show
End If
'
End Sub

Everytime I run it, I get Compile Error: Else without If, but I cannot seem to find the issue.
My first guess was that the issue was the indentation. I tried using Smart Indenter but it still produces the same error.
I am completely uncertain how to proceed and searching forums has not produced useful guidance.

 

[6StringJazzer]

The problem is trying to put condition/action on the same line in a cascading If/ElseIf/Else. (Also, the compiler doesn't care about indentation.) Do this:

If Sh1.Range("G4") = "All Products + Metered" Then
   ActiveWorkbook.CustomViews("All Products + Metered").Show
ElseIf Sh1.Range("G4") = "All Products Non-Metered" Then
   ActiveWorkbook.CustomViews("All Products Non-Metered").Show
ElseIf Sh1.Range("G4") = "Bill Pay + S2" Then
   ActiveWorkbook.CustomViews("Bill Pay + S2").Show
ElseIf Sh1.Range("G4") = "Bill Pay Only" Then
   ActiveWorkbook.CustomViews("Bill Pay Only").Show
ElseIf Sh1.Range("G4") = "Billing + Submetered" Then
   ActiveWorkbook.CustomViews("Billing + Submetered").Show
ElseIf Sh1.Range("G4") = "Billing Non Submetered" Then
   ActiveWorkbook.CustomViews("Billing Non Submetered").Show
ElseIf Sh1.Range("G4") = "Billing + Submetered & Bill Pay" Then
   ActiveWorkbook.CustomViews("Billing + Submetered").Show
ElseIf Sh1.Range("G4") = "Billing + Non Submetered & Bill Pay" Then
   ActiveWorkbook.CustomViews("Billing Non Submetered").Show
Else
   ActiveWorkbook.CustomViews("All Products + Metered").Show
End If

 

[Vanish29]

Hey 6StringJazzer,

I had tried this before and figured it wasn't the answer, but I see now that the defaults of VBA don't actually work, so I defined sh1 and it worked just fine when testing.
However, it doesn't seem to be actively running.
Example: I change the cell from All Products + Metered to Billing Non Submetered but the custom views don't change unless I manually run the macro again.
Thoughts?

 

[6StringJazzer]

First things first. Do you still have the compiler error?

the defaults of VBA don't actually work

No idea what you mean by that. If you fixed a problem by defining Sh1, that's not related to defaults. I strongly recommend to everyone that they use Option Explicit and declare variables. Doing so prevents a lot of bugs and runtime errors.

the custom views don't change unless I manually run the macro again.

Well, that does seem to be how your macro is written. Is it called by Sub Worksheet_Change?

 

[Scott Huish]

I've never used CustomViews but why not use the more straightforward Select Case?

Sub CustomViews()
'
' Designed to make the custom views automated
'
Dim cv As String

Select Case Range("G4")
    Case "All Products + Metered": cv = "All Products + Metered"
    Case "All Products Non-Metered": cv = "All Products Non-Metered"
    Case "Bill Pay + S2": cv = "Bill Pay + S2"
    Case "Bill Pay Only": cv = "Bill Pay Only"
    Case "Billing + Submetered": cv = "Billing + Submetered"
    Case "Billing Non Submetered": cv = "Billing Non Submetered"
    Case "Billing + Submetered & Bill Pay": cv = "Billing + Submetered"
    Case "Billing + Non Submetered & Bill Pay": cv = "Billing Non Submetered"
    Case Else: cv = "All Products + Metered"
End Select

ActiveWorkbook.CustomViews(cv).Show
End Sub

 

[6StringJazzer]

I agree, Select Case is better for this situation.

 

[Vanish29]

@6StringJazzer
Apologies for not stating more clearly, but I am no longer getting the compiler errors, nor any other errors.
As for the comment on the default, there are defaults in VBA that do not need to be defined, leading to less code. However, this was not functioning, so I went ahead and defined sh1 to be

Set Sh1 = Worksheets("Checklist")

Well, that does seem to be how your macro is written. Is it called by Sub Worksheet_Change?

I am not certain what this means. I assumed that my original code would allow for any change to force the worksheet to update.
@Scott Huish

I've never used CustomViews but why not use the more straightforward Select Case?

I am not familar with Select Case. Could you explain how the code you posted works?

 

[6StringJazzer]

there are defaults in VBA that do not need to be defined, leading to less code. However, this was not functioning

This is just a restatement of your earlier statement. Specifically which default did you want to use? What do you mean, specifically, by "not functioning"?

Also, less code is not always better.

I am not familar with Select Case. Could you explain how the code you posted works?

The syntax and use of Select Case is readily found in hundreds (thousands?) of places on the web.

Select Case <expression>

   Case <expression1>
      ' stuff do when the value of expression is expression1

   Case <expression2>
      ' stuff do when the value of expression is expression2
   ...

   Case Else
       ' stuff to do when no other cases fit

End Select

It's more complicated than this but this is the basic idea.

 

[Vanish29]

I will look into Select Case at a later time.

For the purpose of this question, here is the correct answer, provided by 6StringJazzer, with the necessary modifications for Explicit Code

Set Sh1 = Worksheets("Checklist")
If Sh1.Range("G4") = "All Products + Metered" Then
   ActiveWorkbook.CustomViews("All Products + Metered").Show
ElseIf Sh1.Range("G4") = "All Products Non-Metered" Then
   ActiveWorkbook.CustomViews("All Products Non-Metered").Show
ElseIf Sh1.Range("G4") = "Bill Pay + S2" Then
   ActiveWorkbook.CustomViews("Bill Pay + S2").Show
ElseIf Sh1.Range("G4") = "Bill Pay Only" Then
   ActiveWorkbook.CustomViews("Bill Pay Only").Show
ElseIf Sh1.Range("G4") = "Billing + Submetered" Then
   ActiveWorkbook.CustomViews("Billing + Submetered").Show
ElseIf Sh1.Range("G4") = "Billing Non Submetered" Then
   ActiveWorkbook.CustomViews("Billing Non Submetered").Show
ElseIf Sh1.Range("G4") = "Billing + Submetered & Bill Pay" Then
   ActiveWorkbook.CustomViews("Billing + Submetered").Show
ElseIf Sh1.Range("G4") = "Billing + Non Submetered & Bill Pay" Then
   ActiveWorkbook.CustomViews("Billing Non Submetered").Show
Else
   ActiveWorkbook.CustomViews("All Products + Metered").Show
End If

Thanks

 

[6StringJazzer]

You know, I tried to answer your question instead of actually reading the code. Can't you do this:

' Default
ActiveWorkbook.CustomViews("All Products + Metered").Show

' If G4 contains a valid view
On Error Resume Next
ActiveWorkbook.CustomViews(Sh1.Range("G4")).Show
On Error GoTo 0