Visible property for worksheets objects

[vitorbarbosa1979]

Hi. I have a problem I'm needing some help to solve it.

My project is something like this:

In my first worksheet (that I called Login) I have some protected data
which is used by a userform for a login. It contais a column with a
ID, another with his name, another for his password and finally one
for his Department ID.
After this "Login" worksheet I have a few others which name is the
DeparmentID.

Im my "frmLogin" userform I have one combobox (cboNumMec), one textbox
(txtPassword) and three Commandbuttons (OK, Cancel, Change password).
For my combobox I set as rowsource D2:G200 (containing ID, Name,
Password, DepartmentID) and as ColumnWidths 40 pt;110 pt;0 pt;0 pt
(hiding password and department).
Assigned to OK button I have this code:

----------------------------------------------------------------------------------------------------------------------------
Private Sub btnOK_Click()
On Error GoTo Err_btnOK_Click

Dim strNumServiço, strNumMec, strPassword, strPasswordInserida As
String
Dim booPasswordInseridaCheck, booNumMecCheck As Boolean

strNumMec = Me.cboNumMec.Value
strPassword = Me.cboNumMec.Column(2)
strNumServiço = Me.cboNumMec.Column(3)
strPasswordInserida = Me.txtPassword.Value

booNumMecCheck = IsNull(strNumMec)
booPasswordInseridaCheck = IsNull(strPasswordInserida)

If booNumMecCheck = True And booPasswordInseridaCheck = False Then
MsgBox "Falta o número de utilizador.", vbInformation, "Dados em
falta"
Me.cboNumMec.SetFocus
Exit Sub
ElseIf booPasswordInseridaCheck = True And booNumMecCheck = False Then
MsgBox "Falta a palvra-passe.", vbInformation, "Dados em falta"
Me.txtPassword.SetFocus
Exit Sub
ElseIf booNumMecCheck = True And booPasswordInseridaCheck = True Then
MsgBox "Falta o número de utilizador e a palavra-passe.",
vbInformation, "Dados em falta"
Me.cboNumMec.SetFocus
Exit Sub
End If

If strPassword = strPasswordInserida Then
Worksheets(strNumServiço).Visible = True
Application.GoTo Reference:=Worksheets(strNumServiço).Range("A1")
Sheets(1).Visible = False
Me.Hide
Else
MsgBox "Palavra-passe incorrecta. Tente novamente", vbInformation,
"Login"
Me.txtPassword.SetFocus
Exit Sub
End If

Exit_btnOK_Click:
Exit Sub

Err_btnOK_Click:
Select Case Err.Number
Case 9
MsgBox "O código de serviço introduzido não consta deste
documento. Seleccione um dos disponíveis.", vbInformation, "Serviço
não encontrado"
Case 381
MsgBox "Para poder alterar a palavra-passe, insira o nome
de utilizador.", vbInformation, "Alteração da palavra-passe"
Case Else
MsgBox Err.Description
Resume Exit_btnOK_Click
End Select

End Sub
---------------------------------------------------------------------------------------------------------------


When I run my project I get a «Run-time error '9' Subscript ou of
range» when it gets here:
Worksheets(strNumServiço).Visible = True

I don't know why I can't use a string to store worksheet name. How can
I solve this?

 

[Doug Glancy]

Hi,

One thing is that in the statement:

Dim strNumServiço, strNumMec, strPassword, strPasswordInserida As String

only strPasswordInserida is being declared as a String. The first 3
variables are being declared as Variants. If you want them all as strings
you'd declare them like Dim strNumServiço as String, strNumMec as String ...

Since StrNumServiciao is a Variant, if it contains only numerals VBA will
intrepret it as a number (Long, I think). In that case the statement:

Worksheets(strNumServiço).Visible = True

would treat strNumServiço as a worksheet index, not a name. And if there is
no worksheet with that index, you'll get a "Subscript out of Range" error.

I don't know if that's what's happening, but maybe. To test it, put a
breakpoint on that line and check whether strNumServiço is a string or a
number. You can just hover over it, if it's in quotes it's a string.

hth,

Doug


Hi. I have a problem I'm needing some help to solve it.

My project is something like this:

In my first worksheet (that I called Login) I have some protected data
which is used by a userform for a login. It contais a column with a
ID, another with his name, another for his password and finally one
for his Department ID.
After this "Login" worksheet I have a few others which name is the
DeparmentID.

