Thread: Exit Event with a single textbox control

I'd like to password protect some features of a spreadsheet, so to take
advantage of the PasswordChar Property of a Textbox control, I created
a small userform with only a text box on it. My intention was to
replace the InputBox function since it doesn't allow character
protection. Unfortunately, with no other controls on the form it
appears that I can't get the exit event to fire without closing the
userform...

If I put another text box on the userform, the exit event fires just
fine when I tab out of the first control.

Am I missing something?

Here are the applicable code fragments
'
' From a UserForm called PSSDataForm
'
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
Integer)
Load PasswordDataForm
PasswordDataForm.Show
End Sub

'
' From PasswordDataForm
' PasswordBox is the textbox control where I enter the password
'
Sub PasswordBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If PasswordBox.Value = ClosurePassword Then
Unload PSSDataForm
Unload PasswordDataForm
Else
'
' Do other stuff here
'
End If
End Sub

Excel 2002, all current service packs applied, runnning on XP Pro SP1.
(Don't start - its my work laptop and we haven't rolled out SP2 yet...)

ChazE911,
Some control needs to have the focus. If you only have a single control,
there is nowhere for the focus to go.
You could at least put a Cancel and/or OK button also, otherwise the user
may get stuck in a loop if they cannot supply the correct PW.

NickHK

<ChazE911@gmail.com> wrote in message
news:1139379022.140861.324890@g47g2000cwa.googlegroups.com...
> I'd like to password protect some features of a spreadsheet, so to take
> advantage of the PasswordChar Property of a Textbox control, I created
> a small userform with only a text box on it. My intention was to
> replace the InputBox function since it doesn't allow character
> protection. Unfortunately, with no other controls on the form it
> appears that I can't get the exit event to fire without closing the
> userform...
>
> If I put another text box on the userform, the exit event fires just
> fine when I tab out of the first control.
>
> Am I missing something?
>
> Here are the applicable code fragments
> '
> ' From a UserForm called PSSDataForm
> '
> Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As
> Integer)
> Load PasswordDataForm
> PasswordDataForm.Show
> End Sub
>
> '
> ' From PasswordDataForm
> ' PasswordBox is the textbox control where I enter the password
> '
> Sub PasswordBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> If PasswordBox.Value = ClosurePassword Then
> Unload PSSDataForm
> Unload PasswordDataForm
> Else
> '
> ' Do other stuff here
> '
> End If
> End Sub
>
> Excel 2002, all current service packs applied, runnning on XP Pro SP1.
> (Don't start - its my work laptop and we haven't rolled out SP2 yet...)
>

I just tried that basic solution. I added a second text box that's
VERY small and put it in the form as an overlay on the 'main' text box.
Kind of a kludge, I guess, but it works.

Thanks for the input.