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

How to Copy Range Into a New Workbook Using VBA

In this lesson you can learn how to copy a range from an existing workbook to a new workbook. Existing workbook should stay open when we run the program. And we are going to paste the range to a new workbook. So the program will create a workbook automatically before paste. If you want to copy a range from a closed workbook to a new workbook then first you need to open the closed workbook using VBA.

Macros included in this lesson

1. Macro to copy range with formatting
2. Macro to copy range with original widths
3. Macro to copy range as values (two macros using two different approaches)

So let’s consider this sample workbook. Suppose the name of the workbook is “Data File.xlsx” and the name of the sheet is “Sheet1”. So we have the data in range B5:E12

You can copy ranges from any Excel file format such as .xlsx, .xls, .xlsm etc. But you can save code only inside the .xlsm files. Or else add the macro to the personal macro workbook.

How To Record Macros In Personal Macro Workbook

Now what we are going to do is create a new workbook and copy paste this data into the range starting from cell A1.

First create two variables to hold the workbooks.

Dim WB_SourceFile As Workbook
Dim WB_New As Workbook

Then assign the Data File.xlsx worksheet to the variable WB_SourceFile

Set WB_SourceFile = Workbooks("Data File.xlsx")

Copy range B5:E12 from sheet1 of the Data File.xlsx

WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy

Create a new workbook and assign it to the variable WB_New

Set WB_New = Workbooks.Add

Select cell A1 of the activesheet of the newly created workbook.

WB_New.ActiveSheet.Range("A1").Select

Now you can paste the data.

ActiveSheet.Paste

So this is the final sub procedure.

Sub CopyPasteRange()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     ActiveSheet.Paste

End Sub

Here is the result of the above macro.

As you can see the width of columns of the new workbook differ from the original file. However we can use the PasteSpecial method to paste the values with original widths. Use the below macro if you want to paste the data with original column widths.

Sub CopyPasteRange_OriginalWidth()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
          SkipBlanks:=False, Transpose:=False
     ActiveSheet.Paste

End Sub

In above examples range was copy pasted with formatting like colors, outlines etc. But you can also copy paste only values from the original workbook to the new workbook. This is how you can do it.

Sub CopyPasteRange_PasteValues()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     WB_SourceFile.Worksheets("Sheet1").Range("B5:E12").Copy
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False

End Sub

This is the result you will get.

Sometimes you may want to copy only a few values from an existing workbook to a new workbook. Here is an example.

Suppose this is the Sheet2 of the Data File.xlsx file. If we want to copy paste these two rates in the cell A1 and A2 of a new workbook, then we can do it easily using the Range.Value property as follows.

Sub CopyPasteValues()

     Dim WB_SourceFile As Workbook
     Dim WB_New As Workbook

     Set WB_SourceFile = Workbooks("Data File.xlsx")
     Set WB_New = Workbooks.Add
     WB_New.ActiveSheet.Range("A1").Value = WB_SourceFile.Worksheets("Sheet2").Range("C4").Value
     WB_New.ActiveSheet.Range("A2").Value = WB_SourceFile.Worksheets("Sheet2").Range("C5").Value
End Sub

Detect VBA InputBox Cancel - Two Solutions

We often use InputBox to get inputs from users. But do you know how to detect if a user cancels an InputBox. If you don’t detect it then it may cause some errors. Because if you have lines of codes after the InputBox, and if you don’t detect the cancel and handle it accordingly, then the program will execute the lines of codes after the InputBox method. And this can cause various errors. So you should always detect InputBox cancel and handle it accordingly when you use this method.

So let’s look at how we can detect when a user cancels an InputBox. Let’s consider this sample subroutine.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     Debug.Print "Hello " & Response & "!"

End Sub

If we run this, the program will ask to enter the username.

Then when we click “OK”, the program will print this in the immediate window.

However if we click the “Cancel” button, the program still prints in the immediate window.

