DatabasesinExcel

Create a Database Entry Form in Excel to Populate a Sheet Using VBA Macros

Excel isn’t a database software but as long as you don’t need to manage millions of data points or have complex, interlocking systems, it can potentially do the job for you — and save you plenty of money. You can create forms, that with the use of visual basic, can populate a database which you can track within Excel and then create reports on later.

Designing a user form and database sheet

Let’s suppose we want to track sales and commission data. A form that someone may fill out could include the following fields:

  • Sales Rep
  • Store
  • Date
  • Product
  • Sale Amount

Upon entering this data, a user could click on a button to post the data to another sheet within the workbook, thereby adding it to a database. And with more entries, the data can grow over size. We could then use the data to run reports on and to track store sales, calculate commission, and other metrics.

Here’s how I’ve designed this form in Excel:

A sales entry form in Excel.

The ‘Post Data’ button was created using a shape. And it will be attached to a macro, which will then post to the database worksheet.

Next, I’ll create the corresponding fields where these values will post to on a separate sheet, which I’ll call the database sheet. The layout is a simple one which just involves the same headers from the sales entry form.

A database sheet contain the fields from the data entry form.

Populating the database sheet using a macro

Next, I’ll create a macro to copy the data from the sales entry form to the next row in the database sheet.

The following macro will find the next row in the database sheet, and then copy the values from the sales entry form into the corresponding field. I’ve also added a message box at the end to let the user know that the data was posted successfully to the database sheet. My inputs in cells C4:C8.

Sub copyinputs()

Dim cl As Range
Dim nextrow As Double
Dim wsinput As Worksheet
Dim wsdatabase As Worksheet

'set the worksheet variables
Set wsinput = Worksheets("Input")
Set wsdatabase = Worksheets("Database")

'detect the next available row based on values in column A
nextrow = WorksheetFunction.CountA(wsdatabase.Range("A:A")) + 1

'copy values over
wsinput.Range("C4").Copy wsdatabase.Range("A" & nextrow) ' copy the sales rep
wsinput.Range("C5").Copy wsdatabase.Range("B" & nextrow) ' copy the store
wsinput.Range("C6").Copy wsdatabase.Range("C" & nextrow) ' copy the date value
wsinput.Range("C7").Copy wsdatabase.Range("D" & nextrow) ' copy the product
wsinput.Range("C8").Copy wsdatabase.Range("E" & nextrow) ' copy the sale amount

MsgBox "Posted!"

End Sub

This macro, called copyinputs still needs to be assigned to the button. To do this, I need to right-click on the button on the sales entry form and select Assign Macro and then select this macro. After doing this, I can start entering data and populating my database. When I finish filling in my data and I click on the Post Data button, I get a message saying it has been posted.

Entering data on the sales entry form in Excel.

And in my database sheet, it has begun populating the data:

Database sheet in Excel containing data from the input page.

As I enter data and click on the Post Data button, it continues adding to my database:

The database sheet growing in size.

Since it is always calculating the next row, it isn’t overwriting the existing information.

Additional macros to setup

This current macro is a simple one which just posts the data. It doesn’t do anything besides that. It doesn’t check for if the fields are entered nor does it clear them after they’ve been entered. You can adjust this, however, to make the macro and form more robust.

Here’s a macro which includes the existing code plus code to clear the values after they’ve been entered, assuming the values are in the range C4:C8.

Sub copyinputs()

Dim cl As Range
Dim nextrow As Double
Dim wsinput As Worksheet
Dim wsdatabase As Worksheet

'set the worksheet variables
Set wsinput = Worksheets("Input")
Set wsdatabase = Worksheets("Database")

'detect the next available row based on values in column A
nextrow = WorksheetFunction.CountA(wsdatabase.Range("A:A")) + 1

'copy values over
wsinput.Range("C4").Copy wsdatabase.Range("A" & nextrow) ' copy the sales rep
wsinput.Range("C5").Copy wsdatabase.Range("B" & nextrow) ' copy the store
wsinput.Range("C6").Copy wsdatabase.Range("C" & nextrow) ' copy the date value
wsinput.Range("C7").Copy wsdatabase.Range("D" & nextrow) ' copy the product
wsinput.Range("C8").Copy wsdatabase.Range("E" & nextrow) ' copy the sale amount

'clear the data
wsinput.Range("C4:C8").ClearContents

MsgBox "Posted!"


End Sub

Additionally, we can setup rules to make sure that the fields have to be entered fully before clicking on the Post Data button. This can prevent incomplete entries from being posted to the database sheet. Here’s how we can add a code to check if the sales rep field contains a value. This can be repeated for all other required fields:

Sub copyinputs()

Dim cl As Range
Dim nextrow As Double
Dim wsinput As Worksheet
Dim wsdatabase As Worksheet

'set the worksheet variables
Set wsinput = Worksheets("Input")
Set wsdatabase = Worksheets("Database")

'detect the next available row based on values in column A
nextrow = WorksheetFunction.CountA(wsdatabase.Range("A:A")) + 1

'check if the sales rep field contains a value
If wsinput.Range("C4") = "" Then
    MsgBox "Please enter a sales rep"
    Exit Sub
End If


