• 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.​
 
Top