But if the user clicks the “Cancel” button, the program should not print anything in the immediate window. So how do we modify the subroutine to do that. When a user clicks the Cancel button, the InputBox method returns an empty string. So we can use that returned value to detect the cancellation action.

Sub AskUserName()

     Dim Response As String

     Response = InputBox("Enter the user name:", "Username")

     If Len(Response) = 0 Then
          Exit Sub
     Else
          Debug.Print "Hello " & Response & "!"
     End If

End Sub

So in the above code first we check the length of the string. If it is 0 then we exit the subroutine using the Exit statement. However there is a small problem in this method. If a user clicks the OK button without entering anything then the program still sees it as a cancellation. Because the InputBox method returns an empty string as the user didn’t enter anything. So if you want to act differently, for the user clicks cancel than to when the user clicks ok without entering anything, then you can’t use the above method. If you want to treat the user in different ways for those two actions, then you should use your own userform and handle the cancellation as desired. This second method explains how to do that.

First go to the VBA editor and insert a userform.

Then add label, textbox and two command buttons to the form and format them to your preference.

Also see
How to add labels to VBA Userforms
How to Add Textboxes to VBA Userforms
How to Format Label Text in VBA Forms (To Give Professional Look)

In this method you have more freedom. You can apply any colors you want and also add additional buttons if needed. Next, insert a module.

Then add this code to the module.

Sub Main()

     UserForm1.Show

End Sub

When you run the “Main” Sub procedure, it will show the form. We can add the following code to the OK button of the form.

Private Sub cmdOK_Click()

     Debug.Print "Hello " & txtUsername.Value & "!"

End Sub

So this will print the username when you click the OK button. Finally we can add below code to the cancel button.

Private Sub cmdCancel_Click()

     Unload Me

End Sub

Now the form will close when the user clicks the cancel button.

Lock Macro Execution (Using Password)

In the last post, we learnt how to lock a VBA project with a password. So if a user wants to see the code then he/she has to input the correct password. But we realized, although the codes are locked, users can run the macros from the developer tab. But there may be times you want to also lock the macros from being executed as well. Then users will need to provide the password before running the macro. So in this post you can learn how to lock the macro execution using a password.

Let’s consider this simple macro

Sub WriteNumbers()

     Dim i As Integer

     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i

End Sub

What this macro does is it writes the numbers from 1 to 10000 in column A of the Sheet1. Now let’s look at how we can modify the above macro to protect it from being executed. First we need to declare an additional variable.

Dim Res As String
Dim i As Integer

Then we can use an InputBox as follows.

Res = InputBox("Please enter the password!", "Password")

Now the program will ask for the password when the user tries to run the macro.

Once the user enters the password, the program needs to verify whether it is correct or not. We can use an IF Statement for this. Also we can use the StrComp function to compare the user input with the password. Assume our password is “atyo45#4Yt”. Then we can develop the validating section as follows.

If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
     For i = 1 To 10000
          Worksheets("Sheet1").Range("A" & i).Value = i
     Next i
Else
     MsgBox "Sorry! Incorrect password"
     Exit Sub
End If

