How to make userform in Excel VBA remember passwor

Question

I have a userform that calls a function with username/password prompt.

The user usually has to run it more times and each time is prompted for the login credentials so I want the userform to remember the user/password once it has been given until the workbook has been closed.

It is working fine until the user presses the 'process' button or closes the userform from the 'close' button, but when he/she closes it with the 'red x', the user/pass disappears on the next run.

I know that the 'red x' can be disabled, but I am curious if there is another way to make the userform remember even when the 'red x' is pressed?

Here is my code so far:

Private Sub cbCancel_Click()
maplogin.Hide
End Sub

Sub cbProcess_Click()

Dim drivetomap As String
Static user As String
Static pass As String

pcname = ActiveCell.Value
user = Me.txtUser.Value
pass = Me.txtPass.Value
...

Answer 1

You can redirect the X to run the cancel button code instead:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        cbCancel_Click
    End If
End Sub

Answer 2

There area few points here. Let's deal with your specific question first: you can use the QueryClose event to handle a Userform being closed, and in your case you'd set the Cancel parameter to True (or basically any integer not 0) so that you could Hide the form rather than Unload it. Like so:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode <> vbFormCode Then
        Cancel = True
        Me.Hide
    End If
End Sub

But there are other issues.

1. If you use this Userform instance to store the password then you'd need to scope the variables at module level so that you could access the variables outside of your cbProcess_Click routine. As below:

   Option Explicit
   Private mPcName As String
   Private mUser As String
   Private mPass As String
   
   Private Sub cbCancel_Click()
       maplogin.Hide
   End Sub
   
   Sub cbProcess_Click()
   
       mPcName = ActiveCell.Value
       mUser = Me.txtUser.Value
       mPass = Me.txtPass.Value
       '...
   End Sub
   

2. The Static keyword in VBA is only useable at procedure level. Yes, it will retain the values of the variables when the procedure ends, but you still wouldn't be able to access those variables outside of the procedure. Hence the use of Private for module-level declarations.

3. Life's tricky if you store variable relating to the application in a Userform. It basically means you can never Unload the Userform for the duration of the programme. Far better would be to pass those values into some other storage method. Examples might be to pass them into a Module, to pass them into a Class, to write them into a hidden Worksheet, etc. An example of the module would be like so:

Module

    Option Explicit
    Private mUser As String
    Private mPass As String

    Public Sub SetLogin(user As String, pass As String)
        mUser = user
        mPass = pass
    End Sub

Userform

    SetLogin Me.txtUser.Value, Me.txtPass.Value