Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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.

Designed custom userform

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

Unmasked textbox

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.

Select the password textbox

Go to the properties window and find “PasswordChar” field

Find PasswordChar field - properties window

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

Use asterisk as PasswordChar

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

Asterisks displayed instead of real characters

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.

Sample excel sheet with data

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

Program will show the row number of the matching value in a message box

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.

Program will throw an error if can’t find a match

Clicking debug will highlight the line where error occurred

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.

Click on the File menu

Then click on the “Options”

Click on the Options

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

Go to Advanced tab and untick Allow editing directly in cells

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

Cursor jumps to the formula bar

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.

Default formatting

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

Data pasted with the source formatting

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.

Click on the formula bar and paste the data

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.

Cursor will appear inside the cell

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”.

Right click and select Match destination format

Contact Form

Name

Email *

Message *