VBA code to display 'password' store in cells

I have two columns of data. The first contains 'names' and the second contains 'passwords'. The Userform is completed with information plus a combobox displaying 'names'. When the command button is clicked, the information is updated to a database and the 'name' selected in the combobox should display the matching 'password' on a message box.

The question is how do I code the combobox element of the userform to display on a message box, the 'password' matching the 'name' selected?

Hi mosesena

It seems like you have two distinct problems - the first is displaying the password for a name, and the second is something to do with updating the place where these items are stored. The first part is pretty easy to do, the second will, I suspect, require some more input from you.

Let's take a look at the first part.

We'll have a spreadsheet where the Names are in Column "A", and the Passwords are in Column "B". You want to show the Names in a combobox, and when the user clicks on (selects) a name, you want to show a message box with the password, right?

So, we need to load the combobox with two pieces of information. The first is the Name, and the second will be the password itself. However, we won't actually show the password in the combobox (although this is perfectly easy to do).

You'll need a user form and the combobox. In the properties for the combobox set the ColumnCount property to 2.
Then set the column widths to "144pt; 0pt". This will give you two columns, one of which is invisible.

Then you'll use the following code to load up the combobox:

Sub LoadCombobox()
Dim i As Long
Dim lc As Long

Dim a As String
Dim b As String

'   We assume that names start in row 2 (row 1 is the caption)
i = 2
a = "A" + Format(i)
b = "B" + Format(i)

While ActiveSheet.Range(a) > ""
      Me.cboName.AddItem ActiveSheet.Range(a)
      lc = Me.cboName.ListCount - 1
      Me.cboName.List(lc, 1) = ActiveSheet.Range(b)
      i = i + 1
      a = "A" + Format(i)
      b = "B" + Format(i)
Wend

End Sub

End Sub

This will put all the names into the visible first column of the combobox, and all the passwords into the invisible second column!

Then, you will add the following code for the Click event of the combobox. (I'm assuming you know how to do this. If not, let usknow, and we can explain in a little more detail)

Private Sub cboName_Click()

Dim lx As Long

lx = Me.cboName.ListIndex
MsgBox "Password for " & Me.cboName.List(lx, 0) & " is " & Me.cboName.List(lx, 1)

End Sub

Lastly, we need to load the combobox, and this is perhaps best done when the form is initialized:

Private Sub UserForm_Initialize()

LoadCombobox

End Sub

So, the final code is this:

Sub LoadCombobox()
Dim i As Long
Dim lc As Long

Dim a As String
Dim b As String

'   We assume that names start in row 2 (row 1 is the caption)
i = 2
a = "A" + Format(i)
b = "B" + Format(i)

While ActiveSheet.Range(a) > ""
      Me.cboName.AddItem ActiveSheet.Range(a)
      lc = Me.cboName.ListCount - 1
      Me.cboName.List(lc, 1) = ActiveSheet.Range(b)
      i = i + 1
      a = "A" + Format(i)
      b = "B" + Format(i)
Wend

End Sub

Private Sub cboName_Click()

Dim lx As Long

lx = Me.cboName.ListIndex
MsgBox "Password for " & Me.cboName.List(lx, 0) & " is " & Me.cboName.List(lx, 1)

End Sub

Private Sub UserForm_Initialize()

LoadCombobox

End Sub

Now, having said all this, it is probably easier simply to make both columns of the combo box visible (set the ColumnWidths property to simply "144pt".

And lastly, it's generally a pretty terrible idea to make passwords visible on a user form! You typically never want anyone other than the owner of a password to see what it is. To reset a password you clear it out, and then have other code somewhere that prevents the use of an empty password from being valid! However, that's another day, and another story!

Hope this helps!

Tony