Unhide Worksheet with Login UserForm
 

I have a UserForm that is shown when I open a workbook. The UserForm has a
UserName ComboBox, Password TextBox, Login CommandButton, and a Cancel
CommandButton. I only want .Sheets("QUOTE") to Unhide when this particular
user clicks the Login button, how do I do that? This is what I got so far,
but I am getting an Error.

Note: I don't know if this helps but I use this code to Hide all the
worksheets, when I close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Windows("CALCULATOR 11-5-2007.xls").Visible = False

End Sub


Private Sub cmbLogin_Click()

Select Case
Case "Peter Maida"
If tbxPassword = "peterm2" Then
Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible =
xlSheetHidden '<===ERROR ERROR
Sheets("QUOTE").Visible = xlSheetVisible
With Sheets("QUOTE")
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With
Unload Me
Exit Sub
Else
MsgBox "Try agian.", vbCritical
With tbxPassword
.Value = ""
.SetFocus
End With
End If
End Select

End Sub

Unhide Worksheet with Login UserForm
 

"RyanH" skrev i en meddelelse
...
I have a UserForm that is shown when I open a workbook. The UserForm has a
UserName ComboBox, Password TextBox, Login CommandButton, and a Cancel
CommandButton. I only want .Sheets("QUOTE") to Unhide when this
particular
user clicks the Login button, how do I do that? This is what I got so
far,
but I am getting an Error.

Note: I don't know if this helps but I use this code to Hide all the
worksheets, when I close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Windows("CALCULATOR 11-5-2007.xls").Visible = False

End Sub


Private Sub cmbLogin_Click()

Select Case
Case "Peter Maida"
If tbxPassword = "peterm2" Then
Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible =
xlSheetHidden '<===ERROR ERROR
Sheets("QUOTE").Visible = xlSheetVisible
With Sheets("QUOTE")
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With
Unload Me
Exit Sub
Else
MsgBox "Try agian.", vbCritical
With tbxPassword
.Value = ""
.SetFocus
End With
End If
End Select

End Sub

Hi

Have a look at this:

Workbooks("CALCULATOR 11-5-2007.xls").Worksheets("QUOTE").Visible = True

//Per

Unhide Worksheet with Login UserForm
 

Ryan

I think it should just be

Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible = True

Good luck.

Ken
Norfolk, Va


On Jan 4, 11:11*am, RyanH wrote:
I have a UserForm that is shown when I open a workbook. *The UserForm has a
UserName ComboBox, Password TextBox, Login CommandButton, and a Cancel
CommandButton. *I only want .Sheets("QUOTE") to Unhide when this particular
user clicks the Login button, how do I do that? *This is what I got so far,
but I am getting an Error.

Note: *I don't know if this helps but I use this code to Hide all the
worksheets, when I close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

* * Windows("CALCULATOR 11-5-2007.xls").Visible = False

End Sub

Private Sub cmbLogin_Click()

Select Case
* * Case "Peter Maida"
* * * * If tbxPassword = "peterm2" Then
* * * * * * Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible =
xlSheetHidden * * *'<===ERROR ERROR
* * * * * * Sheets("QUOTE").Visible = xlSheetVisible
* * * * * * With Sheets("QUOTE")
* * * * * * * * .Unprotect Password:="AdTech"
* * * * * * * * .Range("F3").Value = "Peter Maida *Ext.. 208"
* * * * * * * * .Range("F4").Value = "E-mail: "
* * * * * * * * .Protect Password:="AdTech"
* * * * * * End With
* * * * * * Unload Me
* * * * * * Exit Sub
* * * * Else
* * * * * * MsgBox "Try agian.", vbCritical
* * * * * * With tbxPassword
* * * * * * * * .Value = ""
* * * * * * * * .SetFocus
* * * * * * End With
* * * * End If
End Select

End Sub

Unhide Worksheet with Login UserForm
 

Thanks for the quick response guys! That syntax fixed that problem. Now I
have a different error: Run-time Error '1004' Method 'Sheets' of object
'_Global' failed. Here is my updated code:

