Runtime Error '1004'

[Guest]

Previously, I received a runtime error '9': Subscript out of range. Now I
get a runtime error '1004': Unable to set the Visible property of the
Worksheet class at
Sheets(sSName).Visible = True
in the code below.

Does anyone have an idea of what other causes there could be?

Perhaps someone could design a workbook and see if the result is the same.
The instructions are at
http://exceltips.vitalnews.com/Pages/T0087_Protecting_Individual_Worksheets_by_User.html.

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean

bOK2Use = False
bError = True
If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
bError = False
Select Case txtUser.Text
Case "Austin"
sSName = "Corp"
If txtPass.Text <> "u1pass" Then bError = True
Case "Tejas"
sSName = "Elect"
If txtPass.Text <> "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate

bOK2Use = True
Unload UserForm1
End If
End Sub

Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close (False)
End If
End Sub

 

[Guest]

Is your workbook protected? you can not unhide a sheet in a protected book

ThisWorkbook.Unprotect

 

[Guest]

Dim bOK2Use As Boolean

Private Sub btnOK_Click()
Dim bError As Boolean
Dim sSName As String
Dim p As DocumentProperty
Dim bSetIt As Boolean
Dim sh as Worksheet

bOK2Use = False
bError = True
If Len(txtUser.Text) > 0 And Len(txtPass.Text) > 0 Then
bError = False
Select Case txtUser.Text
Case "Austin"
sSName = "Corp"
If txtPass.Text <> "u1pass" Then bError = True
Case "Tejas"
sSName = "Elect"
If txtPass.Text <> "u2pass" Then bError = True
Case Else
bError = True
End Select
End If
If bError Then
MsgBox "Invalid User Name or Password"
Else
'Set document property
bSetIt = False
For Each p In ActiveWorkbook.CustomDocumentProperties
If p.Name = "auth" Then
p.Value = sSName
bSetIt = True
Exit For
End If
Next p
If Not bSetIt Then
ActiveWorkbook.CustomDocumentProperties.Add _
Name:="auth", LinkToContent:=False, _
Type:=msoPropertyTypeString, Value:=sSName
End If
set sh = Nothing
On Error Resume Next
set sh = Worksheets(sName)
On Error goto 0
if sh is nothing then
msgbox sName & " sheet does not exist"
else

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate
end if
bOK2Use = True
Unload UserForm1
End If
End Sub

Private Sub UserForm_Terminate()
If Not bOK2Use Then
ActiveWorkbook.Close False
End If
End Sub

 

[Guest]

Thanks, Jim. I'll try that. However, just FYI, that was one of the steps
listed in the instructions.

 

[Guest]

Thanks, Tom. I don't get the runtime error anymore; however, it tells me
"sheet does not exist." So, I have three worksheets named Main, Corp, and
Elect. The worksheets Corp and Elect are hidden and the workbook is
unprotected, how do I get the code to perform as written?

In the interest of others I am going to explain what I am trying to do since
I noticed I failed to do so initially so I apologize for failing to follow
forum rules.

I am using Excel 2003 and the Win XP OS.

When the workbook opens, a form opens on the Main worksheet asking for the
user's name and password. Afterwards, the specified worksheet opens and the
user can do whatever needs to be done. However, the user cannot view the
other worksheet(s).

 

[Guest]

Tom's code has a small typo... Should be sSName not sName...

set sh = Worksheets(sSName)

What happens now???

As an aside try adding "option explicit" at the top of the cod emodule to
catch undeclared variables such as sName...
http://www.cpearson.com/excel/DeclaringVariables.htm

 

[Tom Ogilvy]

As Jim said, but just to be clear

set sh = Nothing
On Error Resume Next
set sh = Worksheets(sSName) '<== Here
On Error goto 0
if sh is nothing then
msgbox sSName & " sheet does not exist" '<== Here
else

Sheets(sSName).Visible = True
Sheets(sSName).Unprotect (txtPass.Text)
Sheets(sSName).Activate
end if


Since you didn't get the 1004 error, I suspect you will find that you are
not getting a valid sheet name in sSName. You sheet names have leading or
trailing spaces in them - check them carefully.

 

[Guest]

Tom, Jim,

It worked. I have been working on this for almost a week all because the
code you gave me is different from the code in the example (from Vital News)
I was using to accomplish this task.

I thank you two for your assistance.