Create a Masked Password Using VBA

In this lesson you will learn how to create a masked password using VBA. Masked passwords are essential for the security of the data of the user. Because if you enter a password to an unmasked textbox or inputbox then the password will be visible to others around you. But if you use a masked textbox or inputbox then Excel will hide the password with asterisks.

Here is a custom VBA form designed to get the password from a user.

But as the textbox is not masked, the password entered will be visible.

So now let’s look at how to mask a password in Excel VBA forms.

First, open the visual basic editor. You can use “Developer” tab or the keyboard shortcut Alt + F11

Can’t see the developer tab in your Excel application? Learn how to show the Developer tab

Next, select the textbox you want to apply the mask to.

Go to the properties window and find “PasswordChar” field

Now select the character you want to show when the user types password. The asterisk(*) is the typical symbol used for this.

All set with the form. Now asterisks will be displayed when you enter characters in the textbox.

Retrieve password from the textbox

Now you learnt how to hide passwords with asterisks in Excel VBA textboxes. But do you know how to retrieve the password entered by the user? You can use the below code to print the password in the immediate window. cmdOK is the name of the OK button used in the form.

Private Sub cmdOk_Click()

     Dim Pwd As String

     Pwd = frmPwd.TextBox1.Value
     Debug.Print Pwd

End Sub

Now when the user clicks the OK button after entering the password, the program will assign that to the variable Pwd of type string. Then you can use that variable for the next steps such as password validation or password matching.

Hide password with asterisks in an inputbox

You can use inputbox to get input from users. But is it possible to mask a password with asterisks in an inputbox? Unfortunately you can’t use the above method for an inputbox. But this page explains how to use a private InputBox to mask the password with asterisks.

Private InputBox to mask the password with asterisks

Get Row Number of a Matching Value

In this lesson you can learn how to find the row number of a matching value. This page explains about 3 ways to do this. You can adapt a solution according to your preference. These techniques will be very helpful when you develop applications like data entry systems. Because in these types of applications you may need to show the matching result to the user. For example you might want to let the user search with a text using a textbox or a inputbox.

I will use this sample worksheet to explain these three methods.

Suppose user search for the code “C00KLCMU14”. So the program should return the matching row number as 14.

Method 1

'Row number of matching value
Sub MatchingRowNumber()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim i As Long
Dim SearchedValue As String

Set WS = Worksheets("Sheet1")

SearchedValue = "C00KLCMU14"

For i = 1 To 10000
     If StrComp(WS.Range("B" & i).Value, SearchedValue, vbTextCompare) = 0 Then
          MatchingRow = i
          Exit For
     End If
Next i

MsgBox MatchingRow

End Sub

In the above macro the For Next loop is used assuming there are 10,000 rows of data. But if you have data up to x rows then you should replace For i = 1 to 10000 with For i = 1 to x

If no match is found, the message box will output 0 because zero is the default value of the Long data type.

However there is one problem with this solution. It is that if you have data in a higher number of rows in your Excel sheet then the program will take considerable time to output the result. But we can avoid this problem if we use arrays.

Method 2

Assume we have one million rows of data in our sheet. Here is how you can use an array to get the required result without delay.

Sub MatchingRowNumber_ArraySolution()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim i As Long
Dim SearchedValue As String
Dim DataArr() As Variant

Set WS = Worksheets("Sheet1")

DataArr = WS.Range("A1:E1000000")
SearchedValue = "C00KLCMU14"

For i = 1 To 1000000
     If StrComp(DataArr(i, 2), SearchedValue, vbTextCompare) = 0 Then
          MatchingRow = i
         Exit For
     End If
Next i

MsgBox MatchingRow

End Sub

This is an easy way to write a range to an array.

DataArr = WS.Range("A1:E1000000")

Things you need to remember when creating an array using the above method.

• This method always creates a multidimensional array.
• Indexes of both dimensions will start from 1.

Want to learn more about arrays? Check these lessons.

Multidimensional Arrays in VBA
Quick Multidimensional Array from Excel Range
Quickly Write Multidimensional Array to Excel Range

