Honor 9 Lite review
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!
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)
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")
.PasswordChar = PasswordChar
.Left = 4.5
.Top = 69.75
.Width = 254.25
.Height = 15.75
.Value = Default
'Add the prompt
Set Lb = UF.Designer.Controls.Add("Forms.Label.1")
.Caption = Prompt
.WordWrap = True
.Left = 6.75
.Top = 6.75
.Width = 198
.Height = 54
'Button OK, it is the default button
Set BOk = UF.Designer.Controls.Add("Forms.CommandButton.1", "BOk")
.Caption = "OK"
.Left = 209.25
.Top = 4.5
.Width = 49.5
.Height = 18
.Default = True
Set BCancel = UF.Designer.Controls.Add("Forms.CommandButton.1", "BCancel")
.Caption = "Cancel"
.Cancel = True
.Left = 209.25
.Top = 27
.Width = 49.5
.Height = 18
'Add code to the Userform module
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"
'Properties for the userform
.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
'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
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
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 :)
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.
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'.
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.