Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Been scratching my head for a few hours and am ready to call in the "A" team...
My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
did you protect the workbook through the tools menu? If so, this could be
the problem. -- Regards, Tom Ogilvy "Steve E" wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
You are probably trying to hide them all before making another visible, one
must be visible at all times Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steve E" wrote in message ... Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Not sure if this helps any but I can significantly shorten the code for
you. Private Sub Workbook_Open() ' Make all sheets very hidden except Entrance sheet Dim ws As Worksheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = -2 'VeryHidden, 0 For Hidden Else ws.Visible = -1 'Visible End If Next Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Charles Chickering Steve E wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Whoops - missed your command at the beginning and end.
if you don't leave any sheets visible but the Entrance sheet, then I would move this command to be the first action you take before you hide any sheets: ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible a workbook has to always have at least one sheet visible. If that doesn't help, then what has changed since it last worked? -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: did you protect the workbook through the tools menu? If so, this could be the problem. -- Regards, Tom Ogilvy "Steve E" wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Hi Tom,
First, thanks for the quick reply. I had originally protected the workbook and worksheets via the TOOLS menu but have since not re-protected using that menu. I'm hoping to eventually figure out how to do that just via the workbook/worksheet code (although that is still out of my reach right now) Any other ideas? TIA, Steve "Tom Ogilvy" wrote: did you protect the workbook through the tools menu? If so, this could be the problem. -- Regards, Tom Ogilvy "Steve E" wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
with the caution that Entrance can not be the last sheet.
-- Regards, Tom Ogilvy "Die_Another_Day" wrote: Not sure if this helps any but I can significantly shorten the code for you. Private Sub Workbook_Open() ' Make all sheets very hidden except Entrance sheet Dim ws As Worksheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = -2 'VeryHidden, 0 For Hidden Else ws.Visible = -1 'Visible End If Next Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Charles Chickering Steve E wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
well, you are already doing the workbook level protection and unprotect in
your code, so it shouldn't take a lot of spinup. But see my followup post -- Regards, Tom Ogilvy "Steve E" wrote: Hi Tom, First, thanks for the quick reply. I had originally protected the workbook and worksheets via the TOOLS menu but have since not re-protected using that menu. I'm hoping to eventually figure out how to do that just via the workbook/worksheet code (although that is still out of my reach right now) Any other ideas? TIA, Steve "Tom Ogilvy" wrote: did you protect the workbook through the tools menu? If so, this could be the problem. -- Regards, Tom Ogilvy "Steve E" wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Tom (& Charles):
Thanks so much for your help! It was indeed the sequence that I was hiding the sheets in. I'd added the entrance sheet at the end and while I was testing had another sheet (quote form) still visible... Next question: I (naturally) like the much shortened code that Charles wrote but I don't know how to make sure that the "Entrance" sheet is visible first (its actually sheet(9))... And my last question of the day will be: where can I go to learn how to protect the formatting, formulaes, etc in the cells of individual worksheets via the VBA code instead of using the TOOLS menu? TIA, Steve "Tom Ogilvy" wrote: with the caution that Entrance can not be the last sheet. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Not sure if this helps any but I can significantly shorten the code for you. Private Sub Workbook_Open() ' Make all sheets very hidden except Entrance sheet Dim ws As Worksheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = -2 'VeryHidden, 0 For Hidden Else ws.Visible = -1 'Visible End If Next Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Charles Chickering Steve E wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Private Sub Workbook_Open()
' Make all sheets very hidden except Entrance sheet Dim ws As Worksheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Worksheets("Entrance").Visible = -1 For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = -2 'VeryHidden, 0 For Hidden End If Next Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub That should fix the Visible Problem. Charles Steve E wrote: Tom (& Charles): Thanks so much for your help! It was indeed the sequence that I was hiding the sheets in. I'd added the entrance sheet at the end and while I was testing had another sheet (quote form) still visible... Next question: I (naturally) like the much shortened code that Charles wrote but I don't know how to make sure that the "Entrance" sheet is visible first (its actually sheet(9))... And my last question of the day will be: where can I go to learn how to protect the formatting, formulaes, etc in the cells of individual worksheets via the VBA code instead of using the TOOLS menu? TIA, Steve "Tom Ogilvy" wrote: with the caution that Entrance can not be the last sheet. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Not sure if this helps any but I can significantly shorten the code for you. Private Sub Workbook_Open() ' Make all sheets very hidden except Entrance sheet Dim ws As Worksheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = -2 'VeryHidden, 0 For Hidden Else ws.Visible = -1 'Visible End If Next Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Charles Chickering Steve E wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Just do that Entrance sheet first--outside the loop.
Private Sub Workbook_Open() ' Make all sheets very hidden except Entrance sheet Dim ws As Object 'Worksheet Const PWORD_Workbook As String = "hi" Application.Screenupdating = False ThisWorkbook.Unprotect PWORD_Workbook worksheets("Entrance").visible = xlsheetvisible For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = xlsheetveryhidden 'xlsheethidden End If Next ws Worksheets("Entrance").Activate ThisWorkbook.Protect PWORD_Workbook Application.Screenupdating = True End Sub I changed the numbers to VBA's constants--I think it makes it easier to read the code later. And I changed the .enableevents to .screenupdating. I'm guessing that Charles wanted to hide the flickering of any sheets being hidden. And I change the "dim ws as worksheet" to "dim ws as object". Just to match the loop. Charles looped through Sheets--not just the worksheets. And you can look at VBA's help for info. You may want to record a macro when you experiment and then look at the recorded code. Steve E wrote: Tom (& Charles): Thanks so much for your help! It was indeed the sequence that I was hiding the sheets in. I'd added the entrance sheet at the end and while I was testing had another sheet (quote form) still visible... Next question: I (naturally) like the much shortened code that Charles wrote but I don't know how to make sure that the "Entrance" sheet is visible first (its actually sheet(9))... And my last question of the day will be: where can I go to learn how to protect the formatting, formulaes, etc in the cells of individual worksheets via the VBA code instead of using the TOOLS menu? TIA, Steve "Tom Ogilvy" wrote: with the caution that Entrance can not be the last sheet. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Not sure if this helps any but I can significantly shorten the code for you. Private Sub Workbook_Open() ' Make all sheets very hidden except Entrance sheet Dim ws As Worksheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = -2 'VeryHidden, 0 For Hidden Else ws.Visible = -1 'Visible End If Next Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Charles Chickering Steve E wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlSheetVeryHidden vs xlSheetVisible
Steve,
A good way to get the syntax is to turn on the macro recorder and do it manually. This produces ugly and inefficient code, but it does show you the properties and methods you need to use. You can then clean up the code and lookup the properties and methods in VBA help. -- Regards, Tom Ogilvy "Steve E" wrote: Tom (& Charles): Thanks so much for your help! It was indeed the sequence that I was hiding the sheets in. I'd added the entrance sheet at the end and while I was testing had another sheet (quote form) still visible... Next question: I (naturally) like the much shortened code that Charles wrote but I don't know how to make sure that the "Entrance" sheet is visible first (its actually sheet(9))... And my last question of the day will be: where can I go to learn how to protect the formatting, formulaes, etc in the cells of individual worksheets via the VBA code instead of using the TOOLS menu? TIA, Steve "Tom Ogilvy" wrote: with the caution that Entrance can not be the last sheet. -- Regards, Tom Ogilvy "Die_Another_Day" wrote: Not sure if this helps any but I can significantly shorten the code for you. Private Sub Workbook_Open() ' Make all sheets very hidden except Entrance sheet Dim ws As Worksheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) For Each ws in ThisWorkbook.Sheets If ws.Name < "Entrance" Then ws.Visible = -2 'VeryHidden, 0 For Hidden Else ws.Visible = -1 'Visible End If Next Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Charles Chickering Steve E wrote: Been scratching my head for a few hours and am ready to call in the "A" team... My project was opening fine and having no problems. I changed a macro assignment on a button and now am getting an error when I open the project: "Unable to set the Visible property of the Worksheet class" My XL2003 project consists of a workbook with 9 worksheets. I have (had?) the project set up so that when the workbook opens there is an "entrance" sheet that explains to my user how to use the program. All of the other sheets are hidden. My user clicks on a button and this opens up the quote form... But now my project is opening with the error and the sheet that is open is the quote form... When I go to the workbook module (where I am setting the visible sheets, etc in my Workbook_Open event and try and run this sub I get this error message: "Application defined or Object defined error" But I can't find what I've gotten goofed up... In a seperate module I have: Public Const PWORD_Workbook As String = "password" And in my ThisWorkbook module I have: Private Sub Workbook_Open() ' ' Make all sheets very hidden except Entrance sheet Application.EnableEvents = False ThisWorkbook.Unprotect (PWORD_Workbook) ThisWorkbook.Sheets("Fabric Colors").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Input Lists").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Quote Form").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Bill of Materials").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Margin Structure").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Shipping Costs").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("zips").Visible = xlSheetVeryHidden ThisWorkbook.Sheets("Dealer List").Visible = xlSheetVeryHidden ' Make Entrance Sheet visible and active ThisWorkbook.Sheets("Entrance").Visible = xlSheetVisible Worksheets("Entrance").Activate ThisWorkbook.Protect (PWORD_Workbook) Application.EnableEvents = True End Sub Can you see what I have goofed up and help me ungoof it? TIA, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
my xlSheetVeryHidden is hidden and I can't unhide it - HELP! | Excel Discussion (Misc queries) | |||
Can't get my xlSheetVeryHidden | Excel Worksheet Functions | |||
Trouble with copy of xlSheetVeryHidden | Excel Programming | |||
Opeations on xlSheetVeryHidden worksheet | Excel Programming | |||
xlSheetVeryHidden issue | Excel Programming |