Category: Excel VBA

On the Contextures YouTube channel, someone asked if Excel can automatically create a sheet when the file opens:

• “this question from my friend, she always need to open same file and create a new sheet with the name of the month when her is updating the data. is there any ways to skip the above process?”

Video Answer

Since the question came from YouTube, I provided the answer in a video, which you can see at the end of this blog post.

If you prefer to see the code, or download a sample file, you can find the details below.

How the Add Sheet Code Works

The goal is to add a worksheet with the month name, when the Excel file opens. We only want that to happen at the start of the month, if the sheet doesn’t exist already.

We’ll write a macro that:

• figures out what the current month is
• checks for a sheet with that name
• adds that sheet, if it doesn’t exist

The Add Worksheet Code

Insert a regular module in the workbook, and paste in the following code. I used yyyy-mm as the sheet name format, but you could use a different format.

For example, to see the full month name, use mmmm as the format.

Sub AddMonthWkst()
Dim ws As Worksheet
Dim strName As String
Dim bCheck As Boolean
On Error Resume Next
strName = Format(Date, "yyyy_mm")
bCheck = Len(Sheets(strName).Name) > 0
If bCheck = False Then
  Set ws = Worksheets.Add(Before:=Sheets(1))
  ws.Name = strName
End If
End Sub

Run the Code Automatically

To make the code run automatically, when the workbook opens, you’ll create a Workbook_Open event.

Paste the following code on the ThisWorksheet module:

Private Sub Workbook_Open()
    AddMonthWkst
End Sub

Download the Sample File

To see the workbook and the Add Worksheet code, you can download the Add Worksheet sample file.

The file is in Excel 2007 format, and zipped. It contains macros, so you’ll need to enable them, to see the code working.

Watch the Add Worksheet Automatically Video

To see the steps for creating the code, and making it run automatically, you can watch this Excel Video Tutorial.

_____________

A couple of years ago, I posted about an Excel bingo workbook, that you could use to create a set of three cards with random numbers.

Bingo Card Formulas

In the Excel workbook, the bingo card sheet uses the INDEX and MATCH functions to pull the numbers from another sheet.

• =INDEX(Numbers!$M$1:$M$15, MATCH(LARGE(
  Numbers!$N$1:$N$15, ROW()-1), Numbers!$N$1:$N$15,0))

There were a few follow up workbooks, including an Excel Bingo Number Selector file from Dick Kusleika.

Horizontal Number Lists

This week, Carrie posted a comment on that article, and she wanted to adapt the bingo cards so they could be printed with Adobe InDesign. Instead of a square with 25 numbers, that program needs the 25 numbers in a single row.

Using the example in the screen shot above, the numbers in the first two rows would be arranged like this, followed by the numbers from the remaining rows:

Random Number Code

Jim Cone pitched in, and wrote some code to generate the random numbers in single rows, but it created some duplicates in the rows. Carrie didn’t want an uprising in the bingo hall, so we sent Jim away to try again. 😉

It didn’t take long for him to return with some code that worked correctly, so Carrie, and her Hummel figurine collecting friends, can safely play bingo this summer.

Whew! A bit later, Dick posted an update for Jim’s code. It’s shorter, and does the job very well.

Copy the Random Number Code

Thanks Jim and Dick! I’m sure Carrie appreciates the code, and maybe it will help a few other people.

To use this random number code, copy it to a regular code module in your workbook.

Then, go to a blank sheet, and run the SurelyYouCantBeSerious_R1 code.

Sub SurelyYouCantBeSerious_R1()
'Generates 800 sets of random Bingo numbers with no duplicates.
'Each row contains an individual set of Bingo numbers.
'Designed to be used with the "Adobe InDesign" application.
'Jim Cone - Portland, Oregon USA - February 02, 2011
'[email protected] - remove all "X"
'Edited by Dick dailydoseofexcel.com
On Error GoTo DontCallMeShirley
Dim arrList(1 To 800, 1 To 25) As Long
Dim j As Long
Dim R As Long
'B COLUMNS
'1 to 15 in the B column
Randomize
For R = 1 To 800
  For j = 1 To 5
    FillList arrList, j, R
  Next j
Next R
Range("A1:Y800").Value = arrList()
  Exit Sub
DontCallMeShirley:
  Beep
  Resume Next
End Sub
'==========
Sub FillList(ByRef arrList As Variant, lStart As Long, R As Long)
Dim j As Long
Dim C As Long
Dim N As Long
Dim arrCheck(1 To 75) As Long
j = 1
For C = lStart To 25 Step 5
  Do While j < 6
    'Int((High - Low + 1) * Rnd + Low)
    N = Int(Rnd * 15 + ((lStart - 1) * 15 + 1))
    If arrCheck(N) < 1 Then
      arrList(R, C) = N
      arrCheck(N) = N
      j = j + 1
      Exit Do
    End If
  Loop
