Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to publicly declare a variable?
I have this code in "ThisWorkbook":
Option Explicit Public sShName As String Public bPwrdEntrd As Boolean Public sPwrd As String Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform (ufPwrdEntry): Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I'm getting a "Variable not defined" error on the noted line. I checked in Walkenbach, which says, "To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module at the module level by using the "Public" keyword rather than "Dim"." So is a userform not counted as part of the project? Why isn't this varible being recognized? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to publicly declare a variable?
because you have them declared in a workbook/worksheet sub instead of a module.... open a new module & declare all your public variables THERE. susan On Jan 30, 12:18 pm, "davegb" wrote: I have this code in "ThisWorkbook": Option Explicit Public sShName As String Public bPwrdEntrd As Boolean Public sPwrd As String Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform (ufPwrdEntry): Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I'm getting a "Variable not defined" error on the noted line. I checked in Walkenbach, which says, "To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module at the module level by using the "Public" keyword rather than "Dim"." So is a userform not counted as part of the project? Why isn't this varible being recognized? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to publicly declare a variable?
Thanks for your reply. I moved the declarations out of "ThisWorkbook"
and put them in a Module. So I guess public declarations in "ThisWorkbook" don't count? In any case, now I'm getting an error message on the next line where I set the variable equal to the textbox string saying "Object doesn't support this method or property"! Any ideas on that? On Jan 30, 10:21 am, "Susan" wrote: because you have them declared in a workbook/worksheet sub instead of a module.... open a new module & declare all your public variables THERE. susan On Jan 30, 12:18 pm, "davegb" wrote: I have this code in "ThisWorkbook": Option Explicit Public sShName As String Public bPwrdEntrd As Boolean Public sPwrd As String Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform (ufPwrdEntry): Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I'm getting a "Variable not defined" error on the noted line. I checked in Walkenbach, which says, "To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module at the module level by using the "Public" keyword rather than "Dim"." So is a userform not counted as part of the project? Why isn't this varible being recognized? Thanks!- Hide quoted text -- Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to publicly declare a variable?
whenever i get that error, i either add "set" to the statement (if it
isn't there), or delete it if it is......... cuz i can never remember when i have to use it or not. try that. :) set sPwrd = ufPwrdEntry.tbPwrdEntry.Value it's also POSSIBLE (but i'm not sure) that by using .value you are implying a numerical variable, where sPwrd is declared as a string..... if a "guru-type-person" checks in, they will tell us if that's true or not. :) susan On Jan 30, 12:37 pm, "davegb" wrote: Thanks for your reply. I moved the declarations out of "ThisWorkbook" and put them in a Module. So I guess public declarations in "ThisWorkbook" don't count? In any case, now I'm getting an error message on the next line where I set the variable equal to the textbox string saying "Object doesn't support this method or property"! Any ideas on that? On Jan 30, 10:21 am, "Susan" wrote: because you have them declared in a workbook/worksheet sub instead of a module.... open a new module & declare all your public variables THERE. susan On Jan 30, 12:18 pm, "davegb" wrote: I have this code in "ThisWorkbook": Option Explicit Public sShName As String Public bPwrdEntrd As Boolean Public sPwrd As String Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform (ufPwrdEntry): Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I'm getting a "Variable not defined" error on the noted line. I checked in Walkenbach, which says, "To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module at the module level by using the "Public" keyword rather than "Dim"." So is a userform not counted as part of the project? Why isn't this varible being recognized? Thanks!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to publicly declare a variable?
Thanks for your reply, Susan. In this case, that won't help. Set is
for setting an object equal to something, like a workbook, worksheet, range, etc. This is a string, so "Set" doesn't apply. On Jan 30, 11:00 am, "Susan" wrote: whenever i get that error, i either add "set" to the statement (if it isn't there), or delete it if it is......... cuz i can never remember when i have to use it or not. try that. :) set sPwrd = ufPwrdEntry.tbPwrdEntry.Value it's also POSSIBLE (but i'm not sure) that by using .value you are implying a numerical variable, where sPwrd is declared as a string..... if a "guru-type-person" checks in, they will tell us if that's true or not. :) susan On Jan 30, 12:37 pm, "davegb" wrote: Thanks for your reply. I moved the declarations out of "ThisWorkbook" and put them in a Module. So I guess public declarations in "ThisWorkbook" don't count? In any case, now I'm getting an error message on the next line where I set the variable equal to the textbox string saying "Object doesn't support this method or property"! Any ideas on that? On Jan 30, 10:21 am, "Susan" wrote: because you have them declared in a workbook/worksheet sub instead of a module.... open a new module & declare all your public variables THERE. susan On Jan 30, 12:18 pm, "davegb" wrote: I have this code in "ThisWorkbook": Option Explicit Public sShName As String Public bPwrdEntrd As Boolean Public sPwrd As String Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform (ufPwrdEntry): Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I'm getting a "Variable not defined" error on the noted line. I checked in Walkenbach, which says, "To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module at the module level by using the "Public" keyword rather than "Dim"." So is a userform not counted as part of the project? Why isn't this varible being recognized? Thanks!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to publicly declare a variable?
see, i'm never sure! ha ha
maybe you should start a new thread with your most recent problem, since i obviously don't know the answer. :) susan On Jan 30, 1:11 pm, "davegb" wrote: Thanks for your reply, Susan. In this case, that won't help. Set is for setting an object equal to something, like a workbook, worksheet, range, etc. This is a string, so "Set" doesn't apply. On Jan 30, 11:00 am, "Susan" wrote: whenever i get that error, i either add "set" to the statement (if it isn't there), or delete it if it is......... cuz i can never remember when i have to use it or not. try that. :) set sPwrd = ufPwrdEntry.tbPwrdEntry.Value it's also POSSIBLE (but i'm not sure) that by using .value you are implying a numerical variable, where sPwrd is declared as a string..... if a "guru-type-person" checks in, they will tell us if that's true or not. :) susan On Jan 30, 12:37 pm, "davegb" wrote: Thanks for your reply. I moved the declarations out of "ThisWorkbook" and put them in a Module. So I guess public declarations in "ThisWorkbook" don't count? In any case, now I'm getting an error message on the next line where I set the variable equal to the textbox string saying "Object doesn't support this method or property"! Any ideas on that? On Jan 30, 10:21 am, "Susan" wrote: because you have them declared in a workbook/worksheet sub instead of a module.... open a new module & declare all your public variables THERE. susan On Jan 30, 12:18 pm, "davegb" wrote: I have this code in "ThisWorkbook": Option Explicit Public sShName As String Public bPwrdEntrd As Boolean Public sPwrd As String Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform (ufPwrdEntry): Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I'm getting a "Variable not defined" error on the noted line. I checked in Walkenbach, which says, "To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module at the module level by using the "Public" keyword rather than "Dim"." So is a userform not counted as part of the project? Why isn't this varible being recognized? Thanks!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to publicly declare a variable?
On Jan 30, 12:02 pm, "Susan" wrote:
see, i'm never sure! ha ha maybe you should start a new thread with your most recent problem, since i obviously don't know the answer. :) susan On Jan 30, 1:11 pm, "davegb" wrote: Thanks for your reply, Susan. In this case, that won't help. Set is for setting an object equal to something, like a workbook, worksheet, range, etc. This is a string, so "Set" doesn't apply. On Jan 30, 11:00 am, "Susan" wrote: whenever i get that error, i either add "set" to the statement (if it isn't there), or delete it if it is......... cuz i can never remember when i have to use it or not. try that. :) set sPwrd = ufPwrdEntry.tbPwrdEntry.Value it's also POSSIBLE (but i'm not sure) that by using .value you are implying a numerical variable, where sPwrd is declared as a string..... if a "guru-type-person" checks in, they will tell us if that's true or not. :) susan On Jan 30, 12:37 pm, "davegb" wrote: Thanks for your reply. I moved the declarations out of "ThisWorkbook" and put them in a Module. So I guess public declarations in "ThisWorkbook" don't count? In any case, now I'm getting an error message on the next line where I set the variable equal to the textbox string saying "Object doesn't support this method or property"! Any ideas on that? On Jan 30, 10:21 am, "Susan" wrote: because you have them declared in a workbook/worksheet sub instead of a module.... open a new module & declare all your public variables THERE. susan On Jan 30, 12:18 pm, "davegb" wrote: I have this code in "ThisWorkbook": Option Explicit Public sShName As String Public bPwrdEntrd As Boolean Public sPwrd As String Private Sub Workbook_SheetChange(ByVal wsShName As Object, ByVal Target As Range) If Right(wsShName.Name, 7) < "Monthly" Then 'ignore these sheeets Select Case wsShName.Name Case "(Code Key)" 'Excluded sheets Exit Sub Case Else 'if password hasn't been entered yet, then ask for password If bPwrdEntrd = False Then Call PasswordEntry End If End Select End If End Sub Sub PasswordEntry() Dim rFoundShName As Range Dim rShNames As Range Dim wsPwrdNames As Worksheet 'Dim sPwrd As String Set wsPwrdNames = ThisWorkbook.Sheets("sheet1") Set rShNames = wsPwrdNames.Range("A1:A8") wsPwrdNames.Visible = True ufPwrdEntry.Show Set rFoundShName = rShNames.Find(ActiveSheet.Name, _ LookIn:=xlValues, _ LookAt:=xlWhole) If sPwrd = rFoundShName.Offset(1, 0).Value Then bPwrdEntrd = True End If wsPwrdNames.Visible = False End Sub And this code in the userform (ufPwrdEntry): Sub cbOK_Click() sPwrd = ufPwrdEntry.tbPwrdEntry.Value <----VARIABLE NOT DEFINED ERROR If sPwrd = "" Then MsgBox "Please enter the password to access this worksheet.", vbOKCancel End If End Sub I'm getting a "Variable not defined" error on the noted line. I checked in Walkenbach, which says, "To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module at the module level by using the "Public" keyword rather than "Dim"." So is a userform not counted as part of the project? Why isn't this varible being recognized? Thanks!- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text - - Show quoted text - Thanks for your help! I'll re-post. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declare Variable | Excel Programming | |||
Declare Variable | Excel Programming | |||
Declare Variable | Excel Programming | |||
Declare Variable | Excel Programming | |||
Declare Variable | Excel Programming |