Blog Archives

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell

Here’s how you can link a cell’s value to be displayed inside a drawing object. In this example, a Text Box is being used, but this method will also work for Rectangles and other draw-able Forms shapes.

Read more ›

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

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: Deselecting All OptionButtons and CheckBoxes

Tom’s Tutorials For Excel: Deselecting All Embedded OptionButtons and CheckBoxes

If you have embedded OptionButtons or CheckBoxes onto your worksheet from the ActiveX Toolbox or the Forms toolbar, here are two macros for examples of how to deselect those controls. Notice how simple the Forms controls are to manipulate…no looping needed,

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: Creating a Right Click Event For ActiveX Controls

Tom’s Tutorials For Excel: Creating a Right Click Event For ActiveX Controls

Free and safe downloadable workbook example.

There is no built-in menu for the right-click event of ActiveX objects. Here is a utility for that, using a CommandButton as an example.

Read more ›

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

Tom’s Tutorials For Excel: Hyperlinking a CommandButton to the Internet

Tom’s Tutorials For Excel: Hyperlinking a CommandButton to the Internet

When you have an ActiveX CommandButton on a UserForm or embedded onto your worksheet, the FollowHyperlink method can take you to your favorite website.

In the above picture, the green CommandButton’s caption is the literal URL address.

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: 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: 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: , , , , , , , , , , , , , ,