Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visible property for worksheets objects
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 he 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visible property for worksheets objects
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 wrote in message ... 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 he 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visible property for worksheets objects
What is the value when you watch the variable strNumServiço?
Cliff Edwards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visible property for worksheets objects
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!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Visible property for worksheets objects
You are very welcome.
Doug wrote in message ... 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Objects with .type property | Excel Programming | |||
VBA visible property | Excel Programming | |||
Visible property on Worksheets | Excel Programming | |||
.Visible Property | Excel Programming |