Excel Calling a function and retrieving a value

I found a Visual Basic snippet that emulates an InputBox to encrypt a password. I have tried to adapt it to my Excel application. This is the code I use to call a Function from a UserForm:

Private Sub Command_Click()

Dim myPassword As String
'Format for password InputBox
'Return = InputBoxX("Caption","Message","Default Value", Boolean)
'In the last field:
'0 = No password x's
'1 = Use password x's

myPassword = frmInput.InputBoxX("Restricted Access", "Please enter password:", "", 1)

The function is in a Userform that looks like an InputBox:

Public InputBoxStr$
Public Function InputBoxX(bCaption As String, Msg As String, Default As String, Stars As Boolean) As String

If Stars = True Then frmInput.txtInput.PasswordChar = "x"
frmInput.Caption = bCaption
frmInput.lblMsg.Caption = Msg
frmInput.txtInput.Text = Default

frmInput.Show

Do
DoEvents
Loop Until InputBoxStr <> ""

If InputBoxStr = "ksCancel" Then
InputBoxStr = ""
InputBoxX = InputBoxStr
Unload frmInput
Exit Function
End If

Unload frmInput

InputBoxX = InputBoxStr
InputBoxStr = ""
End Function

Private Sub cmdOK_Click()
InputBoxStr = txtInput.Text
End Sub

Private Sub cmdCancel_Click()
InputBoxStr = "ksCancel"
End Sub

What happens is that the program stops at "frmInput.Show". In Visual Basic, the code works fine, i.e. it loops and takes the values for the InputBoxStr depending on which of the two command buttons is clicked. Why doesn't this code behave the same way in VBA?

Cheers - Stan

change the name of your userform to
frmInput
Also as I undertand it the Vb code in the userform
has;
1 Lable
1 textbox
2 commandbuttons
You should name these appropriately to the
names setup eg.

1 lable = Lblmsg
1 textbos = txtinput
2 cmdbuttons = cmdOk, cmdCancel

The private command_Click is the commandbutton
avail from Toolbox = activex control and
should be on your sheet.
Also note that the password character can be ANY
character you wish...Std = *

Ivan

Ivan Dim myPassword As String 'Format for password InputBox 'Return = InputBoxX("Caption","Message","Default Value", Boolean) 'In the last field: '0 = No password x's '1 = Use password x's myPassword = frmInput.InputBoxX("Restricted Access", "Please enter password:", "", 1)

Archive of Mr Excel Message Board

Calling a function and retrieving a value

Re: Calling a function and retrieving a value