Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
I asked a question earlier and received a VBE code which will probley work
but the problem is, is that i already have part of this code in the macro. I didn't create the program. I just want 7 of the 8 tabs to stay hidden when i reopen the workbook. Here is the code that i have as you can see i am using the beforeclose and the work_book open code so i don't know what else to do I have posted the entire code for your review. Private Sub Workbook_Open() 'check for solver If (CheckSolver = False) Then MsgBox "Solver Add-In is not installed, you will not be able to solve least costing on the Auto-Balance sheet. Consult Excel help for information on installing the Solver Add-In." End If 'load the expiry date from cell Expiration!B1 Expiry = getExpirationDate rightNow = Date 'if we have expired, or moved to a different machine If (Expiry = Empty Or rightNow Expiry Or Not IsDataValid(getRegistrationNumber, getExpirationDate)) Then showExpiryDialog End If 'load the workbook loadworkbook End Sub Private Sub loadworkbook() Application.ScreenUpdating = False changeVisibility InstructionsSheet, xlSheetVisible InstructionsSheet.Activate changeVisibility RequirementsSheet, xlSheetVisible changeVisibility EvaluateSheet, xlSheetVisible changeVisibility AutoBalanceSheet, xlSheetVisible changeVisibility HerdDescriptionSheet, xlSheetVisible changeVisibility IngredientsSheet, xlSheetVisible changeVisibility MixSheet, xlSheetVisible changeVisibility ReportsEVSheet, xlSheetVisible changeVisibility ReportsLCSheet, xlSheetVisible changeVisibility ErrorSheet, xlSheetVeryHidden Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False resetToStart Geminator.Save Application.ScreenUpdating = True End Sub Private Sub changeVisibility(Sheet As Worksheet, v As Integer) unProtect "AmIn0" Sheet.unProtect "AmIn0" Sheet.Visible = v Sheet.Protect "AmIn0" Protect "AmIn0" End Sub Private Sub resetToStart() changeVisibility ErrorSheet, xlSheetVisible changeVisibility AutoBalanceSheet, xlSheetVeryHidden changeVisibility EvaluateSheet, xlSheetVeryHidden changeVisibility HerdDescriptionSheet, xlSheetVeryHidden changeVisibility IngredientsSheet, xlSheetVeryHidden changeVisibility MixSheet, xlSheetVeryHidden changeVisibility InstructionsSheet, xlSheetVeryHidden changeVisibility ReportsEVSheet, xlSheetVeryHidden changeVisibility ReportsLCSheet, xlSheetVeryHidden changeVisibility RequirementsSheet, xlSheetVeryHidden setUseCount getUseCount + 1 If (getFirstUsed = Empty) Then setFirstUsed Date End If Geminator.Protect "AmIn0" End Sub Private Sub showExpiryDialog() registrationForm.regTextBox.SetFocus registrationForm.regTextBox.SelStart = 0 registrationForm.regTextBox.SelLength = Len(registrationForm.regTextBox.Text) registrationForm.Show End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.name < "Instructions" Then ws.Visible = xlSheetVeryHidden End If Next ws End Sub -- Thank you, Brenda |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
Hi,
Isn't the code working? If not what is failing to do what you want? By the way it looks to me as though something is missing, maybe code associated with a form, or other module. For example, where is the variable v coming in? -- Thanks, Shane Devenshire "Brenda" wrote: I asked a question earlier and received a VBE code which will probley work but the problem is, is that i already have part of this code in the macro. I didn't create the program. I just want 7 of the 8 tabs to stay hidden when i reopen the workbook. Here is the code that i have as you can see i am using the beforeclose and the work_book open code so i don't know what else to do I have posted the entire code for your review. Private Sub Workbook_Open() 'check for solver If (CheckSolver = False) Then MsgBox "Solver Add-In is not installed, you will not be able to solve least costing on the Auto-Balance sheet. Consult Excel help for information on installing the Solver Add-In." End If 'load the expiry date from cell Expiration!B1 Expiry = getExpirationDate rightNow = Date 'if we have expired, or moved to a different machine If (Expiry = Empty Or rightNow Expiry Or Not IsDataValid(getRegistrationNumber, getExpirationDate)) Then showExpiryDialog End If 'load the workbook loadworkbook End Sub Private Sub loadworkbook() Application.ScreenUpdating = False changeVisibility InstructionsSheet, xlSheetVisible InstructionsSheet.Activate changeVisibility RequirementsSheet, xlSheetVisible changeVisibility EvaluateSheet, xlSheetVisible changeVisibility AutoBalanceSheet, xlSheetVisible changeVisibility HerdDescriptionSheet, xlSheetVisible changeVisibility IngredientsSheet, xlSheetVisible changeVisibility MixSheet, xlSheetVisible changeVisibility ReportsEVSheet, xlSheetVisible changeVisibility ReportsLCSheet, xlSheetVisible changeVisibility ErrorSheet, xlSheetVeryHidden Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False resetToStart Geminator.Save Application.ScreenUpdating = True End Sub Private Sub changeVisibility(Sheet As Worksheet, v As Integer) unProtect "AmIn0" Sheet.unProtect "AmIn0" Sheet.Visible = v Sheet.Protect "AmIn0" Protect "AmIn0" End Sub Private Sub resetToStart() changeVisibility ErrorSheet, xlSheetVisible changeVisibility AutoBalanceSheet, xlSheetVeryHidden changeVisibility EvaluateSheet, xlSheetVeryHidden changeVisibility HerdDescriptionSheet, xlSheetVeryHidden changeVisibility IngredientsSheet, xlSheetVeryHidden changeVisibility MixSheet, xlSheetVeryHidden changeVisibility InstructionsSheet, xlSheetVeryHidden changeVisibility ReportsEVSheet, xlSheetVeryHidden changeVisibility ReportsLCSheet, xlSheetVeryHidden changeVisibility RequirementsSheet, xlSheetVeryHidden setUseCount getUseCount + 1 If (getFirstUsed = Empty) Then setFirstUsed Date End If Geminator.Protect "AmIn0" End Sub Private Sub showExpiryDialog() registrationForm.regTextBox.SetFocus registrationForm.regTextBox.SelStart = 0 registrationForm.regTextBox.SelLength = Len(registrationForm.regTextBox.Text) registrationForm.Show End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.name < "Instructions" Then ws.Visible = xlSheetVeryHidden End If Next ws End Sub -- Thank you, Brenda |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
This is possibly what you want, but without seeing a workbook its difficult: Private Sub Workbook_Open() 'check for solver If (CheckSolver = False) Then MsgBox "Solver Add-In is not installed, you will not be able to" & vbNewLine & _ "solve least costing on the Auto-Balance sheet." & vbNewLine & _ "Consult Excel help forinformation on installing the Solver Add-In." End If 'load the expiry date from cell Expiration!B1 Expiry = getExpirationDate rightNow = Date 'if we have expired, or moved to a different machine If (Expiry = Empty Or rightNow Expiry Or Not _ IsDataValid(getRegistrationNumber, getExpirationDate)) Then showExpiryDialog End If Call Sheets_Hide End Sub Private Sub resetToStart() setUseCount getUseCount + 1 If (getFirstUsed = Empty) Then setFirstUsed Date End If Geminator.Protect "AmIn0" End Sub Private Sub showExpiryDialog() registrationForm.regTextBox.SetFocus registrationForm.regTextBox.SelStart = 0 registrationForm.regTextBox.SelLength = _ Len(registrationForm.regTextBox.Text) registrationForm.Show End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Sheets_Hide resetToStart Geminator.Save End Sub Private Sub Sheets_Hide() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Sheets If ws.Name < "Instructions" Then ws.Visible = xlSheetVeryHidden End If Next ws Application.ScreenUpdating = True End Sub -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27108 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
I JUST WANT TO SAY THANK YOU. There is no way i would have figured this out
and with you putting in the wording for me is greatly appreciated. From the way everything looks it is working fine. This is a very complicated program that was developed. Again, thank you. -- Thank you, Brenda "Simon Lloyd" wrote: This is possibly what you want, but without seeing a workbook its difficult: Private Sub Workbook_Open() 'check for solver If (CheckSolver = False) Then MsgBox "Solver Add-In is not installed, you will not be able to" & vbNewLine & _ "solve least costing on the Auto-Balance sheet." & vbNewLine & _ "Consult Excel help forinformation on installing the Solver Add-In." End If 'load the expiry date from cell Expiration!B1 Expiry = getExpirationDate rightNow = Date 'if we have expired, or moved to a different machine If (Expiry = Empty Or rightNow Expiry Or Not _ IsDataValid(getRegistrationNumber, getExpirationDate)) Then showExpiryDialog End If Call Sheets_Hide End Sub Private Sub resetToStart() setUseCount getUseCount + 1 If (getFirstUsed = Empty) Then setFirstUsed Date End If Geminator.Protect "AmIn0" End Sub Private Sub showExpiryDialog() registrationForm.regTextBox.SetFocus registrationForm.regTextBox.SelStart = 0 registrationForm.regTextBox.SelLength = _ Len(registrationForm.regTextBox.Text) registrationForm.Show End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Sheets_Hide resetToStart Geminator.Save End Sub Private Sub Sheets_Hide() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Sheets If ws.Name < "Instructions" Then ws.Visible = xlSheetVeryHidden End If Next ws Application.ScreenUpdating = True End Sub -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27108 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
Loyd, I do have another question for you. When i open the workbook it works
perfectly but when i close out i get the following error message: Compile error in hidden module: Geminator. When i went to help it said something about password? Any suggestions? -- Thank you, Brenda "Simon Lloyd" wrote: This is possibly what you want, but without seeing a workbook its difficult: Private Sub Workbook_Open() 'check for solver If (CheckSolver = False) Then MsgBox "Solver Add-In is not installed, you will not be able to" & vbNewLine & _ "solve least costing on the Auto-Balance sheet." & vbNewLine & _ "Consult Excel help forinformation on installing the Solver Add-In." End If 'load the expiry date from cell Expiration!B1 Expiry = getExpirationDate rightNow = Date 'if we have expired, or moved to a different machine If (Expiry = Empty Or rightNow Expiry Or Not _ IsDataValid(getRegistrationNumber, getExpirationDate)) Then showExpiryDialog End If Call Sheets_Hide End Sub Private Sub resetToStart() setUseCount getUseCount + 1 If (getFirstUsed = Empty) Then setFirstUsed Date End If Geminator.Protect "AmIn0" End Sub Private Sub showExpiryDialog() registrationForm.regTextBox.SetFocus registrationForm.regTextBox.SelStart = 0 registrationForm.regTextBox.SelLength = _ Len(registrationForm.regTextBox.Text) registrationForm.Show End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Sheets_Hide resetToStart Geminator.Save End Sub Private Sub Sheets_Hide() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Sheets If ws.Name < "Instructions" Then ws.Visible = xlSheetVeryHidden End If Next ws Application.ScreenUpdating = True End Sub -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27108 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
Loyd, After running the program with the code you gave me i am now getting
another error message that states run time error 1004 (unable to set the visible property of the worksheet class. Here is the thing i have buttons on the other pages so that if someone were to click on this button it will bring up the tab i have hidden and when they click on another button it will hide the tab that they had clicked on before and bring up the new tab. I think this is the reason why I am getting the errors messages, I think we may be back to square one :-( Here is the code for the buttons that worked great... Sub UnHideHerdDescription() Sheets("HerdDescription").Visible = True Sheets("HerdDescription").Select End Sub Sub UnHideRequirements() Sheets("Requirements").Visible = True Sheets("Requirements").Select End Sub Sub UnHideIngredients() Sheets("Ingredients").Visible = True Sheets("Ingredients").Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As range) End Sub Sub UnHideMakeAMix() Sheets("MakeAMix").Visible = True Sheets("MakeAMix").Select End Sub Sub UnHideAutoBalance() Sheets("AutoBalance").Visible = True Sheets("AutoBalance").Select End Sub Sub UnHideEvaluate() Sheets("Evaluate").Visible = True Sheets("Evaluate").Select End Sub Sub UnHideReportAutoBalance() Sheets("ReportAutoBalance").Visible = True Sheets("ReportAutoBalance").Select End Sub Sub UnHideReportEvaluate() Sheets("ReportsEvaluate").Visible = True Sheets("ReportsEvaluate").Select End Sub The thing is when i first bring up the workbook it is hidding the tabs when you open it but now i can't click on the buttons to take them to other tabs that I hide. Can you help me out on this? h -- Thank you, Brenda "Simon Lloyd" wrote: This is possibly what you want, but without seeing a workbook its difficult: Private Sub Workbook_Open() 'check for solver If (CheckSolver = False) Then MsgBox "Solver Add-In is not installed, you will not be able to" & vbNewLine & _ "solve least costing on the Auto-Balance sheet." & vbNewLine & _ "Consult Excel help forinformation on installing the Solver Add-In." End If 'load the expiry date from cell Expiration!B1 Expiry = getExpirationDate rightNow = Date 'if we have expired, or moved to a different machine If (Expiry = Empty Or rightNow Expiry Or Not _ IsDataValid(getRegistrationNumber, getExpirationDate)) Then showExpiryDialog End If Call Sheets_Hide End Sub Private Sub resetToStart() setUseCount getUseCount + 1 If (getFirstUsed = Empty) Then setFirstUsed Date End If Geminator.Protect "AmIn0" End Sub Private Sub showExpiryDialog() registrationForm.regTextBox.SetFocus registrationForm.regTextBox.SelStart = 0 registrationForm.regTextBox.SelLength = _ Len(registrationForm.regTextBox.Text) registrationForm.Show End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call Sheets_Hide resetToStart Geminator.Save End Sub Private Sub Sheets_Hide() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Sheets If ws.Name < "Instructions" Then ws.Visible = xlSheetVeryHidden End If Next ws Application.ScreenUpdating = True End Sub -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27108 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
Brenda, why not join our forums where you can make a post and upload a workbook that way we can see your structure and all of the code as just giving sections leads us to give you incorrect answers, make sure you replace any sensitive or personal data with dummy data. -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27108 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
Cool tell me where to go and consider it done!!!
-- Thank you, Brenda "Simon Lloyd" wrote: Brenda, why not join our forums where you can make a post and upload a workbook that way we can see your structure and all of the code as just giving sections leads us to give you incorrect answers, make sure you replace any sensitive or personal data with dummy data. -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27108 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
suggestions for another code
Found it never mind i will do this.
-- Thank you, Brenda "Simon Lloyd" wrote: Brenda, why not join our forums where you can make a post and upload a workbook that way we can see your structure and all of the code as just giving sections leads us to give you incorrect answers, make sure you replace any sensitive or personal data with dummy data. -- Simon Lloyd Regards, Simon Lloyd 'www.thecodecage.com' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=27108 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Suggestions please! | Excel Worksheet Functions | |||
Suggestions??? | Excel Discussion (Misc queries) | |||
any suggestions? | Excel Discussion (Misc queries) | |||
Suggestions | Excel Discussion (Misc queries) | |||
Suggestions Please | Excel Discussion (Misc queries) |