Contextures Blog

Last week, we saw how to move items from one listbox to another on an Excel worksheet.

Now I’ve added a page on the Contextures website, with similar instructions for listboxes on an Excel UserForm.

Thanks to Dave Peterson, who provided both sets of sample code.

ListBoxes in Excel UserForm

In the screen shot below, there is an Excel UserForm with two ListBoxes, and four command buttons, down the centre.

• The buttons with 2 arrows move ALL items from one list to the other, in the direction that the arrows are pointing
• The buttons with 1 arrow move the SELECTED items from one list to the other, in the direction that the arrow is pointing

Download the Move Listbox Items Sample File

To see the code, and test the ListBox move items code for a UserForm, you can download the ListBox UserForm Move Items sample workbook.

The file is in Excel 2007 format, and is zipped. It contains macros, so enable them if you want to test the code.

Related Excel VBA Tutorials

If you’re looking for a fun and exciting way to fill your holiday Monday, here are links to a few other UserForm and Excel VBA tutorials on my Contextures website:

FAQs, Excel VBA, Excel Macros

Create an Excel UserForm Video

UserForm with ComboBoxes

Excel VBA Edit Your Recorded Macro

Excel VBA Getting Started

______________________

To select specific items from a long list, you can create 2 ListBoxes on a worksheet.

Then, use arrow buttons to move all the items from one list to the other, or move selected items only.

ListBoxes on Worksheet

In the screen shot below, there are 2 ListBoxes, and 4 buttons with arrows, on a worksheet named CreateRpt.

• In the ListBox at the left, you can select one or more items that you’d like in a report.
• Then, click the 2nd button, with one arrow that point to the right.
• The selected items will move to the ListBox on the right.

OR

• You could click the. top button, to move all the items to the right-hand ListBox.

Items List on Worksheet

When you get started, the list at the left is filled with items. Those items come from a named range, on a worksheet named Admin.

You could modify that list, to include the items that you need in your ListBox.

Use the ListBoxes

In the sample file that you can download, there is a Menu worksheet, with a navigation button, “Select Report Items”.

Click that button to go to the CreateRpt worksheet, where the ListBoxes are located.

Macro Runs Automatically

When the CreateRpt sheet is activated, an event procedure (macro) runs automatically.

The VBA code in that event procedure does the following steps:

• Clears both ListBoxes
• Adds all items from worksheet list, in ListBox at the left

Back to Menu Sheet

On the CreateRpt sheet, there is another navigation button, “Menu”, at the top left of the sheet.

Click that button, and a macro runs, to take you back to the Menu sheet.

Navigation Button Macros

The two navigation buttons run simple macros, and the VBA code is shown below.

First, here is the code that the Menu button runs:

Sub GoMenu()
Worksheets("Menu").Activate
End Sub

Next, here is the code that the Select Report Items button runs:

Sub GoRpt()
Worksheets("CreateRpt").Activate
End Sub

Read the Detailed Instructions

Thanks to Dave Peterson, who sent me the instructions and sample code for moving listbox items.

To see the details on how this technique works, visit the Excel VBA Move Listbox Items page on the Contextures website.

The tutorial shows, step by step,  how to set up the worksheets, add the ListBoxes, and create the named ranges.

It also shows all of Dave’s sample code, for moving the items between the ListBoxes.

Download the Move Listbox Items Sample File

To see the code, and test the Listbox move items code, you can download the Listbox Move Items sample workbook.

The file is in Excel 2007 format, and is zipped. It contains macros, so enable them if you want to test the code.

Video: Move Listbox Items on Worksheet

To see the steps to create the Listbox example, you can watch this Excel video tutorial.

______________

When you’re working with Excel VBA, you might want find the last row with data, so you can paste new data in the row below that.

The following code works up from the last row on the worksheet, until it hits a cell with data. It’s like using the End key and Up arrow, to manually move from the bottom of the worksheet.

Sub GetLastRow()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ActiveSheet
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lRow
End Sub

In the sample sheet, the last data in column A is in row 10, and that’s the result when running the code.

Unexpected Result

This week, I was using similar code in a client’s workbook, to find the last row of data. The data was in a named Excel table, and the last couple of rows in the table were blank. I wanted to find the last row with data, so I could change the table definition, to end at that last row.

When I used the same Excel VBA code, the last row wasn’t what I expected – it found the last row in the named table, even though that row was empty.

I’m sure that information is helpful in some situations, but it sure wasn’t going to help me resize the table!

My Last Row Workaround

There might be a more sophisticated solution to this problem, but I added a line of code to resize the table, so it ends at row 2.

Then, the code found the correct last row of data, and resize the table to end at that row.

Sub ResizeTheTable()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ActiveSheet
ws.ListObjects(1).Resize ws.Range("$A$1:$H$2")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
ws.ListObjects(1).Resize ws.Range("$A$1:$H$" & lRow)
MsgBox lRow
End Sub

That fixed the problem, and the table resized correctly.

A Better Solution?

Have you run into this problem with named Excel tables? How did you solve it?

Video: Create an Excel Named Table

When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps

____________