So far we have discussed two methods of finding the row number of a matching value. But sometimes cells can contain spaces before or after the values. So if there are spaces like that, then the program will not be able to find the matching value. To avoid this issue we can use the Trim function to remove unwanted spaces before or after the values.

This is how you can use the Trim function for the first method.

Replace

If StrComp(WS.Range("B" & i).Value, SearchedValue, vbTextCompare) = 0 Then

With

If StrComp(Trim(WS.Range("B" & i).Value), SearchedValue, vbTextCompare) = 0 Then

And here is how you can use the Trim function for the second method.

Replace

If StrComp(DataArr(i, 2), SearchedValue, vbTextCompare) = 0 Then

With

If StrComp(Trim(DataArr(i, 2)), SearchedValue, vbTextCompare) = 0 Then

Also if you get the SearchedValue from the user from a method such as input box or textbox then you can use the Trim function to remove unwanted spaces from SearchedValue as well.

If StrComp(Trim(DataArr(i, 2)), Trim(SearchedValue), vbTextCompare) = 0 Then

Method 3

Now let’s look at the third method to find the row number of a matching value. You can use the Find method to return the row number of the matching value.

Sub MatchingRowNumber_FindFunction()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim SearchedValue As String

Set WS = Worksheets("Sheet1")
SearchedValue = "C00KLCMU14"
MatchingRow = WS.Cells.Find(What:=SearchedValue, After:=Cells(1, 1), LookIn:=xlFormulas, _
     LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=False, SearchFormat:=False).Row

MsgBox MatchingRow
End Sub

This will output the correct row number if there is a match. However if there is no match then the program will throw an error.

To avoid this error, we can use the error handling technique as follows. So if there is no match, the program will output 0.

Sub MatchingRowNumber_FindFunction()

Dim WS As Worksheet
Dim MatchingRow As Long
Dim SearchedValue As String

Set WS = Worksheets("Sheet1")
SearchedValue = "C00KLCMU14"

On Error Resume Next
MatchingRow = WS.Cells.Find(What:=SearchedValue, After:=Cells(1, 1), LookIn:=xlFormulas, _
     LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
     MatchCase:=False, SearchFormat:=False).Row
If Err.Number <> 0 Then
     If Err.Number = 91 Then
         MatchingRow = 0
     Else
         MsgBox "Unknown Error"
         On Error GoTo 0
         Exit Sub
     End If
End If
On Error GoTo 0

MsgBox MatchingRow
End Sub

However this method will not work if cells contain extra spaces before or after the values.

Access Formula Bar Using Keyboard - Excel 2013

In the last post we discussed how to activate the edit mode for the active cell using a keyboard shortcut. From this post you can learn how to access the formula bar in Excel 2013 using the keyboard. Here are the steps to follow.

Click on the “File” menu.

Then click on the “Options”

Go to “Advanced” tab and remove tick from the “Allow editing directly in cells”

Now the cursor will jump to the formula bar when you press F2 on the keyboard.

Activate Edit Mode For The Active Cell - Keyboard Shortcut

In the last post we discussed how to copy paste data to an Excel sheet from a different source without formatting. I explained three methods to do that. One method was double clicking on the cell and paste. However you can also activate this edit mode using the keyboard as well.

Below is the shortcut key to activate the edit mode.

This is not only useful when copy-pasting data without formatting but also when editing formulas or content of the active cell. For example if you want to edit a formula or a text inside a selected cell, you can activate the edit mode and then use left and right arrow keys to move the cursor to where you want. When you press F2, Cursor will initially move to the end of the text, value or formula. But then you can use left and right arrow keys to move the cursor along. But if you press the left or right arrow without activating the edit mode, Excel will send you to the next cell at left or right respectively.

How to Copy Paste Data to an Excel Sheet From a Different Source (Without Formatting)

Sometimes we need to copy data from various sources to Excel sheets. For an example you may want to copy data from a webpage into an Excel sheet. If you ever did that, you should have noticed that when you paste data to Excel, data is pasted with the source formatting.

Let's see an example. This is the default formatting of an Excel sheet when I create a blank workbook.

But if I copy one of the post titles from this website (excelvbasolutions.com) to this sheet, then it will be pasted like this.