Private Sub cmbLogin_Click()

Select Case cboUserName
Case "Peter Maida"
If tbxPassword = "peterm2" Then
Workbooks("CALCULATOR
11-5-2007.xls").Worksheets("QUOTE").Visible = True
With Sheets("QUOTE") '<===ERROR ERROR
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With
Unload Me
Exit Sub
Else
MsgBox "YOUR MEMORY IS IMPRESSIVE! That's NOT the right
password. Try agian.", vbCritical
With tbxPassword
.Value = ""
End With
End If
End Select

End Sub

I have confirmed the password is correct. Any other ideas?

"Ken" wrote:

Ryan

I think it should just be

Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible = True

Good luck.

Ken
Norfolk, Va


On Jan 4, 11:11 am, RyanH wrote:
I have a UserForm that is shown when I open a workbook. The UserForm has a
UserName ComboBox, Password TextBox, Login CommandButton, and a Cancel
CommandButton. I only want .Sheets("QUOTE") to Unhide when this particular
user clicks the Login button, how do I do that? This is what I got so far,
but I am getting an Error.

Note: I don't know if this helps but I use this code to Hide all the
worksheets, when I close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Windows("CALCULATOR 11-5-2007.xls").Visible = False

End Sub

Private Sub cmbLogin_Click()

Select Case
Case "Peter Maida"
If tbxPassword = "peterm2" Then
Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible =
xlSheetHidden '<===ERROR ERROR
Sheets("QUOTE").Visible = xlSheetVisible
With Sheets("QUOTE")
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext.. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With
Unload Me
Exit Sub
Else
MsgBox "Try agian.", vbCritical
With tbxPassword
.Value = ""
.SetFocus
End With
End If
End Select

End Sub

Unhide Worksheet with Login UserForm
 

Ooops!! Sorry this is what I have. Ignore my last post. Error is Method
'Visible' of object 'Sheets'

Private Sub cmbLogin_Click()

Select Case cboUserName
Case "Peter Maida"
If tbxPassword = "peterm2" Then

With Workbooks("CALCULATOR 11-5-2007.xls")
.Unprotect ("AdTech")
.Worksheets.Visible = False <==ERROR
.Sheets("QUOTE").Visible = True
.Protect ("AdTech")
End With

With Sheets("QUOTE")
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With

Unload Me
Exit Sub
Else
MsgBox "YOUR MEMORY IS IMPRESSIVE! That's NOT the right
password. Try agian.", vbCritical
With tbxPassword
.Value = ""
.SetFocus
End With
End If
End Select

End Sub

"RyanH" wrote:

Thanks for the quick response guys! That syntax fixed that problem. Now I
have a different error: Run-time Error '1004' Method 'Sheets' of object
'_Global' failed. Here is my updated code:

Private Sub cmbLogin_Click()

Select Case cboUserName
Case "Peter Maida"
If tbxPassword = "peterm2" Then
Workbooks("CALCULATOR
11-5-2007.xls").Worksheets("QUOTE").Visible = True
With Sheets("QUOTE") '<===ERROR ERROR
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With
Unload Me
Exit Sub
Else
MsgBox "YOUR MEMORY IS IMPRESSIVE! That's NOT the right
password. Try agian.", vbCritical
With tbxPassword
.Value = ""
End With
End If
End Select

End Sub

I have confirmed the password is correct. Any other ideas?

"Ken" wrote:

Ryan

I think it should just be

Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible = True

Good luck.

Ken
Norfolk, Va


On Jan 4, 11:11 am, RyanH wrote:
I have a UserForm that is shown when I open a workbook. The UserForm has a
UserName ComboBox, Password TextBox, Login CommandButton, and a Cancel
CommandButton. I only want .Sheets("QUOTE") to Unhide when this particular
user clicks the Login button, how do I do that? This is what I got so far,
but I am getting an Error.

Note: I don't know if this helps but I use this code to Hide all the
worksheets, when I close the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Windows("CALCULATOR 11-5-2007.xls").Visible = False

End Sub

Private Sub cmbLogin_Click()

Select Case
Case "Peter Maida"
If tbxPassword = "peterm2" Then
Workbooks("CALCULATOR 11-5-2007.xls").Worksheets.Visible =
xlSheetHidden '<===ERROR ERROR
Sheets("QUOTE").Visible = xlSheetVisible
With Sheets("QUOTE")
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext.. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With
Unload Me
Exit Sub
Else
MsgBox "Try agian.", vbCritical
With tbxPassword
.Value = ""
.SetFocus
End With
End If
End Select

End Sub

Unhide Worksheet with Login UserForm
 

"RyanH" skrev i en meddelelse
...
Ooops!! Sorry this is what I have. Ignore my last post. Error is Method
'Visible' of object 'Sheets'

Private Sub cmbLogin_Click()

Select Case cboUserName
Case "Peter Maida"
If tbxPassword = "peterm2" Then

With Workbooks("CALCULATOR 11-5-2007.xls")
.Unprotect ("AdTech")
.Worksheets.Visible = False <==ERROR
.Sheets("QUOTE").Visible = True
.Protect ("AdTech")
End With

With Sheets("QUOTE")
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With

Unload Me
Exit Sub
Else
MsgBox "YOUR MEMORY IS IMPRESSIVE! That's NOT the right
password. Try agian.", vbCritical
With tbxPassword
.Value = ""
.SetFocus
End With
End If
End Select

End Sub

"RyanH" wrote:

Hi Ryan

You have to refer to at worksheet name or a worksheet index number, ie
Worksheets(1).visible =....

If you are trying to hide all sheets but "Quote" try this:


With Workbooks("CALCULATOR 11-5-2007.xls")
.Unprotect ("AdTech")
For sh = 1 To .Sheets.Count
If .Sheets(sh).Name = "QUOTE" Then
.Sheets(sh).Visible = True
Else
.Sheets(sh).Visible = False
End If
Next
.Protect ("AdTech")
End With

Regards,

Per

Unhide Worksheet with Login UserForm
 

I think this will work, with one exception. The first time I tried the loop
you suggested it did not show the Sheet."QUOTE". But I added this line of
code below the With Statement to get it to show, why is that? Does it have
something to do with the window protection settings?

Windows("CALCULATOR 11-5-2007.xls").Visible = True

Maybe I should mention that when I close the workbook I have this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Windows("CALCULATOR 11-5-2007.xls").Visible = False

End Sub




"Per Jessen" wrote:


"RyanH" skrev i en meddelelse
...
Ooops!! Sorry this is what I have. Ignore my last post. Error is Method
'Visible' of object 'Sheets'

Private Sub cmbLogin_Click()

Select Case cboUserName
Case "Peter Maida"
If tbxPassword = "peterm2" Then

With Workbooks("CALCULATOR 11-5-2007.xls")
.Unprotect ("AdTech")
.Worksheets.Visible = False <==ERROR
.Sheets("QUOTE").Visible = True
.Protect ("AdTech")
End With

With Sheets("QUOTE")
.Unprotect Password:="AdTech"
.Range("F3").Value = "Peter Maida Ext. 208"
.Range("F4").Value = "E-mail: "
.Protect Password:="AdTech"
End With

Unload Me
Exit Sub
Else
MsgBox "YOUR MEMORY IS IMPRESSIVE! That's NOT the right
password. Try agian.", vbCritical
With tbxPassword
.Value = ""
.SetFocus
End With
End If
End Select

End Sub

"RyanH" wrote:

Hi Ryan

You have to refer to at worksheet name or a worksheet index number, ie
Worksheets(1).visible =....

If you are trying to hide all sheets but "Quote" try this:


With Workbooks("CALCULATOR 11-5-2007.xls")
.Unprotect ("AdTech")
For sh = 1 To .Sheets.Count
If .Sheets(sh).Name = "QUOTE" Then
.Sheets(sh).Visible = True
Else
.Sheets(sh).Visible = False
End If
Next
.Protect ("AdTech")
End With

Regards,

Per