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! |
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 |