How to Include Userform for Inputbox in VBA

[Rajeshkumar R]

Hello Expert,

In the attached file I have some VBA code.

My concern here is, when I enter the password it is visible on the screen. Instead of this I would like to get something like this ********.

I had gone through few website and comes to userform is the way to achieve the same. However due to my lack of knowledge I am not able to incorporate the same in attached file.

Hence, need your help to accomplish the same. Immediate help is really appreciated!

Thanks & Regards,
Rajeshkumar R

[Debraj Roy]

in > User Form1
select > Text Box 1
in properties section.. (press f4) You will found a object called "PasswordChar"
set this to "*"

PS:

Can someone please help me to modify below code so that I can use it in Immediate Window..

Set userform1.TextBox1.PasswordChar = "*"

I tried but its not setting as desired..
Please help..

[Andy Pope]

this will set it at design time, requires access to vba projects to be enabled.

thisworkbook.VBProject.VBComponents("Userform1").Designer.controls("TextBox1").PasswordChar="*"

What I think you really mean is this, add code for Pwd4 only

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pwd As String, PwdChk As String
Dim frmPwd As UserForm1

If Target.Cells.Count > 1 Then
    MsgBox "Only one cell at a time may be edited"
    GoTo UndoIT
    Exit Sub
End If

If Target.Column >= 4 And Target.Column <= 7 Then
    Select Case Target.Column
        Case 4
            PwdChk = P4             'column D
            If Len(Pwd4) = 0 Then
                Set frmPwd = New UserForm1
                Load frmPwd
                frmPwd.TextBox1.PasswordChar = "*"
                frmPwd.Show
                Pwd4 = frmPwd.TextBox1.Text
                Unload frmPwd
                Set frmPwd = Nothing
            End If
            If Pwd4 <> P4 Then
                MsgBox "Password incorrect"
                Pwd4 = ""
                GoTo UndoIT
            End If
        Case 5
            PwdChk = P5             'column E
            If Len(Pwd5) = 0 Then Pwd5 = Application.InputBox("Enter password for this column...", "Password", Type:=2)
            If Pwd5 <> P5 Then
                MsgBox "Password incorrect"
                Pwd5 = ""
                GoTo UndoIT
            End If
        Case 6
            PwdChk = P6             'column F
            If Len(Pwd6) = 0 Then Pwd6 = Application.InputBox("Enter password for this column...", "Password", Type:=2)
            If Pwd6 <> P6 Then
                MsgBox "Password incorrect"
                Pwd6 = ""
                GoTo UndoIT
            End If
        Case 7
            PwdChk = P7             'column G
            If Len(Pwd7) = 0 Then Pwd7 = Application.InputBox("Enter password for this column...", "Password", Type:=2)
            If Pwd7 <> P7 Then
                MsgBox "Password incorrect"
                Pwd7 = ""
                GoTo UndoIT
            End If
    End Select
End If

Exit Sub

UndoIT:
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End Sub

[Rajeshkumar R]

Hi Debraj Roy & Andy Pope,

Thanks for your immediate response on my query!

Aside Andy Pope,

Your suggestion looks like fine for me. However, when I copy pasted the VBA code into my file and trying to access column D, I was
getting the Error as below:-

“Compile Error: Invalid outside Procedure”.

Can you please help me to understand what is that, I am doing wrong and how to resolve the same?

Thanks & Regards,
Rajeshkumar R

[Debraj Roy]

Hi Rajesh..

In which sheet you are working.. in the same sheet.. at sheet name right click and view code..
in the opened area.. delete all codes.. (if any worksheet_change alreday exists) then
paste Pope's code there..
no need to insert a new module.. or something..

try again and let us know..

[Rajeshkumar R]

Hi Debraj Roy,

Once again thanks for your replay. Can you please confirm, should I retain below mentioned codes on top of Andy Pope codes or only his suggest code is okay. If so I am wondering, no where we have defined the passowrd for each column.

Option Explicit
Const P4 = "Password4", P5 = "Password5", P6 = "Password6", P7 = "Password7"
Public Pwd4 As String, Pwd5 As String, Pwd6 As String, Pwd7 As String

Can you please clarify on this?

Thanks & Regards,
Rajeshkumar R

Moderator Note:

Pls use code tags around your code next time as per forum rules.

[Andy Pope]

simply replace the event code, leave other code intact.

[Rajeshkumar R]

Hello Andy Pope,

I tried in all the way, but due to my lack of knowledge I couldn’t able to achieve what I exactly need and also am not able to find where exactly I am doing wrong.

Hence, Can you please share my attached excel file with your VBA Code? Hope this will be a great support for me to move forward!

Thanks & Regards,
Rajeshkumar R

[Rajeshkumar R]

Hi Andy Pope,

As per your last suggestion I did and now it is working fine. But, I am find the problem as below:-

1. After entering the correct password, userform does not exit automatically, we have to press right top “X” ie close button to get rid of userform

2. If we enter the wrong password also, after pressing right top “X” close buttong only “Password incorrect” signal is coming

Get rid of userform has to be automatic, once we press enter after key in the password. Can you please help me in accomplishing the same?

Thanks & Regards,
Rajeshkumar R

[Andy Pope]

you need to post your most recent workbook so we can see what you are working with now.

[Rajeshkumar R]

Hi Andy Pope,

Here is my latest working file for your reference.

Thanks & Regards,
Rajeshkumar R

[Andy Pope]

You have no code in the button click events on the userform.
Also you need to add code to check whehter the user cancelled the userform or not.

                Set frmPwd = New UserForm1
                Load frmPwd
                frmPwd.TextBox1.PasswordChar = "*"
                If Not frmPwd.UserCancel Then
                    Pwd4 = frmPwd.TextBox1.Text
                    If Pwd4 <> P4 Then
                        MsgBox "Password incorrect"
                        Pwd4 = ""
                        GoTo UndoIT
                    End If
                Else
                    GoTo UndoIT
                End If
                Unload frmPwd
                Set frmPwd = Nothing

There is also some code addition to the userform code module required

Private m_blnUserCancelled As Boolean
Public Function UserCancel() As Boolean
    m_blnUserCancelled = True
    Me.Show
    UserCancel = m_blnUserCancelled
End Function


Private Sub CommandButton1_Click()
    m_blnUserCancelled = False
    Me.Hide
End Sub

Private Sub CommandButton2_Click()
    m_blnUserCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then
        MsgBox "Use Buttons"
        Cancel = True
        Exit Sub
    End If
End Sub

[Rajeshkumar R]

Hi Andy Pope,

Thank you So much for your extended support on this.

Now the file is working exactly the way I was expecting!

Thanks & Regards,
Rajeshkumar R