suggestions for another code

B

Brenda

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
 
S

ShaneDevenshire

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?
 
S

Simon Lloyd

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)
 
B

Brenda

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.
 
B

Brenda

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?
 
B

Brenda

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
 
S

Simon Lloyd

Brenda, why not join our forums where you can make a post and upload
workbook that way we can see your structure and all of the code as jus
giving sections leads us to give you incorrect answers, make sure yo
replace any sensitive or personal data with dummy data

--
Simon Lloy

Regards
Simon Lloy
'www.thecodecage.com' (http://www.thecodecage.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error message 4

Top