StrComp function compare the user input and the password (atyo45#4Yt). Then if the user input and password are matching then “If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0” becomes true. So the program goes to the For Next loop and writes the numbers. If the user input and password are not matching then the program shows the below message.

This is the completed code.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However there is one small problem in this macro. Typically, if a user clicks the cancel button of the input box or closes the input box, the program should not do anything. It should stop the execution without even showing any message. But in this macro,if a user cancels the Input box or closes it, still the program shows the password incorrect message. This is because when the user cancels or closes the input box it returns an empty string. So to avoid this what we can do is we can first check whether the variable “Res” holds an empty string. If it holds an empty string program will not perform any other actions. Here is the improved macro.

Sub WriteNumbers()

     Dim Res As String
     Dim i As Integer

     Res = InputBox("Please enter the password!", "Password")

     If Res = "" Then
          'Do nothing
     ElseIf StrComp(Res, "atyo45#4Yt", vbTextCompare) = 0 Then
          For i = 1 To 10000
               Worksheets("Sheet1").Range("A" & i).Value = i
          Next i
     Else
          MsgBox "Sorry! Incorrect password"
          Exit Sub
     End If

End Sub

However note that, when a user clicks the “OK” button without entering password (i.e. When the user input an empty string as password) the program doesn’t show the password incorrect message. Instead the program behaves similar to when a user clicks the cancel button.

How to Lock a VBA Macro

So far we developed various macros according to numerous requirements. So today I’m going to show you how to lock them with passwords. Because once you developed a macro you may want to lock it as it is your intellectual property. Also you may want to lock the project to protect it from being wrecked by other users.

So now let’s see how we can lock the project. Follow these easy steps. First open the Excel file containing the macro. Then click somewhere in the worksheet. Next press Alt + F11 on your keyboard. This will open the VBA editor.

Alternatively, you can go to the “Developer” tab and click on “Visual Basic” to open the VBA editor as well.

Note that the “Developer” tab is not displayed by default. This post explains how to add it to the ribbon.

How to show the Developer tab

As you can see in the first image, I have a very simple macro in the module1. To lock the project first click on the “Tools” menu.

Then select “VBAProject Properties...”

“Project Properties” dialog box will open. Go to the “Protection” tab and check the “Lock project for viewing”. Then enter the password in both textboxes and click OK.

Save the file, close it and reopen. Now when you go to the VBA editor and try to expand the project from project explorer it will ask you the password to unlock.

So if a user doesn't have a password then he/she can't view the code. But they can still run the macros from the developer tab. Thus anyone can run the macro without messing it up.

However if you want you can also protect the macro execution with a password too.

Lock Macro Execution (Using Password)

Get the Day Name of a Date Independent of Regional Settings

In the last post we developed a macro to get the weekday name of a date. But then we had to use the format of the dates according to the regional settings. So in this lesson I will show you how to develop a more advanced solution, to get the day name of a date independent of regional settings. For that I will use two more functions called “Split” and “DateSerial” in addition to the “Format" function. Let’s consider this sample excel sheet. As specified in the top row of column A, dates should be added in the given format (ex - mm/dd/yyyy). However you can easily change the code to suit with the format you need.

Dates have been added to column A in "mm/dd/yyyy" format. Now our macro should write the day name in front of each date.

So let’s start the macro by declaring the variables.

Dim WS As Worksheet
Dim i As Long
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As Integer
Dim ColA_Text As String
Dim CurrentDate As Date
Dim DayName As String

Assign the activesheet to the WS variable.

Set WS = ActiveSheet

We have dates in multiple rows. Therefore we need a For Next statement to iterate through each row. As we have dates from row 2 to row 10 we can use the For Next statement as follows.

For i = 2 To 10

Next i

When looping through each row assign the date of column A to ColA_Text variable.

ColA_Text = WS.Range("A" & i).Text

Now this variable holds the date as string. So we can extract day, month and year separately using the split function.

iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)

Want to learn about split function. Read this post.

Split Function

Now we have day, month and year in 3 separate variables. So we can use the DateSerial function to get the date correctly independent on the region user lives.

CurrentDate = DateSerial(iYear, iMonth, iDay)

Next, use the “Format” function to get the day name like we did in the previous lesson.

DayName = Format(CurrentDate, "dddd")

Finally we can write the day name to column B of the worksheet.

WS.Range("B" & i).Value = DayName

Below is the completed code.

