If/Then with 'Or' not Returning Expected Results

[neobrainless]

I *think* this is the right section as it's a VBA issue, sorry if it's not!

So I'm tryign to get an excel file to open with all but certain worksheets hidden. I have found some code to make ONE sheet visible and all others hidden, and have tried to edit it to get the three I want to show by using 'or' in the If/Then statement, but it's still only showing one worksheet. Here.s the code I've got to:

Private Sub Workbook_Open()
Dim wsSheet As Worksheet
Application.ScreenUpdating = False
    For Each wsSheet In Worksheets
    If wsSheet.Name = "Allocations" Or wsSheet.Name = "Contact_Info" Or wsSheet.Name = "Mailout" Then
    Else
    wsSheet.Visible = False
    End If
    Next wsSheet
Application.ScreenUpdating = True
End Sub

Thanks for any help!

[Luk3r]

Edit**: During your iteration you should do away with your Else statement and use If Not.

Example:

If Not wsSheet.Name = "Allocations" Or wsSheet.Name = "Contact_Info" Or wsSheet.Name = "Mailout" Then
wsSheet.Visible = False
End If

[neobrainless]

I'm getting a compile error with that? Saying it's expecting 'Then' or Goto'...

[ADezii]

You do not event need the Iff...Else...End If Construct. Just a slight adjustment will do the trick for you:

Private Sub Workbook_Open()
Dim wsSheet As Worksheet
 
Application.ScreenUpdating = False
 
For Each wsSheet In Worksheets
  With wsSheet
    .Visible = (wsSheet.Name = "Allocations" Or wsSheet.Name = "Contact_Info" _
                Or wsSheet.Name = "Mailout")
  End With
Next wsSheet
 
Application.ScreenUpdating = True
End Sub

[NeoPa]

NeoBrainless:
"I'm getting a compile error with that? Saying it's expecting 'Then' or Goto'..."
That's because Luk3r omitted the [ CODE ] tags when posting and you copied the code as it appeared - on separate lines, whereas it is, in fact, all on one (except the If ... & End If lines).

This only becomes clear when the [ CODE ] tags are used of course ;-)

[NeoPa]

As for the question, there have already been some pointers on how your code might benefit from certain changes, yet I don't see why it wouldn't work in your original format. Certainly ADezii's suggestion is cleaner and to be recommended, but that's not to say yours shouldn't have worked.

In Access there's a nice Eval() function that gives a back-door to using SQL's In() construct, where you can specify a list of values, but it seems Excel doesn't support that one :-( (At least if it does the new versions obscure it well with their quite attrocious new web-based help systems - urghh).

[neobrainless]

@NeoPa - actually Luk3r's code is now different, the original used AndAlso which after it didn't work I did some research and realised (as he clearly did!) that AndAlso isn't a function in VBA...

I have made a workaround, but it's not pretty - I basically just said:
'Hide everything' then 'unhide these specific sheets'.

I'll have a play now with these other ideas and see which works best for me - I'd much rather a nice clean version!

Thanks for the tips everyone!

[neobrainless]

Ok, so Luk3r's new code partially works, but ONLY the 'Allocations' sheet is showing up, not any of the 'Or' sheets...

The code from ADezii does the trick very nicely, however. Think I'll go with that as it's MUCH neater than my current code! Thanks!

[NeoPa]

I'm afraid Luk3r does seem to have a habit of posting "guessware". Unfortunate really. Their level of knowledge seems fairly basic ATM it seems.