Hide/Unhide

HimThruMe

Board Regular
Joined
Jun 26, 2008
Messages
117
I have a macro that when i click on it - it unhides some sheets to modify them and then hides them again when it is done. I want to be able to make it so it will recognize if the sheets are already hidden or not.

I need a macro that IF the sheet is hidden... continue on and unhide it... then hide it after all alterations are made... but IF the sheet isnt hidden... just ignore the rest and make the alterations...

How would i write this out?

i appreciate any suggestions and help - thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

lionpants

Board Regular
Joined
Nov 11, 2009
Messages
117
Code:
Dim ws as Worksheet
If ws.Visible = xlSheetHidden Then ...

Should do the trick.<code></code>
 

HimThruMe

Board Regular
Joined
Jun 26, 2008
Messages
117
the problem is that it needs to recognize if the sheet was hidden to begin with at the end of the macro.

The macro runs like this:

unhide all sheets
make alterations
hide all sheets that were hidden

the problem is that if they werent hidden to begin with when the macro was run i dont want them to be hidden at the end... is there a way to do this?
 

lionpants

Board Regular
Joined
Nov 11, 2009
Messages
117
Code:
Sub AlterHidden()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetHidden Then
            ws.Visible = xlSheetVisible
            ' make alterations here
            ws.Visible = xlSheetHidden
        Else
            ' make alterations
        End If
    Next
End Sub
I'm not sure why you need to unhide the sheet first though... alterations can be made on a hidden sheet through a script.
 

HimThruMe

Board Regular
Joined
Jun 26, 2008
Messages
117

ADVERTISEMENT

how do i make alterations on a hidden sheet? - everytime i run my macro it stops if the sheet is hidden.
 

lionpants

Board Regular
Joined
Nov 11, 2009
Messages
117
Yup, you need to reference the sheet like this...

Code:
Sheets("Sheet1").

Rather than what your macro is most likely doing, which is selecting it first.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,440
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top