Been Googling all night and just can't seem to find the fix for this
problem (unlikely, but true...) Maybe one of the experts can help if I
am able to ask the question in an understandable way.

The offending section...

--In standard module:
Public PW,UN, APPSTRING as String
Sub OpenWorkbook()
'initialize public variables
APPSTRING = ThisWorkbook.Worksheets("SundaySheet").Range
_("APPSTRING").Value
PW = ThisWorkbook.Worksheets("SundaySheet").Range("PW") .Value
UN = ThisWorkbook.Worksheets("SundaySheet").Range("UN") .Value
'setup the app
UnprotectSheets
frmSplash.Show
---other setup stuff goes here
ProtectAll 'protect the sheets and workbook for vba operation
End Sub

Sub ShowAdminPW()
frmAdminPW.Show
End Sub

--In frmSplash

Sub CommandButton1_Click()
Unload Me
ShowAdminPW 'call the show method from the module
End Sub

--In frmAdminPW
(Contains TextBox1, TextBox2, CommandButton1, CommandButton2)
(User enters text into TextBox1 & TextBox2, tabs to CommandButton1)

Private Sub UserForm_Initialize() 'initialize the AdminPW form
TextBox1.Text = ""
TextBox2.Text = ""
TextBox1.SetFocus
End Sub

Private Sub CommandButton1_Click()'check the data against the variables
UN2 = TextBox1.Text
PW2 = TextBox2.Text

Select Case UN 'check the username entry for correctness
Case Is < UN2
MsgBox "Incorrect username entered. Please start again!"
TextBox1.Text = "" 'start over
TextBox1.SetFocus
TextBox2.Text = ""
Exit Sub
End Select

Select Case PW
Case Is < PW2
MsgBox "Please enter the correct password!"
TextBox2.Text = "" 'try again
TextBox2.SetFocus
Exit Sub
End Select

MsgBox "Entering Adminstrator Mode.", vbInformation, APPSTRING
Unload Me
---Administrator Mode code goes here
End Sub

Private Sub CommandButton2_Click()
MsgBox "Cancelling attempt to enter Adminstrator Mode.",
vbInformation, APPSTRING 'this public variable is passed successfully
TextBox1.Text = ""
TextBox2.Text = ""
Unload Me
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
'prevent user from closing with close box, force my cleanup with cancel
button
If CloseMode < 1 Then Cancel = 1
End Sub

The objective is to store UN, PW & APPSTRING in the sheet named
SundaySheet using Names, declare these as Public Variables, set their
values and pass these to the form for use in validating a username and
password in a logon form (frmAdminPW)

This code fails to pass the Public variables UN & PW to the
CommandButton1_Click event in the frmAdminPW, but it successfully
passes the Public Variable APPSTRING to the MsgBox Title row at the end
of the sub. Hmmmm.

Problem can be fixed by declaring the variables UN & PW in the Click
event of the CommandButton1, making them local.

I have copied some example code from the group that successfully passes
Public Variables from the module to forms, but I cannot correct my code
& make it work. I hope I have explained this well enough to be
understood...

Any help that anyone can offer is greatly appreciated.

Mike Dunworth

in your code PW and UM are variant
Public PW,UN, APPSTRING as String

try
Public PW as String,UN as String, APPSTRING as String

Also, its unsafe to save these two this way...allow the user to enter their
names and password, then test...it sfairly easy to break the code while the
form is showing, and then check the variables for their values, thus
revealing the UN & PW.

I made two changes - I always set Option Explicit as the first line of code
modules - a big help in stopping variable name typos...and also added one
line in frmAdminPW ...

Private Sub CommandButton1_Click() 'check the data against the variables
Dim UN2 As String, PW2 As String ' new

apart from these, your code worked ok for me.


"Mike Dunworth" wrote:

Been Googling all night and just can't seem to find the fix for this
problem (unlikely, but true...) Maybe one of the experts can help if I
am able to ask the question in an understandable way.

The offending section...

--In standard module:
Public PW,UN, APPSTRING as String
Sub OpenWorkbook()
'initialize public variables
APPSTRING = ThisWorkbook.Worksheets("SundaySheet").Range
_("APPSTRING").Value
PW = ThisWorkbook.Worksheets("SundaySheet").Range("PW") .Value
UN = ThisWorkbook.Worksheets("SundaySheet").Range("UN") .Value
'setup the app
UnprotectSheets
frmSplash.Show
---other setup stuff goes here
ProtectAll 'protect the sheets and workbook for vba operation
End Sub

Sub ShowAdminPW()
frmAdminPW.Show
End Sub

--In frmSplash

Sub CommandButton1_Click()
Unload Me
ShowAdminPW 'call the show method from the module
End Sub

--In frmAdminPW
(Contains TextBox1, TextBox2, CommandButton1, CommandButton2)
(User enters text into TextBox1 & TextBox2, tabs to CommandButton1)

Private Sub UserForm_Initialize() 'initialize the AdminPW form
TextBox1.Text = ""
TextBox2.Text = ""
TextBox1.SetFocus
End Sub

Private Sub CommandButton1_Click()'check the data against the variables
UN2 = TextBox1.Text
PW2 = TextBox2.Text

Select Case UN 'check the username entry for correctness
Case Is < UN2
MsgBox "Incorrect username entered. Please start again!"
TextBox1.Text = "" 'start over
TextBox1.SetFocus
TextBox2.Text = ""
Exit Sub
End Select

Select Case PW
Case Is < PW2
MsgBox "Please enter the correct password!"
TextBox2.Text = "" 'try again
TextBox2.SetFocus
Exit Sub
End Select

MsgBox "Entering Adminstrator Mode.", vbInformation, APPSTRING
Unload Me
---Administrator Mode code goes here
End Sub

Private Sub CommandButton2_Click()
MsgBox "Cancelling attempt to enter Adminstrator Mode.",
vbInformation, APPSTRING 'this public variable is passed successfully
TextBox1.Text = ""
TextBox2.Text = ""
Unload Me
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
'prevent user from closing with close box, force my cleanup with cancel
button
If CloseMode < 1 Then Cancel = 1
End Sub

The objective is to store UN, PW & APPSTRING in the sheet named
SundaySheet using Names, declare these as Public Variables, set their
values and pass these to the form for use in validating a username and
password in a logon form (frmAdminPW)

This code fails to pass the Public variables UN & PW to the
CommandButton1_Click event in the frmAdminPW, but it successfully
passes the Public Variable APPSTRING to the MsgBox Title row at the end
of the sub. Hmmmm.

Problem can be fixed by declaring the variables UN & PW in the Click
event of the CommandButton1, making them local.

I have copied some example code from the group that successfully passes
Public Variables from the module to forms, but I cannot correct my code
& make it work. I hope I have explained this well enough to be
understood...

Any help that anyone can offer is greatly appreciated.

Mike Dunworth

Thanks for the quick and complete reply. Your changes solved my problem
immediately.

A followup question, if I may?

What might you recommend as a safer construction for a logon process?

Thanks again!

Mike Dunworth