Blog Archives

Tom’s Tutorials For Excel: Populating TextBoxes From Last Row Upwards

Tom’s Tutorials For Excel: Populating TextBoxes From Last Row Upwards

When you have a list and you want to populate userform TextBoxes for, say, the last 10 records of the items in that list, here’s how.

In the above picture,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Tom’s Tutorials For Excel: Copying Multiple ListBox Selections to Worksheet

Here’s how to transfer multiple selected items from an ActiveX ListBox onto your spreadsheet without intervening empty cells.

Private Sub cmdConfirm_Click()
‘Turn off ScreenUpdating.
Application.ScreenUpdating = False

‘Declare variables for row and ListBox item index.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Populating and Sorting Unique Items in a ListBox or ComboBox

Tom’s Tutorials For Excel: Populating and Sorting Unique Items in a ListBox or ComboBox

There are times when you need to populate a ListBox or ComboBox with unique items from a dynamic range containing many repeated items. You’ll also want the items to be sorted automatically,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Filtering Columns

Tom’s Tutorials For Excel: Filtering Columns
Free and safe downloadable workbook to filter by columns

As you know, Excel only provides a built-in method of filtering for rows by individual columns.

If you want to filter columns by their row headers,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Showing And AutoClosing a UserForm

Tom’s Tutorials For Excel: Showing And AutoClosing a UserForm

Sometimes you might want to only show a UserForm for a brief time, say 5 seconds. For example, you might want to show a quick message that’s custom-formatted beyond what a MessageBox can do, or you want the effect of a splash screen to advertise your work when the workbook opens.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Populating a ComboBox For a Range of Years

Tom’s Tutorials For Excel: Populating a ComboBox For a Range of Years

This populates a UserForm ComboBox with years that start 10 years ago and end 10 years from now. The idea is to reasonably control the available year which a user can select. At each passing new year the code will adjust the relative list.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Maximizing Your UserForm to Full Screen Size

Tom’s Tutorials For Excel: Maximizing Your UserForm to Full Screen Size

To fill the screen with your UserForm while maintaining the relative position of the form’s controls, stick this Initialize event code into the UserForm’s module:

Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With
End Sub

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window

Tom’s Tutorials For Excel: Specifying UserForm Position in Excel’s Application Window

You can automatically position your UserForm almost anywhere on your Excel application’s window, with the following Initialize event code that goes into the UserForm’s module.

Example 1, upper left

Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Dim Top As Double,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only

Tom’s Tutorials For Excel: Validate TextBoxes for Numbers Only

Here’s an example of controlling what a user enters into a TextBox on your userform, in this case, allowing only five whole numbers to be entered.

Suppose you are collecting mailing addresses for your customers,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: ListBox Days & ComboBox Months

Tom’s Tutorials For Excel: ListBox Days & ComboBox Months

In your userform’s module, this Initialize event code populates a ListBox with days of the week, and a ComboBox with months of the year, as seen by example in the picture.

Private Sub UserForm_Initialize()

‘Populate a ListBox with days of the week.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,