'copy values over
wsinput.Range("C4").Copy wsdatabase.Range("A" & nextrow) ' copy the sales rep
wsinput.Range("C5").Copy wsdatabase.Range("B" & nextrow) ' copy the store
wsinput.Range("C6").Copy wsdatabase.Range("C" & nextrow) ' copy the date value
wsinput.Range("C7").Copy wsdatabase.Range("D" & nextrow) ' copy the product
wsinput.Range("C8").Copy wsdatabase.Range("E" & nextrow) ' copy the sale amount

'clear the data
wsinput.Range("C4:C8").ClearContents

MsgBox "Posted!"


End Sub


If you like this post on Create a Database Entry Form in Excel to Populate a Sheet Using VBA Macros please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

DateStampsExcel

Create a Date and Time Stamp in Excel Using VBA

Do you have a spreadsheet that needs to track dates? Whether it’s a shipping log, an inventory tracker, a sales order template, or just something to track when the last change was made to a cell, there’s an easy way you can create a date stamp in Excel with VBA.

Use checkboxes to make your spreadsheet more user friendly

If you have a spreadsheet where you want to track statuses, using checkboxes can be helpful. This way, someone can check or uncheck the status of an order. This can indicate whether it has been shipped, ordered, or completed. Excel has made it easier to insert checkboxes with a recent update. If you’re using Microsoft 365, then on the Insert tab on the Ribbon, you should see an option to insert a Checkbox:

Selecting to insert a checkbox into Excel.

When you click on this button, it will insert a checkbox right into the active cell that you’re on. Want to insert checkboxes into multiple cells at once? Simply select a range of cell and then click on the button:

Checkboxes in an Excel spreadsheet.

If a checkbox is checked, its value is TRUE. If it is unchecked, then the value is FALSE. This is important to know when creating formulas.

Populating the date using the NOW() function isn’t useful for date stamps

If you want to enter the current date into a cell, you can use the CTRL+; shortcut. The problem is that it won’t change if you go to uncheck and re-check a checkbox. It’s a stale value and it isn’t a formula.

What you may be tempted to use is the NOW() function. However, the limitation here is that anytime the cell recalculates, it will refresh with the current date and time. It won’t hold the existing date stamp. You can create a circular reference and adjust iterative calculations. But there’s an easier way you can create a date stamp in Excel with just a few lines of code using VBA.

Creating a custom function using VBA

You can create a custom function with VBA. To do, start by opening up your VBA editor using ALT+F11. On the Insert menu, select the option for Module. There, you’ll have an empty canvas to enter code on. The custom function can simply contain one argument — the cell that contains the checkbox. This is to determine whether it is checked (TRUE) or unchecked (FALSE). If it is checked, then the timestamp will be equal to the current date and time. If it’s unchecked, then the timestamp will be blank, and so will the cell value.

Here’s the full code for the function:


Function timestamp(checkbox As Boolean)

    If checkbox = True Then
        
        timestamp = Now()
    
    Else
    
        timestamp = ""
    
    End If
    

End Function

This function is now created. To use it within your spreadsheet, all you need to do is select a cell where you want the date and time to populate on. Then, assuming your checkbox is in cell A2, enter the following formula:

=timestamp(A2)

This will run through the VBA code to determine whether to populate the current date and time or not. Since there is no NOW() function present in this formula, it won’t recalculate with the current date.

Formatting your date and time

Even if the custom function work, you may notice that the value that it populates doesn’t look right. If you get a number or the time is missing from the date, then you’ll need to modify the cell format. To do that, select the cell and press CTRL+1. Then, select the Date category where you’ll see various date formats:

Formatting cells in Excel

If you scroll down the list, there will be an option that shows the date and time:

Selecting a date format in Excel.

If you use that format, then your date will now look correct, including both the date and time.


If you like this post on How to Create a Date and Time Stamp in Excel Using VBA, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

BankRecGeneric

Bank Reconciliation Template 2023: Use Auto-Matching Rules to Speed Up Your Recs!

Introducing the Ultimate Bank Reconciliation Excel Template – your time-saving companion for hassle-free financial management!

Are you tired of spending hours manually matching transactions and dealing with duplicates during your bank reconciliations? Look no further! This downloadable Excel file is here to revolutionize the way you handle your finances. Packed with powerful features and user-friendly functionalities, this template will streamline your reconciliation process like never before.

Key features

Automated Transaction Matching: Say goodbye to tedious manual matching. This Excel template is equipped with an intelligent algorithm that automatically matches transactions, making your reconciliation process a breeze. Experience unmatched efficiency as the template swiftly identifies and pairs up corresponding transactions, freeing up your valuable time.

Effortless Manual Matching: Whatever the template doesn’t end up matching, you can do so manually using the Reconciler. It’s a much easier process than the manual approach as once you select a transaction, it will find related transactions that you can match the transaction to. Simply review the related items displayed, and with a few clicks, you’ll have your transactions matched accurately and swiftly.

Using the bank reconciliation template to easily match transactions.

Duplicate Detection: This template also checks for duplicates and will be careful not to match any items where there is a duplicate entry. You can still match these transactions manually using the Reconciler, but you won’t have to worry about the template automatically matching items where there are duplicates. This helps ensure accuracy and integrity when doing the automatic matching.

Flexible Matching Rules: Take full control of your reconciliation process with customizable matching rules. The Excel template empowers you to create rules to automatically match transactions on a 1-to-1 basis, 1-to-many, or even many-to-many, tailored to your unique needs.

Matching rules in the bank reconciliation template.

