Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
Hi
If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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 "Roger Govier" wrote: Hi If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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? "Saintsman" wrote: 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 "Roger Govier" wrote: Hi If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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 "JRForm" wrote: 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? "Saintsman" wrote: 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 "Roger Govier" wrote: Hi If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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. -- Regards Roger Govier "Saintsman" wrote in message ... 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 "JRForm" wrote: 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? "Saintsman" wrote: 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 "Roger Govier" wrote: Hi If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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 "Saintsman" wrote: 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 "JRForm" wrote: 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? "Saintsman" wrote: 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 "Roger Govier" wrote: Hi If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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 "JRForm" wrote: 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 "Saintsman" wrote: 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 "JRForm" wrote: 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? "Saintsman" wrote: 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 "Roger Govier" wrote: Hi If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows
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 "Saintsman" wrote: 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 "JRForm" wrote: 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 "Saintsman" wrote: 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 "JRForm" wrote: 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? "Saintsman" wrote: 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 "Roger Govier" wrote: Hi If you make the reference to ActiveSheet rather than Sheets("Sheet1") in your code, then it should work OK. -- Regards Roger Govier "Saintsman" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color alternate rows when after hiding selected rows | Excel Worksheet Functions | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
hiding Rows and buttons/comboxes, over the rows | Excel Programming | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) |