Next C
End Sub

______________

In some workbooks, you want users to select one or more from a list of options.

Select Items in a List Box

To make it easier for people to enter data, you can add a List Box, with check boxes, to an Excel worksheet.

List Box on Data Entry Sheet

You wouldn’t want to have too many List Boxes on a worksheet, but it would be handy in a data entry form, like this one.

• Tip: In worksheets where you need several cells with multiple options, you could use my technique for selecting multiple items from a data validation drop down.

Set Up a List Box

On the Contextures website, there are instructions for setting up a List Box, and a sample workbook.

You can enter records, one at a time, on the data entry sheet, and run the macro to store the saved records another sheet.

Store Selected Items

The List Box macro code pulls the selected items from the List Box, and stores them in separate cells.

Watch the List Box Video

To see the steps for creating a List Box on a worksheet, you can watch this short Excel video tutorial.

____________

People are lazy! Shocking, I know, but who wants to click twice in Excel, if you can do the same thing by only clicking once?

Click to Sort Column

Peterson, champion of weary Excel users, created this sample Excel VBA sort code, that adds invisible rectangles at the top of each column in a table.

A macro is automatically assigned to each rectangle, and it sorts the table by that column, when you click it.

Benefits of Sort Macros

Here are two benefits of using Dave’s code:

1. Reduced wear and tear on clicking fingers
2. Less risk of table scrambling, because it ensures the entire table is selected before sorting

Edit the Setup Macro

There are two macros in Dave’s sample file.

• SetupOneTime – run this once, to add the hidden rectangles
• SortTable – sorts table by selected column, when heading is clicked

Before you run the SetupOneTime macro, you should edit both macros, to adjust them for your workbook

1. On the Excel Ribbon, click the Developer tab, then click Macros
2. Click SetupOneTime, and click Edit

The Setup Macro Code

In the SetupOneTime macro, change the iCol variable to match the number of columns in your table. If your table doesn’t start in cell A1, change that reference.

Edit the SortTable Macro

Next, change the variables in the SortTable macro, to suit your table settings. You can adjust:

• TopRow (row where headings are located)
• iCol (number of columns in the table)
• strCol (column to check for last row)

If you want to see the rectangle outlines, change the Line.Visible setting to True.

Run the SetupOneTime Macro

After you’ve edited the macros, you can run the setup macro:

1. Select the sheet where your table is located.
2. On the Excel Ribbon, click the Developer tab, then click Macros
3. Click SetupOneTime, and click Run

Now, click a heading in the table, to sort by that column.

Excel 2007 Shapes Problem

When I was getting this blog post ready, I discovered that Dave’s original code needed a tweak before it would work correctly in Excel 2007 and Excel 2010.

In the original code, written for Excel 2003, there was one line of code that made the rectangular shape invisible:

.Fill.Visible = False

In the newer versions of Excel, only the borders of the invisible shapes were clickable, so I had to change the code to these two lines:

.Fill.Solid
.Fill.Transparency = 1#

The revised code worked for me in Excel 2003, 2007 and 2010, creating transparent shapes that were clickable.

Download the Sample Workbook

To see the full code for the SetupOneTime and SortTable macros, and download the sample workbook, visit the Sort Data With Excel Macros page on the Contextures website.

And for more Excel advanced sorting tips, go to the Excel Add-in Advanced Sorting page on my Contextures site.

Watch the Click Headings to Sort Columns Video

To see the steps for editing the code, adding the rectangles, and clicking the hidden shapes, you can watch this short Excel tutorial video.

_________________

Did you ever get an Excel file from someone else, and try to sort out their Excel VBA code? Or, even worse, open an Excel file that you wrote long ago, and try to remember what all those variables mean?

I spend lots of time staring at Excel code, but apparently I don’t use the right-click menu too often, because I hadn’t noticed a couple of handy commands until recently.

These commands can help you decipher that mysterious code, and unravel the complicated sections.

Excel VBA Quick Info

The first handy command is Quick Info. I have Auto Quick Info turned on in the VBE Editor options, and it helps me remember the syntax as I type the code.

Right-Click for Quick Info

What I didn’t realize was that you can right-click on a variable, function, statement, method, or procedure in the code, and click Quick Info.

A tooltip appears, with details on the selected item.

Excel VBA Definition

The other right-click command that I finally discovered is the Definition command.

Click the Definition command, and it takes you to the selected variable’s definition.

Finding the definition is easy in most procedures, but in a long procedure, with a long list of variables, the Definition command really makes the job easier.

It’s especially helpful if the variable is defined on a different code module!

___________

Happy Canadian Thanksgiving! You probably have your own spreadsheet to organize the meal, but you can download my Excel Holiday Dinner Planner, if you don’t have one of your own.

Continue reading “Excel Conditional Data Validation”