By creating these rules, you can specify how a transaction should be classified.

In the above example, if a description contains CK#, then it will belong to the ‘Checks’ category. For the key, which is used to help match a transaction, it will take the next 4 numbers. So if you have CK#1234, then 1234 would be the key. For a transaction to automatically match, it will need to be part of the same category, have the the same key, and amount. In the case of 1-to-1 matches, it will only need to find another transaction that matches this criteria. The one exception is if there is a duplicate; in that case, the transactions won’t automatically match. The auto-match is designed to minimize false matches.

For the Wire Transfer example above, it will simply use the date as the key. Since it’s a 1-to-1 match, it will look for another Wire Transfer on the same date with the same amount.

The deposit example shown above is slightly different in that it relies on the date but it is a 1-to-Many match type. However, this can also work as a many-to-many matching type as well. That’s because it will look for all of the deposits on that date, across the book, bank, and previous outstanding items. Only if the total of all the deposits on that date are a match will the auto-match rules kick in and say that everything is a match. You might use this type of matching if you have multiple deposits on the GL side that total just a single deposit amount on the bank side, or vice versa.

Easy Overrides. If you need to force a match, you can do so by entering any value in the ‘Manual Override’ column. This will clear the O/S status and indicates that the transaction has been matched. In the example below, just entering an ‘X’ is enough to mark off a transaction as being reconciled:

Overriding a transaction on the bank reconciliation template.

Quickly Generate Reports and Start a New Month: With just a click of a button, this Excel template generates a comprehensive report summarizing outstanding items. Gain valuable insights into your financial status and easily identify discrepancies that require attention. Stay on top of your bank reconciliations with accurate, up-to-date information at your fingertips. Clicking on the Reconcile Month button will summarize your outstanding items. You can also clear all the data with the Clear Data button. Click the New Month button when you’re done reconciling and want to close out the month. It will transfer all your current outstanding items to the previous outstanding items tab.

Buttons on the bank reconciliation file.

Try the bank reconciliation template for yourself!

Whether you’re a small business owner, a financial professional, or an individual managing personal finances, this Bank Reconciliation template is your go-to solution. Experience unparalleled ease, accuracy, and efficiency in your reconciliation process. Save time, reduce errors, and take control of your financial management like never before.

Best of all, you can try it out for free to see how you like it. Download the trial version here. If you decide you want to buy the full version without restrictions and full VBA code available, check out the product page here.


If you like this Bank Reconciliation template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

VBAfolderpath

Use VBA Code So a User Can Select a File or Folder Path

If you need to reference other files or folders within an Excel sheet, trying to predict what the path will be can be challenging. The easier option is to just have the user select the file or folder from their computer, and then have the path get pasted into a cell. To do this, you will need to use visual basic (VBA) for Excel to be able to get this value for you. In this post, I’ll show you how you can do that.

Creating the VBA code to select a file or folder

There are a couple of variables that need to be setup for this code. One is for the file or folder selection, and the other for the actual path. In the below example, I’m going to use the folder picker option. I’ll also disallow multiple selections to ensure the path value can easily be pasted into Excel:

Dim folder As FileDialog

Dim path As String


Set folder = Application.FileDialog(msoFileDialogFolderPicker)

folder.AllowMultiSelect = False


If folder.Show = -1 Then

path = folder.SelectedItems(1)

End If


If path = "" Then Exit Sub


If Right(path, 1) <> "\" Then path = path & "\"

Range("mypath") = path

The folder.show = -1 line is simply to make sure that the user has clicked the button. And if they do, the macro will just pull in the path of the selected item (since multiple selections are disallowed). Towards, the end of the code, there is also a backslash that is added in case it isn’t included within the path. If you were dealing with a file selection, this wouldn’t be necessary.

The last line of code assigns the path to a named range in your spreadsheet called ‘mypath’. You will need to create this to ensure the path goes to the right cell.

The above code will work just as well if you need to select a single file. The only difference is rather than referencing the msoFileDialogFolderPicker, you would want to access the msoFileDialogFilePicker. You can also remove the line that adds the backslash. And you would probably want to change the name of the variable from folder to file.

Create a button to run the macro

One thing you’ll want to do when setting up this macro is to add a button. This way, the user can just click the location next to where the path will be go. To create a button in Excel, go to the Insert tab. Then, select the drop-down menu for Shapes, and then select a square or rectangle to create a button. Once you have created it, right-click and select Edit Text. Here, you can type in a description for the button, such as Select Folder. Next, you can right-click the button and select Assign Macro. Then you should see the following dialog box, where you can select the macro you just created:

Once you’ve assigned the macro, you will be able to just click it so that it runs.

Selecting multiple file and folder paths

If you want to have the user select multiple paths, the easiest solution is just to have multiple places where you can select files or folders. The good news is you don’t need to create multiple macros for this. Instead, you just need to modify the code so that it looks at which row the button you click on is in. This requires using the application caller. Instead of using a named range, I’ll refer to a set column and the row that the button is on. To adjust the macro, I need to add a variable for the selectedRow and assign its value as follows:

Dim selectedRow as integer

selectedRow = Activesheet.Shapes(Application.Caller).TopLeftCell.Row

Assuming that I want to put the path in column A, this will now be my last line of code:

Range("A" & selectedRow) = path

Here is the full code:

Sub selectfolder()

