• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA - Skip macro action if sheet hidden

Roxhan

New Member
Hello,
Long time Excel admirer, and slowly learning/fumbling my way through macros, I'm hoping someone can help me sort this one out.

Scenario:
Teams will send me excels, with multiple sheets, and depending on the project, some sheets will be hidden as they're N/A, but I need to perform the following steps on the remaining sheets...

Step 1 - Hide specific sheets based on their name.
I managed to get this to work, using the following (dummy sheet names)...

>>> use code - tags <<< instead any indents
Code:
Dim wsSheet As Worksheet
    For Each wsSheet In Worksheets
        If wsSheet.Name = "X" Or wsSheet.Name = "Y" Or wsSheet.Name = "Z" Then
            wsSheet.Visible = xlSheetHidden
        End If
    Next wsSheet
End Sub
Step 2 - Hide specific columns based on sheet name.
I have got the below code to work (dummy sheet names), however, if Sheet 'B' was already hidden, the code breaks.
I am trying to get excel to perform simple pre-determined tasks per sheet name, but to skip those tasks if the sheet is hidden (because that sheet was N/A for this project)... hoping someone can tweak this code accordingly? (I tried playing around with 'If sheet A is visible, Then perform action... but my VBA language isn't up to scratch ha)

Code:
Sheets("A").Select
Range("C:C,E:E,G:G,H:BK").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 70
Sheets("B").Select
Range("C:C,E:E,H:H,J:J").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 70
Sheets C, D.... I'll follow the same steps as above with slightly different instructions on which columns to hide
End Sub
Combine into 1 Macro button
Not sure if I should post separately, but I'm hoping once I have the code correct for step 2, I can join that with Step 1, so I just have to hit one button and all actions happen together?

Many thanks for any help you can provide
Rox
 
Last edited by a moderator:

Marc L

Excel Ninja
Hello, as a good enough VBA procedure does not need to Select anything so for starters :​
Code:
         Dim Ws As Worksheet
    For Each Ws In Worksheets
        With Ws
            If .Visible = -1 Then
                Select Case .Name
                       Case "A"
                            .Columns(1).ColumnWidth = 70
                            .Range("C:C,E:E,G:BK").EntireColumn.Hidden = True
                       Case "B"
                            .Columns(1).ColumnWidth = 70
                            .Range("C:C,E:E,H:H,J:J").EntireColumn.Hidden = True
                       Case "X", "Y", "Z"
                            .Visible = 0
                End Select
            End If
        End With
    Next
Do you like it ? So thanks to click on bottom right Like !​
 

Roxhan

New Member
Hi @Marc L

Thank you so much for your help, that seems to have worked a treat! :)

I replaced the Case "A" with my actual sheet name, and then created additional Cases for all the other sheets I needed to have instructions for.
I wasn't sure if that's what 'Case X, Y, Z' line was for? so I deleted that line...
but then when I ran the macro it hid the last sheet that had instructions.
So I also deleted the '.Visible = 0' line of code, and that seems to have fixed the problem?

Please let me know if I've made a mistake and Visible=0 still needs to be in there, and I need to adjust further? :oops:

Thank you again, much appreciated
Rox
 

Marc L

Excel Ninja
The Case X, Y, Z block is your Step 1 as you asked to « Combine into 1 Macro button » …​
A must read : the VBA help for Select Case.​
 

Roxhan

New Member
Hello @Marc L

I'd like to make a small tweak to the above code you shared, as the team may add more random sheets which I need hidden, but I won't know sheet names ahead of time. So...
Instead of specifying Case "X,Y,Z" to be hidden...
Can it be coded to Hide every Case that isn't "A,B,C..."? (reverse it, if that makes sense?)

Any thoughts would be greatly appreciated
Thanks again
Rox
 

Marc L

Excel Ninja
According to VBA help - a must read ! - replace the codeline Case "X", "Y", "Z" with Case Else …​
 
Top