Sub GetDayName()

     Dim WS As Worksheet
     Dim i As Long
     Dim iYear As Integer
     Dim iMonth As Integer
     Dim iDay As Integer
     Dim ColA_Text As String
     Dim CurrentDate As Date
     Dim DayName As String

     Set WS = ActiveSheet
     For i = 2 To 10
          ColA_Text = WS.Range("A" & i).Text
          iDay = Split(ColA_Text, "/")(1)
          iMonth = Split(ColA_Text, "/")(0)
          iYear = Split(ColA_Text, "/")(2)
          CurrentDate = DateSerial(iYear, iMonth, iDay)
          DayName = Format(CurrentDate, "dddd")
          WS.Range("B" & i).Value = DayName
     Next i

End Sub

And this is the output of the macro.

In the above example we used “mm/dd/yyyy” as the date format. But as I said you can easily change the code to suit other formats. Because you need to change only these three lines to suit with the format.

iDay = Split(ColA_Text, "/")(1)
iMonth = Split(ColA_Text, "/")(0)
iYear = Split(ColA_Text, "/")(2)

For example if dates are written in “dd/mm/yyyy” format you can modify the above three lines as follows.

iDay = Split(ColA_Text, "/")(0)
iMonth = Split(ColA_Text, "/")(1)
iYear = Split(ColA_Text, "/")(2)

Get the Day Name of a Date Using VBA

In this lesson you can learn how to get the weekday name of a date. Here is an example worksheet. Column A of this sheet has a list of dates. We are going to get the day name of each date into column B using a macro.

First let’s give our macro a name. I will name the macro as “GetDayName”.

Sub GetDayName()

End Sub

We need a few variables for this job.

Dim WS As Worksheet
Dim i As Long
Dim CurrentDate As Date
Dim DayName As String

Next I will assign Activesheet to the WS variable.

Set WS = ActiveSheet

We have dates from row 2 to row 10. So we need a For Next statement to iterate through each row.

For i = 2 To 10

Next i

Then when the program loops through each row we can assign the date to CurrentDate variable like this.

CurrentDate = WS.Range("A" & i).Value

Next, the day name can be obtained using Format function.

DayName = Format(CurrentDate, "dddd")

Finally we can write the result to column B

WS.Range("B" & i).Value = DayName

Below is the completed macro.

Sub GetDayName()

     Dim WS As Worksheet
     Dim i As Long
     Dim CurrentDate As Date
     Dim DayName As String

     Set WS = ActiveSheet
     For i = 2 To 10
          CurrentDate = WS.Range("A" & i).Value
          DayName = Format(CurrentDate, "dddd")
          WS.Range("B" & i).Value = DayName
     Next i

End Sub

And this is the result produced by the macro.

In this example I entered the dates in dd/mm/yyyy format. Also I have used the same in regional settings. But if I send this file to a user whose regional date format is “mm/dd/yyyy” then this program will output incorrect results. So if you want to use this macro, then you should format the dates in column A similar to your regional settings.

Calculate Duration Between Two Times of Two Adjacent Days

In the last post we learnt how to calculate the duration between two times of the same day. Today let’s look at how to calculate duration between two times of two adjacent days. Let’s consider this sample data. This is a start and end time of work for some employees.

If you look at the start and end times of the work, you will notice that some employees have ended the work on the same day as started and others have ended the work on the next day. So if we use the code of our previous post it will only give correct results for cases where employees started and ended the work on the same day. Now let’s look at how to develop a solution which suits both of these types.

As usual, first we need to declare a few variables.

Dim WS As Worksheet
Dim StartTime As Date
Dim EndTime As Date
Dim i As Integer
Dim DurationInHours As Double

Let’s assume we have the data in the sheet called “Data”. Then we can assign that sheet to the WS variable as follows.

Set WS = Worksheets("Data")

Next we need a For Next statement to iterate through each row. As we have employee names from 2nd row to 10th row we can write the For Next statement like this.

For i = 2 To 10

Next i

Now when the program loops through each row we can assign values for StartTime and EndTime variables.

StartTime = WS.Range("B" & i).Value
EndTime = WS.Range("C" & i).Value

