MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Calling a function and retrieving a value


Posted by Stan on November 22, 2001 8:23 AM

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


Posted by Ivan F Moala on November 22, 2001 7:16 PM

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)