Dim folder As FileDialog
Dim path As String
Dim selectedRow As Integer

Set folder = Application.FileDialog(msoFileDialogFolderPicker)
folder.AllowMultiSelect = False

selectedRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row


If folder.Show = -1 Then
path = folder.SelectedItems(1)
End If

If path = "" Then Exit Sub

If Right(path, 1) <> "\" Then path = path & "\"

Range("A" & selectedRow) = path


End Sub

Since it looks at the selected row, you can just copy the button to multiple rows. You don’t need to create a different macro. However, since it looks at the top left cell, it’s important to keep the button within just a row. Don’t let it bleed into another row. Otherwise, you may populate the path into the wrong cell. Here’s how the buttons look in my example:

It doesn’t matter if the buttons span multiple columns. Using the code above, they simply shouldn’t be extending into another row. In the above example, I have just copied the button multiple times. Now, when a user click on them and makes a selection, it will paste the selected path into the corresponding cell in column A.


If you liked this post on How to Use VBA Code So a User Can Select a File or Folder Path, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

ForLoops

How to Create For Loops in VBA

You can add lots of automation to your spreadsheet by utilizing visual basic (VBA) and running macros. One of the more common macros you might run include loops. In particular, using for loops in VBA can allow you to cycle through a range of data, check for criteria, and then execute commands. Or, you can also use it to do calculations and to compute totals. There’s a lot of potential with running loops in VBA. In this post, I’ll show you how you can get started with them.

Looping through data in Excel to highlight blank or incomplete data

In the following data set, I have some missing values where there is an (*) in place of a value in column D:

I’m going to loop through the column that shows the change value and I will look for the (*) values.

To get started, launch VBA from the developer tab or hit ALT+F11. Then, insert a new module. I’m going to call this subprocedure ‘cleanup’. I’ll start with declaring variables for the worksheet, individual cell I’m looping through, an integer to track how far along I am in the range, and an integer for the last cell within the range.

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row




End Sub

In the above code, I’ve assigned the worksheet variable to the sheet that I’m on. And I set the lastcell variable to the last row in the sheet. This is the same as if you were press F5->Special->Last Cell. This makes it easy to determine where the data set ends, and gives me an endpoint for my loop. There are other types of loops you can use where you don’t need to specify an end. But this reduces the risk of you getting into a never-ending loop.

As for the loop itself, it will look like this:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Interior.Color = vbRed
            
        
        End If
        

Next i

How it works is it uses the i variable to start from 1 and go all the way until the lastcell variable. In this example, that relates to 92 (the last row in my data set).

Then, it assigns the cl variable to each cell in column D as I go through that range. In the first instance, the cl variable is D1, then D2, and so on, until you reach the end (D92).

Next, it evaluates if the value of that cell is (*). If it is, then it highlights the entire row in the color red. If I run this macro, this is what my sheet looks like afterwards:

Loop in VBA that highlights rows in red.

It did the job well, as you’ll notice everything that had a value of (*) in column D, the entire row ended up getting highlighted in red. There are other colors you can use and other things you could have done. Next, I’ll show you how you can just delete these rows entirely.

Deleting rows while looping through data in Excel

Removing rows seems simple enough, but it is a little tricky here. For instance, to remove the row rather than to just highlight it, that involves a simple line of code (in my example I’m referring to my cl variable):

cl.EntireRow.Delete

If I run the macro with that code, this is what happens to my worksheet:

Looping through and deleting rows in Excel.

I’ve left the red highlighting in place to show you that there are still many rows that should have been deleted and that weren’t. So did the macro simply not work?

The problem lies with how it was set up. In the current macro, I’m moving down from one row to the next. If I’m at row 10 and delete it, then the next row I move on to is row 11. However, the issue is that once I delete row 10, everything moves up a spot. So what was previously row 11 now becomes row 10, causing me to effectively skip over that row and miss it. Now my macro no longer evaluates it.

There are a couple of ways to fix this. One can be that if I delete the row, I can adjust my i variable so that it deducts one so as not to skip over the next row. Just by adding a line of code, you can adjust for that issue:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            i = i - 1
            
        
        End If
        

Next i

The i = i -1 line of code will reset the i variable back down a spot when a row is deleted. This will now prevent the macro from jumping over a row.

However, there’s another option you can use, and that’s looping through the data in the opposite direction.

Looping through the data backwards and utilizing the step keyword

In the first example, I looped through the data from row 1 to the last row. Here’s my full code for that:

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            
        
        End If
        

Next i



End Sub

I’m going to adjust this and go in reverse order. However, it’s not as simple as specifying start from the bottom number and go to row 1. You need to give VBA a bit more information. This is where you can use the Step keyword. By using that, you can specify in which direction you want the loop to go, and whether it should go 1 row at a time or jump multiple rows.

Here’s how the loop looks like if I want VBA to jump backwards one row at a time from the bottom:

For i = lastcell To 1 Step -1


    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            

        End If
        

Next i

If you want to jump by 5 rows, you would use Step -5. However, because I want to evaluate each row, -1 is what I’ll use in this example. By running this loop, now the highlighted rows are all deleted:

Looping through and deleting rows in Excel.

Since the loop is starting from the bottom and working its way up, it doesn’t matter that I’m deleting rows; it doesn’t impact the rows above and so no adjustment to the i variable is necessary.