Im my "frmLogin" userform I have one combobox (cboNumMec), one textbox
(txtPassword) and three Commandbuttons (OK, Cancel, Change password).
For my combobox I set as rowsource D2:G200 (containing ID, Name,
Password, DepartmentID) and as ColumnWidths 40 pt;110 pt;0 pt;0 pt
(hiding password and department).
Assigned to OK button I have this code:

----------------------------------------------------------------------------------------------------------------------------
Private Sub btnOK_Click()
On Error GoTo Err_btnOK_Click

Dim strNumServiço, strNumMec, strPassword, strPasswordInserida As
String
Dim booPasswordInseridaCheck, booNumMecCheck As Boolean

strNumMec = Me.cboNumMec.Value
strPassword = Me.cboNumMec.Column(2)
strNumServiço = Me.cboNumMec.Column(3)
strPasswordInserida = Me.txtPassword.Value

booNumMecCheck = IsNull(strNumMec)
booPasswordInseridaCheck = IsNull(strPasswordInserida)

If booNumMecCheck = True And booPasswordInseridaCheck = False Then
MsgBox "Falta o número de utilizador.", vbInformation, "Dados em
falta"
Me.cboNumMec.SetFocus
Exit Sub
ElseIf booPasswordInseridaCheck = True And booNumMecCheck = False Then
MsgBox "Falta a palvra-passe.", vbInformation, "Dados em falta"
Me.txtPassword.SetFocus
Exit Sub
ElseIf booNumMecCheck = True And booPasswordInseridaCheck = True Then
MsgBox "Falta o número de utilizador e a palavra-passe.",
vbInformation, "Dados em falta"
Me.cboNumMec.SetFocus
Exit Sub
End If

If strPassword = strPasswordInserida Then
Worksheets(strNumServiço).Visible = True
Application.GoTo Reference:=Worksheets(strNumServiço).Range("A1")
Sheets(1).Visible = False
Me.Hide
Else
MsgBox "Palavra-passe incorrecta. Tente novamente", vbInformation,
"Login"
Me.txtPassword.SetFocus
Exit Sub
End If

Exit_btnOK_Click:
Exit Sub

Err_btnOK_Click:
Select Case Err.Number
Case 9
MsgBox "O código de serviço introduzido não consta deste
documento. Seleccione um dos disponíveis.", vbInformation, "Serviço
não encontrado"
Case 381
MsgBox "Para poder alterar a palavra-passe, insira o nome
de utilizador.", vbInformation, "Alteração da palavra-passe"
Case Else
MsgBox Err.Description
Resume Exit_btnOK_Click
End Select

End Sub
---------------------------------------------------------------------------------------------------------------


When I run my project I get a «Run-time error '9' Subscript ou of
range» when it gets here:
Worksheets(strNumServiço).Visible = True

I don't know why I can't use a string to store worksheet name. How can
I solve this?

 

[ward376]

What is the value when you watch the variable strNumServiço?

Cliff Edwards

 

[vitorbarbosa1979]

If I let this statement:
----------------------------------------------------------------------------------------------------------------
Dim strNumServiço, strNumMec, strPassword, strPasswordInserida As
String
----------------------------------------------------------------------------------------------------------------

I would get strNumServiço = 30205 (long) but If I change it to this:

----------------------------------------------------------------------------------------------------------------
Dim strNumServiço As String, strNumMec As String, strPassword As
String, strPasswordInserida As String
----------------------------------------------------------------------------------------------------------------

As Doug suggested, I would get strNumServiço = "30205" (string). So,
Doug was completely correct.

THANK YOU SO MUCH!!!

 

[Doug Glancy]

You are very welcome.

Doug

If I let this statement:
----------------------------------------------------------------------------------------------------------------
Dim strNumServiço, strNumMec, strPassword, strPasswordInserida As
String
----------------------------------------------------------------------------------------------------------------

I would get strNumServiço = 30205 (long) but If I change it to this:

----------------------------------------------------------------------------------------------------------------
Dim strNumServiço As String, strNumMec As String, strPassword As
String, strPasswordInserida As String
----------------------------------------------------------------------------------------------------------------

As Doug suggested, I would get strNumServiço = "30205" (string). So,
Doug was completely correct.

THANK YOU SO MUCH!!!