Then duration can be calculated using the DateDiff function as follows.

DurationInHours = DateDiff("n", StartTime, EndTime) / 60

But now the problem is that the DateDiff function calculates the time difference assuming both times belong to the same day. If the end time is on the next day, then the program returns a negative value. Therefore we can use the sign of the returned value to identify whether end time is on the same day or not. And once we detect an end time which is on the next day, we can correct the returned value easily by adding 24 to it. Here is the reason for adding exactly 24. Even when the end time is on the next day, DateDiff calculates the value assuming the end time is on the morning of the same day. So we can easily fix it by finding the duration in the opposite direction by adding 24 to the returned result.

Also if ending time is on the same day, then we can use the value returned from the DateDiff function as it is.

If DurationInHours < 0 Then
     DurationInHours = DurationInHours + 24
End If
WS.Range("D" & i).Value = DurationInHours

You can see that we have altered the DurationInHours variable only if it is negative. So here is the full code.

Sub CalculateHoursWorked()

     Dim WS As Worksheet
     Dim StartTime As Date
     Dim EndTime As Date
     Dim i As Integer
     Dim DurationInHours As Double
     Set WS = Worksheets("Data")
     For i = 2 To 10
          StartTime = WS.Range("B" & i).Value
          EndTime = WS.Range("C" & i).Value
          DurationInHours = DateDiff("n", StartTime, EndTime) / 60
          If DurationInHours < 0 Then
               DurationInHours = DurationInHours + 24
          End If
          WS.Range("D" & i).Value = DurationInHours
     Next i

End Sub

Now the program calculates the duration correctly for both types.

Calculate Time Difference Using VBA

Sometimes we need to calculate time differences in VBA applications. It is often required when we develop solutions related to employment. Here is an example.

This sheet contains names of employees and the start and end time of their work. Then we need to calculate the hours worked for each employee. So in this lesson you can learn how to calculate the duration of two times using a macro.

First we need to declare a few variables.

Dim WS As Worksheet
Dim StartTime As Date
Dim EndTime As Date
Dim i As Integer

If the name of the sheet is Sheet1, we can assign that sheet to the WS variable like this.

Set WS = Worksheets("Sheet1")

In this example we have only nine names. We can use a For Next loop to loop through each row.

For i = 2 To 10

Next i

Now in each iteration assign the start and end time to variables StartTime and EndTime respectively.

StartTime = WS.Range("B" & i).Value
EndTime = WS.Range("C" & i).Value

Once the start and end times are assigned, use the VBA function called “DateDiff” to calculate the time difference.

DateDiff("n", StartTime, EndTime)

This function has five arguments. But the last two are optional and not needed for this type of calculation. First argument is the interval. We can give the interval as year, month, day, hour, minute etc. In here we used “n” and it refers to the minutes. So we will get the duration in minutes. Then we can divide the result by 60 to get the values in hours. So the final code should look like this.

WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60

Below is the complete macro.

Sub CalculateHoursWorked()

     Dim WS As Worksheet
     Dim StartTime As Date
     Dim EndTime As Date
     Dim i As Integer
     Set WS = Worksheets("Sheet1")
     For i = 2 To 10
          StartTime = WS.Range("B" & i).Value
          EndTime = WS.Range("C" & i).Value
          WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60
     Next i

End Sub

This is the result of the above code.

You may think why I didn’t use the hour as interval to directly calculate the number of hours like below.

WS.Range("D" & i).Value = DateDiff("h", StartTime, EndTime)

Instead of

WS.Range("D" & i).Value = DateDiff("n", StartTime, EndTime) / 60

But then the problem is DateDiff function returns only the whole number part disregarding the decimal part. So if we use “h” as the period then we will get below result which is incorrect.

And here is the next challenge. What if some employees start work in the evening or night and end it in the next day. Then our program generates incorrect results like this.

So in my next post I will show you how to calculate the time difference of two adjacent days like that.