If you like this post on How to Create For Loops in VBA, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EAnimateDashboards

Animate Your Dashboards in Excel With This Free Template

Dashboards in Excel can update when a user makes a selection on a slicer or refreshes data. You can even use macros to automatically update a chart or dashboard for you. In this post, I’ll share with you a template that I’ve created that will allow you to effectively play your dashboard, updating it from one period to the next, and showing the change in the chart over time. Here it is in action:

The template has three sections: one for pivot tables, one for the data, and one for the dashboard. You can set the file up however you want, the main area that needs to remain largely the same is the dashboard sheet. Every chart on this sheet only will automatically get updated. And for it to work properly, all the charts need to be linked to the one timeline chart in here (i.e. there cannot be more than one). For information on how to set up your timeline (or any other slicer for that matter) so that you can link it to multiple charts, you’ll need to learn about how to adjust Report Connections in this post.

Once you’ve got the charts you want to be connected to the timeline, then it’s a matter of just updating the settings section on the Dashboard tab. This is off to the left, with the values that you need to enter/update highlighted in yellow.

Settings section on the template.

These simply specify what date you want to start from, where you want to end at, and by which interval you want to jump (e.g. x days/months/years). Depending on the frequency you select, your dashboard can either play very quickly, or very slowly.

The last step is to just click the Animate Dashboard button at the end of the home tab:

Animated dashboard button.

Upon clicking this, the timeline will jump by the intervals you specified. No other changes will be made to any filters or slicers you have selected. The only changes will take place to the timeline, at which point, you should see something similar to the video posted at the top of this post.

You can download the free Animate Dashboard template for free, from here.


If you liked this free template that helps you animate your dashboards, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2Etogglebutton

How to Create a Toggle Button on the Excel Ribbon

In a previous post, I went over how to modify the Excel ribbon with some basic buttons. This time, I’m going to focus specifically on a toggle button that can change the value of a cell or trigger a calculation or macro. In this example, I’ll show you how you can create a toggle button on the ribbon that will change the color of the sheet, switching between having no fill and a white fill.

The first thing you’ll want to do is download the CustomUI Editor, which you should be able to find online through a search. Once you have that, you can get to work on coding the xml.

Creating the ribbon code

To get started, you can refer to this post on how to create a simple tab and button on the Excel ribbon. Once you have that basic framework set up in the CustomUI Editor, it’s just a matter of modifying the type of button you’ll use. In that post, I just inserted some generic code for a button:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab" label="Custom Tab">
				<group id="customGroup" label="Custom Group">
					<button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

The button in that example is just a basic button. To turn it into a toggle button, I just need to modify the syntax so rather than saying ‘button’ it says ‘toggleButton’. I still need a label, an image, and specify what happens when it is pressed (the ‘onAction’ argument). Here’s what my updated xml code looks like:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab" label="Custom Tab">
				<group id="customGroup" label="Custom Group">
					<toggleButton id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Changing button to toggleButton just allows the button to have a pressed state so that when you click on it, it can look sunken. You can use that variable to also dictate what action should be taken. The key to making the toggle button work is to attach some macros to it, which is what I’ll cover next.

Creating the callback macros

A callback macro tells Excel what should happen when the toggle button is clicked. Here’s what the macro should look like initially:

Sub Callback(control As IRibbonControl, pressed As Boolean)

End Sub

The pressed value is a boolean, so it will only either be true or false. The next part of the code involves setting up an if statement to check on that value.

    If pressed = True Then

        'code goes here if the value is true (pressed)

    Else
    
        'code goes here if the value is false (not pressed)


    End If

In this example, I’m going to make a simple macro where the sheet is either filled white, or there is no fill effect. The code isn’t complicated and I will start will selecting all the cells, selecting the interior property, and setting it to solid (i.e. ‘filled’):

Cells.Interior.Pattern = xlSolid

And to change it back so there is no fill effect:

Cells.Interior.Pattern = xlNone

Within the full callback macro, this is how all the code looks:

Sub Callback(control As IRibbonControl, pressed As Boolean)

    If pressed = True Then
    
        Cells.Interior.Pattern = xlSolid

    Else
    
        Cells.Interior.Pattern = xlNone


    End If


End Sub

Here is the toggle button in action:


If you liked this post on How to Create a Toggle Button on the Excel Ribbon, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EcustomUIEditor

How to Customize the Excel Ribbon Using the Custom UI Editor

In a previous post, I covered how you can customize the Excel ribbon in what was a pretty manual process. The good news is there is a much easier way to make changes to the ribbon using the Custom UI Editor. Below, I’ll cover how you can add and remove both tabs and groups, how to add buttons, and how to even use your own images. The first thing you’ll want to do is to download the Custom UI Editor. There’s not a definitive place you can always find this tool at so your best bet is to do a Google search.

How the Custom UI Editor works

Once you have downloaded the Custom UI Editor, you can get to work and begin to customize the Excel ribbon. Unlike just a simple customization, when you modify the ribbon using the Custom UI Editor, you are making changes to the Excel file itself. That means when you send the file to someone, they will see the changes you have made. With just a ribbon customization, those changes only apply to your computer. But these changes will be saved within the file. The Custom UI Editor goes through the cumbersome process of attaching the XML code to the Excel file and makes it a lot easier to make changes to the ribbon.

When you first open the file, you’ll see a blank canvas such as this:

Custom UI Editor showing a blank page.

Start with clicking on the Folder icon to load up the Excel file that you want to modify. Any Excel file will do. Next, go to the Insert menu and click on Office 2010 Custom UI Part:

Insert menu on the Custom UI Editor.

This will create the xml file for Office 2010 and newer versions of the ribbon. If you want to ensure these ribbon modifications also work on Office 2007, then you will want to also insert the file for Office 2007 Custom UI Part. If the file isn’t going to be used on an older version of Excel, this isn’t necessary, but it also doesn’t require much additional effort.

The Custom UI Editor includes some sample code within it that you can automatically load so that you don’t have to start from scratch. With one of the xml files selected, let’s go back to the Insert menu, and this time click on Sample XML and Custom Tab.

Inserting sample XML from the Custom UI Editor.

This will insert some xml to get you started:

The Custom Tab XML code is loaded into the Custom UI Editor.

What the code has done is created a tab called Custom Tab and within that, created a Custom Group. Lastly, it has also added a button called Custom Button, which is a large size and uses a HappyFace icon that is built-in within Excel. Here’s what that looks like in the actual Excel file:

Custom button using the Happy Face icon.

The one thing that’s left to do is to link the button to some VBA code so that it does something when you click on it. I’ll cover that towards the end of this post.

If you wanted to run this same xml code for the older version of Excel (2007), then everything would work the same except for the very first line. Instead of this:

<customUI xmlns=”http://schemas.microsoft.com/office/2009/07/customui”>

You would use this:

<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui”>

All you need to do is change 2009/07 to 2006/01.

To check that your code is correct, you can click on the red checkmark icon at the top:

Red checkmark button in the Custom UI Editor.

And if you get this message:

Custom UI message saying the XML is well formed.

Then your code is good to go and doesn’t contain any (obvious) errors.

Adding a custom button to the Home tab

Using the above example, you can customize the Excel ribbon to create a group and custom buttons inside of a new tab. However, I prefer simply adding any custom buttons on the Home tab to make them easy to find. Unless you have many buttons and macros, you probably don’t need to put them on an entirely separate tab of their own.

If you don’t want to create a new tab and just want to put your buttons in an existing tab, then you can use the following code to put them on the Home tab:

<tab idMso=”TabHome”>

When you use a reference of ‘Mso’ that means it is an existing Microsoft tab/group/image. You need to refer to the correct name (see further down for a list of groups and tabs) and then you can put your custom group or button in that tab rather than creating a new one. Here’s what the full code would look like by changing this one reference from the above example:

Custom UI Editor showing button group created within the Home tab.

In Excel, I still have my custom group, but now it isn’t on its own tab. Instead, it goes to the end of the tab:

Custom group showing at the end of the Home tab.

If it’s too far to the end what you can do is insert it before a certain group. Let’s say I want to put it just before the Alignment group. Then I just need to adjust the code slightly to add the insertBeforeMso (this is case-sensitive) attribute for the group tag:

<group id=”customGroup” insertBeforeMso=”GroupAlignmentExcel” label=”Custom Group”>

This is how the full code looks:

Custom UI Editor with custom group inserted before the Alignment group.

And now my custom tab shows up a lot earlier in the home tab:

Custom group and button showing on the home tab after the Font group.

Another thing I can do is also remove some groups. If I don’t want the font group, I can add the following line of code in the Custom UI Editor:

<group idMso=”GroupFont” visible=”false”/>

Here is the updated code:

Custom UI Editor that shows line removing the Font group.

And here’s what the ribbon looks like:

Excel ribbon without the Font group.

You could make all the Microsoft tabs and groups invisible if you wanted to and can control where you custom group goes. The key is knowing the correct names.

Names of the Microsoft tabs and groups

Here is a list of the Microsoft tabs and the reference you will want to use when modifying the ribbon:

Tab NameRibbonX Referenece
HomeTabHome
InsertTabInsert
DrawTabDrawInk
Page LayoutTabPageLayoutExcel
FormulasTabFormulas
DataTabData
ReviewTabReview
ViewTabView
DeveloperTabDeveloper

Here are the main groups from the Home tab:

Group NameRibbonX Reference
FontGroupFont
AlignmentGroupAlignmentExcel
NumberGroupNumber
StylesGroupStyles
CellsGroupCells
EditingGroupEditingExcel
ClipboardGroupClipboard
UndoGroupUndo

There are more groups (from other tabs) but for this purpose, I just included the most common ones.

Adding an image to a button

If you want to use an existing Microsoft image for your button, then you can view the imageMso gallery here. Once you find the image you want to use, just put that in place of the HappyFace image in the earlier code.

However, suppose you want to make a custom image. I’m going to create one using the Amazon logo to create a button that will open my browser to the Amazon.com website.

For starters, I need to get an image. For large ribbon buttons, you want to aim for a size of 32 x 32 and for smaller images, 16 x 16. As long as it’s a square image, however, you should be okay. Wide images will stretch and won’t look as good. This is the image I’m going to use:

Amazon logo.

I’m going to use just a simple code for creating the button, which looks as follows:

Custom UI Editor showing code for the Amazon button.

I haven’t associated an image to this button yet. To do that, I’m going to click on the xml file and go back to the Insert menu. This time, I’m going to select Icons. This will open up launch a dialog box where I can now select the image I want to use from my computer. Once I’ve selected it, it now shows up underneath my xml file:

Image file showing attached to the xml file.

I can right-click on the name ‘amazon’ to change the id to something else. Whatever if it is, that’s what I need to reference in my xml code. Since it’s not a Microsoft image, I just add the following attribute:

image=”amazon”

And here is my full code:

Custom UI Editor showing the code referencing the custom image.

If I open up Excel, this is what my custom button looks like:

Custom Amazon button on the ribbon.

But right now, my button doesn’t do anything. That leads us to the last section on how to customize the Excel ribbon: callbacks.

Setting up callback macros

A callback tells the button which code to run. So that means you need some VBA code to begin with, otherwise, the button isn’t going to do anything. I’m going to create a simple macro that will just open the Amazon.com website:

Sub Amazon()

ActiveWorkbook.FollowHyperlink (“https://www.amazon.com”)

End Sub

The callback function itself can be generated from the Custom UI Editor. If I click on the icon next to the checkmark that looks like a block of code:

Callback button on the Custom UI Editor.

The application will produce the VBA code I need to put into my Excel file:

Callback generated from the Custom UI Editor.

I’m going to copy and paste that back into VBA. However, I need to add a line in between as that code only sets up the macro, it doesn’t do anything yet. I need to reference the macro I created earlier. The full callback macro looks as follows:

‘Callback for btnAmazon onAction
Sub Callback(control As IRibbonControl)

Amazon

End Sub

Now when the button is pressed, the ‘Amazon’ macro will run, which opens the Amazon.com website. You can create a custom button for each macro you want to run and assign an image to each one. All you need to do is to use the callback macro to link the button to the code you want to run.


If you liked this post on How to Customize the Excel Ribbon Using the Custom UI Editor, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

time-2676366_1280

Excel Automation: 10 Tasks You Can Automate Today!

Spreadsheets can allow you to analyze data and create reports efficiently. But sometimes the tasks that are involved can be difficult or appear to be time-consuming. The good news is that there’s a lot of automation you can achieve in Excel, and it isn’t always necessary to know how to code in order to do so. Below, I’ll show you 10 types of tasks that you can automate in Excel either on your own or with our help.

1. Cleaning and parsing data

One of the more challenging things in Excel is when you’re dealing with a dataset that may not be easy to manipulate. For instance, if you’ve got text mixed in with numbers or dates that aren’t in the right format, Excel may not interpret or recognize the data properly. But there are many formulas that can help you with that. Rather than manually fixing the data, you can use functions like TRIM, CLEAN, LEFT, MID, and RIGHT to extract what you need while also getting rid of extra, unnecessary spaces and other characters.

If you’re looking for more of a walkthrough of the process, there’s a detailed explanation in this post of how to parse data.

Through the use of formulas, you can save hours that you might otherwise spend trying to clean up your spreadsheet. And the best part is that once you’ve set it up, you can re-use the formulas as you add more data. You don’t need to use macros or complicated coding to clean up; a well-structured template can be enough to do the job for you.

2. Creating simple reports

One of the best features of using Excel is that once you’ve entered data into a spreadsheet, it’s even easier to create a report from it. One example is through the use of a pivot table, where through just a few clicks you can easily summarize your data and split it along different categories. Slicers can make filtering and summarizing data even easier in pivot tables, especially for users who aren’t very familiar with Excel. Forget any manual work here; just a few clicks and you’ve got a report that can quickly summarize information in a table for you!

Alternatively, you can also insert charts easily and Excel will try and select the best one based on your data set. There’s also lots of formatting you can apply to charts so that they have the look and feel that you’re after. And once you’ve got a look that works, you can re-use it over and over again.

3. Creating dynamic dashboards

Dashboards are incredibly popular but they can be complex to set up. Then there’s also the challenge of updating it and making sure the data is up-to-date. It can easily take you hours every time to make sure the information is accurate.

However, in this post, I show you how to create a dynamic dashboard that not only won’t take you hours but that will automatically update as you add data to it. And then, you end up with a report that looks great to send to management to easily review and update.

4. Routine data entry

One of the biggest headaches people can face when using spreadsheets is when they hard-code calculations. A hard-coded calculation is where you don’t reference any cells and just put the result in the cell; it can make it nearly impossible to decipher how that number was calculated (especially if you’re not the person who entered the value). If you go to re-calculate it or update it, you could spend a lot of time just trying to figure out the calculation.

However, by using a formula, there’s no ambiguity as to how a value was calculated. Not only does that save you the time of entering in data but it also makes it easy to correct and update the figure. Ideally, you should minimize the number of places you’re manually entering data into. By doing that, you’ll have a much more robust template where your inputs are kept to a minimum which will eliminate the need for a lot of data entry and your other calculated fields will update automatically. This type of automation doesn’t require complex coding and just needs an Excel spreadsheet to be carefully constructed so that it is efficient and makes the most of formulas.

5. Conditional formatting

Oftentimes you’ll want to color-code your data to highlight things you should be paying attention to. If you’ve got an aged accounts receivable schedule, it is useful to highlight which accounts are more than 90 days overdue. You could manually filter the data and highlight all the cells or rows in red that are overdue, but you can just use conditional formatting to do that for you.

Through conditional formatting, you can create rules to determine when a cell or row should be highlighted in red, when you may want it to be in yellow, or when you may just want to hide the text so that you can easily skip over it. For example, hiding zero values can make it easy to focus on more important numbers.

You can apply many different formatting rules and can even put in a hierarchy to determine if you want to keep applying formatting rules or whether you want to stop if a specific criteria is met. Conditional formatting can be complex but it can be a huge time-saver by allowing you to focus on just the items that are important to you. And once you’ve set up the rules, you don’t need to worry about making changes every time you add new data.

Check out this post to learn about conditional formatting and how you can apply it to your spreadsheet.

6. Updating other workbooks and sheets

If you use multiple workbooks, then another area where you can avoid re-entering data is by linking both workbooks. There are numerous ways that you can do this. One approach involves just linking directly to another worksheet where data will automatically pull from another table.

You can also use the INDIRECT function to reference another worksheet or workbook. Just like with a template, once you set up these formulas and connections, they are there to stay and you can avoid having to manually make changes by yourself.

7. Audit tracking by logging changes

One of the neat features of many Office products is they allow you to track changes that are made. This is normally when you share a workbook with other users. However, through the use of macros, you can have a separate sheet that can tell you which values were changed, when, and by who.

Rather than manually noting these changes or relying on people to make the updates themselves, it doesn’t take much effort through a macro to create a log of what’s been changed.

8. Generating PDFs

One thing many advanced Excel users like to do is to use automation to export reports into PDFs. While there is a way to print to PDF, and it’s particularly easy on the newer versions of Excel, it can be a time-consuming process especially when you need to print out multiple sheets. Here again, with a simple macro, you can auto-generate PDFs and save them in a predefined folder all with the click of a button.

9. Sending emails

Another feature many users like is the ability to use automation to send out emails right from Excel. Through the use of macros, this is also possible. You can create a macro that will enter in the email of the recipient, attach a file, enter the body of the message, and even send the email itself. This can be even set up on a large scale, such as sending out invoices to dozens or hundreds of customers, a process that could easily save you hours worth of work.

10. Just about anything else with VBA

The power of programming in Excel can unlock many different possibilities with what you can automate. Whether it’s using automation to help import data and then manipulating it, creating custom reports, or following a series of complicated steps, there are many tasks in Excel that can be expedited with a few clicks of a button. As long as there’s some logic to the process that you can break into steps, then you can also build that into the code and automate it.

Don’t know where to start? Contact us!

There is significant potential in Excel but not everyone knows how to use automation to make the most of it and to make a spreadsheet as efficient as it can be. You can contact us if you have a certain Excel issue that you need help with or if one of the tasks above has perked your interest and you’d like to learn more. We can help create solutions for you that work efficiently and that can save you many hours, perhaps even days every month.


If you liked this post on 10 Tasks You Can Automate Today, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

convertsummary

Convert a Summary Table in Excel Into a Pivot Table

Often times, when you download a data table from somewhere it’s not in the format you need it to be. Tables are often in a summary format where you have months going down and years going across, or vice versa. It’s the end result of what you want a pivot table to look like, but you can’t easily turn that into a pivot table itself. Below, I’ll show you how to turn a summary table in Excel that looks like this:

Summary table.

Into this:

Data in tabular format in Excel.

This format is much more Excel-friendly and one that you can easily convert into a pivot table.

Converting the table

The data I’m using is the same one that I used in an earlier post that went over transposing data. Transposing data, unfortunately, isn’t enough to make data workable if you want to convert it into a pivot table. You’ll want data to be in a tabular format so that there’s a header for the month, year, and value.

You could manually transpose one year at a time and copy the data one by one. But of course, that isn’t optimal at all. The good news is I’ve got a macro that can help you flip that data in one click. It will go through the painstaking process of reorganizing the data for you.

Here’s the code for the macro. You can just put it into a module (I’ll leave a template to download below if you aren’t comfortable doing this step yourself):

Sub flipdata()

Dim cl, nxtcl As Range
Dim lastcol, lastrow, firstcol, firstrow As Integer


'get total number of rows and columns in range
lastcol = Selection.End(xlToRight).Column
lastrow = Selection.End(xlDown).Row

'get first column and row
firstcol = Selection.Column
firstrow = Selection.Row

'assign output starting point
Set nxtcl = Cells(lastrow + 2, firstcol)

nxtcl = "Header 1"
nxtcl.Offset(0, 1) = "Header 2"
nxtcl.Offset(0, 2) = "Value"

Set nxtcl = nxtcl.Offset(1, 0)


'cycle through data

For yr = (firstrow + 1) To lastrow

    For mth = (firstcol + 1) To lastcol

        nxtcl = Cells(firstrow, mth)
        nxtcl.Offset(0, 1) = Cells(yr, firstcol)
        nxtcl.Offset(0, 2) = Cells(yr, mth)
        Set nxtcl = nxtcl.Offset(1, 0)

    Next mth

Next yr

End Sub

It will output the data a couple of rows below where your data ends. It’s important to select the entire range of data before running the macro since it will go through the range that you’ve selected, nothing else. And if there’s data below your selection, it will overwrite that.

After you’ve selected the data, then you run the macro. In my template, I’ve got a button that you can press that will do the job for you and then you’ll get something that looks like this:

Data in tabular form.

Once in this format, you can easily create a pivot table:

Pivot table.

If you’d like to download the file that contains the macro, it’s available here.


If you liked this post on how to convert a summary table in Excel into a pivot table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.