Hiding rows

[Guest]

I need to hide several rows on sheet - ie if sheet1 A1 = YES hide rows 45-50
on sheet2 (I can mange that!), but...
Every time I make a copy of sheet2 I want the same functionality to apply
-always hiding/unhiding the same rows without having to amend any coding.
Is it possible?

Thanks in advance
Saintsman

 

[Roger Govier]

Hi

If you make the reference to ActiveSheet rather than Sheets("Sheet1") in
your code, then it should work OK.

 

[Guest]

Sorry Roger - not understanding what you mean here I'm afraid
The workbook will have several sheets where I do not want to hide any rows,
how do I differentate btween those that do & don't when I haven't created the
sheets yet

 

[Guest]

Saintsman-

So your worksheet has the condition of A1='Yes' then hide some rows. When
you say so will have sheet with the rows hidden and some won't, would you be
running this macro just to set up the worksheet?

 

[Guest]

Not just to set up worksheet
I will end up with perhaps 10 sheets where I want the option to Hide several
rows (always the same rows), but the remaining sheets should not be affected
My problem is that I can't create the sheets on day 1, new sheets will be
added over a period of time & I do not want to keep revisiting the workbook

 

[Roger Govier]

Hi

Since you say that hiding rows will be conditional upon a value in a certain
cell (Sheet1!A1), could you not AND that with the presence of a value in a
given cell on the individual sheets.
That way, would could easily alter which sheets have the rows hidden or not.

 

[Guest]

Saintsman-

Try this by placing this code below in the "Thisworkbook" code section.
when inserting a new sheet you will be asked if you want to hide rows 45-50.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call SaintsMan
End Sub


Sub SaintsMan()
Dim Msg, Style, Title, Response
Msg = "Do you want to hide rows ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Hide Some"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
ActiveSheet.Rows("45:50").Select
Selection.EntireRow.Hidden = True
Else
Exit Sub
End If

End Sub

 

[Guest]

Thanks - I was sort of getting there, but this is much neater
I will end up with at least 20 sheets where I want this function to work -
is there a way I can make each sheet Active without actually opening it?

Saintsman

 

[Guest]

Saintsman,

I have coded this to allow you to add multiple sheets at a time while hiding
rows as last time. I am quessing that is what you mean with your last
question.
This code will ask for the number of sheets to add with the hidden rows and
then loop through the number requested. I hope this is what you were asking
for.

JR Form

'Declare a global boolean variable and call it bolProcessing
Public bolProcessing As Boolean
'

Private Sub Workbook_NewSheet(ByVal Sh As Object)
'When adding multiple sheets this will stop the refiring of the Saintsman
code below.
If bolProcessing Then Exit Sub
Call SaintsMan
End Sub


Sub SaintsMan()

Dim Msg, Style, Title, Response, Default, Sheets2Add
Msg = "Do you want to hide rows ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Hide Some"

'If you will be adding more than one often change the default to the number
Default = "1"

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then
'Set the new variable to true
bolProcessing = True
Message = "How many sheets do you want to add with hidden rows?"
Sheets2Add = InputBox(Message, Title, Default)

'hide the rows in the current activesheet
ActiveSheet.Rows("45:50").Select
Selection.EntireRow.Hidden = True
Sheets2Add = Sheets2Add - 1

'set up a loop to add sheets
Do Until Sheets2Add = 0
Sheets.Add
ActiveSheet.Rows("45:50").Select
Selection.EntireRow.Hidden = True
Sheets2Add = Sheets2Add - 1
Loop

'reset the global variable so the process can be repeated
bolProcessing = False
Else
Exit Sub
End If

End Sub