As you can see formatting is totally different from the current formatting of the sheet. Even if you have already applied different formatting to the Excel sheet than default, the same thing will happen.

So how do we paste content from different sources to an Excel sheet without the source formatting. There are three ways to do this. First method is you can paste the content on the formula bar.

Then data will be pasted without source formatting.

The second method is double clicking on the cell and pasting the content. If you double click on a cell a cursor will appear inside the cell like this.

Now you can press Ctrl+v from the keyboard or right click and select paste.

If you use one of the above two methods you can only paste data into one cell. But sometimes you may want to paste a large amount of data to different cells at once. For example you might want to copy paste a complete webpage to an Excel worksheet. You can use this third method to paste data to one cell or multiple cells.

Here are the steps of the third method. First copy the data from the source. Then select the cell you want to paste data from. Next, right click and select the “Match destination format” option under the “Paste options”.

If User Close The VBA Userform With x

Today I’m going to show you a solution for a challenge faced by lots of newbie developers. Here is the challenge. When we create VBA forms, we often create a Close or Cancel button to close the form.

So users can use that button to close the form. Also sometimes we add some code to that Close button to carry out some tasks while closing the form. Below is an example.

Private Sub cmdClose_Click()
     Unload Me
     Call UpdateReport
     ThisWorkbook.Save
End Sub

So this Close button calls a sub procedure called “UpdateReport” and then saves the file while closing the form.

Suppose there is a VBA application which has a dashboard or report. Then the dashboard or report should be updated when the new data entered through the form. But the program doesn't need to update the dashboard/report on every single entry. Instead the program can update the dashboard/report while closing the form. Because the user can view the dashboard/report only after he closes the form. If you develop the program to update them on every entry then the program will have to call the update sub procedure every time when a user enters a data set. This is the same for the file saving function. You don’t need to save the file whenever the user enters a data set. Instead you can save it when the user closes the form.

And sometimes developers create VBA applications in Excel in such a way that users can only interact with forms. Developers might use the worksheets to store the data. But users will be restricted to only interact with forms. In such programs developers use the close button to close the entire workbook.

For these reasons developers create separate buttons to close the form. But then the problem is that some users use the default close (x) button at the top of the VBA form.

If a user clicks that button, the form will be closed. But the tasks in the Close or Cancel button will not be completed. So how do we solve this problem? We can use the Terminate event of the form to solve this. What you should do is place the code (you want to run when closing the form) inside the Terminate event of the form. Then write “Unload Me” in the Close or Cancel button you created. Then when a user clicks the Close or Cancel button program will close the form. It will also trigger the terminate event of the form. Therefore code inside the Terminate event will also be executed. Here is an example.

Private Sub cmdClose_Click()
     Unload Me
End Sub

Private Sub UserForm_Terminate()
     Call UpdateReport
     ThisWorkbook.Save
End Sub

Get Selected Cell Using VBA Macro

In some VBA macros, it is needed to get the selected cell (active cell) automatically. This may be required when developing simple as well as advanced macros. In a simple macro you might need to let the user select a cell before running the macro. Then macro will perform different tasks depending on the cell selected by the user. Also if you are developing advanced macros such as Games in Excel, then you may need to identify the active cell to make the game functional.

So now let’s see how to get the selected cell using VBA. I have selected cell B3 on my Excel sheet.

We can easily get the address of the selected cell like this.

Sub GetSelectedCellAddress()

     MsgBox ActiveCell.Address

End Sub

Address of the Active cell will be shown in the message box if we run the above macro.

Also you can use the Split function to extract the column letter and the row number from the above result.

Learn how to use Split Function

However there is another way to get the row number of the active cell easily.

Sub GetSelectedCellRowNo()

     MsgBox ActiveCell.Row

End Sub

You can use a similar way to get the column. But it will return the column number rather than the letter.

Sub GetSelectedCellColumnNo()

     MsgBox ActiveCell.Column

End Sub

Also see

    Get Selected Rows Using VBA Macro
    How to find the name of an active chart using VBA
    Save a Workbook as a Single PDF Using VBA