Excel and Input Boxes

  mickmoran61 00:40 12 May 2004

Any VBA experts out there!
I want to create a VBA project in Excel 2000.
I sometimes use Input Boxes to collect data from users. I would like to know if it is possible to collect password information in an Input Box in such a way that the text entered in the Input Box appears in the format "*******"?
Any help would be most appreciated!


Hi Mick,

I believe it to be impossible to modify the InputBox to get what you require, however by creating your own form and placing a textbox on it, the textbox can then have any password substitute character that you wish.

Sorry I could not be of more help


  VoG II 08:15 12 May 2004

Yes it is possible. I will try to find and post the code when I get home tonight.

I wait with baited breath....I hope that VoG™ can produce a different method from the Class module.

Seriously though the only way that I could find was to create a userform by code that looked like an Inputbox, this was courtesy of Juan Pablo González, a real master in this field.

It was achieved by creating a Class module and then using an instance of said class module when required.

Hopefully this is not what VoG™ is talking about because imho to create your own userform using Forms is easier than this. Go for it VoG™

Best wishes (and hoping)


  VoG II 17:26 12 May 2004

Function PassInputBox(Prompt As String, Optional PasswordChar As String, Optional Title As String, Optional Default As String, Optional XPos As Long, Optional YPos As Long)

Dim UF 'Store the VBComponent

Dim VUF As Object 'Store the userform object

Dim Lb As Object 'Label for the Prompt

Dim Tb As Object 'TextBox which holds the password

Dim BOk As Object

Dim BCancel As Object

Dim VBAVisible As Boolean 'Store VBE.Mainwindow visible state to restore it

Dim i As Integer

'Default Title is the same as InputBox

If Len(Title) = 0 Then Title = Application.Name

'Store the visible property of the VBE mainwindow and hide it to prevent screen flashing

VBAVisible = Application.VBE.MainWindow.Visible

Application.VBE.MainWindow.Visible = False

'Add temporary Userform

Set UF = ThisWorkbook.VBProject.VBComponents.Add(3)

'Add the textbox. If no PasswordChar was supplied, the text will appear normally

Set Tb = UF.Designer.Controls.Add("Forms.Textbox.1", "TextBox1")

With Tb

.PasswordChar = PasswordChar

.Left = 4.5

.Top = 69.75

.Width = 254.25

.Height = 15.75

.Value = Default

End With

'Add the prompt

Set Lb = UF.Designer.Controls.Add("Forms.Label.1")

With Lb

.Caption = Prompt

.WordWrap = True

.Left = 6.75

.Top = 6.75

.Width = 198

.Height = 54

End With

'Button OK, it is the default button

Set BOk = UF.Designer.Controls.Add("Forms.CommandButton.1", "BOk")

With BOk

.Caption = "OK"

.Left = 209.25

.Top = 4.5

.Width = 49.5

.Height = 18

.Default = True

End With

'Button Cancel

Set BCancel = UF.Designer.Controls.Add("Forms.CommandButton.1", "BCancel")

With BCancel

.Caption = "Cancel"

.Cancel = True

.Left = 209.25

.Top = 27

.Width = 49.5

.Height = 18

End With

'Add code to the Userform module

With UF.CodeModule

i = .CountOfLines

'MyText is a variant which will hold the answer the user pressed

.InsertLines i + 1, "Public MyText as Variant"

'Pressed Cancel, so assign False to MyText

.InsertLines i + 2, "Private Sub BCancel_Click()"

.InsertLines i + 3, " MyText = False: Me.Hide"

.InsertLines i + 4, "End Sub"

'Pressed Ok, so assign the value of TextBox1 to MyText

.InsertLines i + 5, "Private Sub BOk_Click()"

.InsertLines i + 6, " MyText = TextBox1.Value: Me.Hide"

.InsertLines i + 7, "End Sub"

'Closing the form using "X", so assign False to MyText

.InsertLines i + 8, "Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)"

.InsertLines i + 9, " If CloseMode = 0 Then Cancel = True: MyText = False: Me.Hide"

.InsertLines i + 10, "End Sub"

End With

'Properties for the userform

With UF

.Properties("Caption") = Title

.Properties("Width") = 273

.Properties("Height") = 108.75

'Center on screen or show in a specific position

If XPos > 0 Or YPos > 0 Then

.Properties("StartUpPosition") = 0

.Properties("Left") = XPos

.Properties("Top") = YPos


.Properties("StartUpPosition") = 1

End If

End With

'Include the UF in the Userforms collection

Set VUF = VBA.UserForms.Add(UF.Name)

'Show the Userform


'Pass the result to this function

PassInputBox = VUF.MyText

'Remove the VBcomponet



'Restore the VBE Mainwindow

'Application.VBE.MainWindow.Visible = VBAVisible

End Function

  VoG II 17:28 12 May 2004

Sub TestMe()

Dim ans As Variant 'ans is declared Variant to work similar to Application.InputBox

Dim App As PwdInputBox 'Reference the class module

Set App = New PwdInputBox 'Create a new instance

ans = App.PassInputBox("Please enter the password", "*", "My Application") 'Show the Inputbox and store the result

If ans = False Then

MsgBox "Pressed Cancel"


MsgBox "The password entered is: " & ans

End If

End Sub


With due acknowledgement to Juan Pablo González.

Sorry Whisperer, I don't kmow another way :o(

It is still brillant coding though! but I will stick to making my own userform for passwords, that way I can control the size with the Properties :)

  mickmoran61 22:43 12 May 2004

Thank you both. I asked that query because somebody else who is programming something for me in VBA said that this could not be done. I myself would use a Userform and password properties, it seems simpler.

I will try VOG's method and let you know how I get on.

Thanks again


To get the class module to work properly I found that it needs to modify the Visual Basic Project code, which is quite safe in this case.

To permit access select <Tools> <Macro> <Security> and from the dialogue box select the Trusted Sources tab, placing a tick in the box to the left of the 'Trust access to Visual Basic Project'.


  Chris the Ancient 08:38 13 May 2004

Goodness knows how many vba routines I've written (with much help from this forum and the venerable master), but I'd never noticed that 'Trust access to Visual Basic Project' before!



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

How Sam Falconer transforms science and geology into digestible, elegant illustrations

HomePod review

Les meilleures séries Netflix (2018)