- Outlook 2016 Mac Property Is Not Valid For This Object Type In Word
- Outlook 2016 Mac Property Is Not Valid For This Object Type Code
- Outlook 2016 Mac Property Is Not Valid For This Object Type In Spanish
- Outlook 2016 Mac Property Is Not Valid For This Object Type List
It is not necessary that only tasks in Excel can be recorded in a macro. Your macro process can extend to other Office applications, and any other applications that support Visual Basic Application (VBA). For example, you can record a macro where you first update a table in Excel and then open Outlook to email the table to an email address. Then, a New Profile dialog box will appear. Enter a descriptive type of a name for the new profile under the Profile Name and click OK. Next, in the E-mail Accounts dialog box, choose Add a new e-mail account in the e-mail options, and after that click on Next to continue. Now, click on the type of server on which the e-mail account works, and then click Next to proceed further.
Table of Contents
- Introduction
- Change ListBox Selection Type
- Return Position of Selected Item in ListBox
- Return Name of Selected Item in ListBox
- Changing which ListBox Items are Selected
- Assigning a Macro to your ListBox
- Determine which ListBox called a Macro
- Enabling/Disabling ListBoxes
- Adding/Deleting a ListBox
- Add Items to your ListBox list
- Link Range to a ListBox
- Remove items from your ListBox
- Edit an Item in your ListBox
- Selecting a ListBox
- Linking ListBox Selection to a Cell
- Enable 3D Shading with VBA
- Prevent ListBox from Printing
- Control ListBox Object Positioning
- Looping Through All ListBoxes
Introduction
This VBA Guide will teach you everything you could possibly want to know about handling Excel Form Control Listboxes using VBA. When I say 'Form Control' ListBox, I'm talking about this little guy located under 'Form Controls' on the Developer Tab > Insert menu:
Form Control ListBox
If you want to read more complete guides to Form Control or ActiveX Controls, visit my ActiveX and Form Control Guides page.
Before I get criticized, yes, I know the formal term is the two-word phrase List Box but I'm partial to referring to it like you would when writing a VBA macro: ListBox. You'll find I use them interchangeably throughout this guide.
I made a calendar form using Excel Form Control ListBoxes, which I'll reference throughout this guide as we learn how to manipulate our listboxes with VBA macros.
Excel ListBox Form
In the form control world, a listbox contains all the items in your list with an up and down scroll bar on the right side. If it's a dropdown list your after, you need to try a combobox.
In this tutorial, I'll show you how to select items, enable multiple selections, return the value of your listbox, link your listbox to a range, assign a macro to your listbox and loop through all the listboxes in your form - all using VBA!
Jump to Table of Contents
Change ListBox Selection Type
How you control the selected values in a listbox using VBA depends largely on what you've chosen for your Selection Type. That's why we're starting here, first. The selection type can be changed by right clicking your ListBox, selecting Format Control and clicking the Control tab. Your choices are:
- Single - allows only one selection (xlNone).
- Multi - allows multiple selections (xlSimple).
- Extend - allows multiple selections and allows you to select a range of items in the list by shift-clicking (xlExtended).
Selection Type
Let's change the selection type of our listboxes using VBA. Remember, the listbox name can be found in the upper left of Excel when you have your listbox selected:
List Box 1
Each of the three methods below can be used to change the MultiSelect property to any of the three acceptable values (xlNone, xlSimple and xlExtended).
Method 1
Method 2
Method 3
Jump to Table of Contents
Return Position of Selected Item in ListBox
How you return the value of a selected item in a listbox depends on what you've chosen for your Selection Type.
Just like with Form Control Checkboxes, there are many ways to run the same command with Excel Form Control ListBoxes. I'll present three ways. The following macros return the index position of the selected item in a listbox named 'List Box 1' and stores the value in the variable lbValue.
Return Position with Single Selection
First, we'll assume your Selection Type is set to single, so only one item can be selected at a time:
Method 1
Method 2
Method 3
Make powerful macros with our free VBA Developer's Kit
There's a lot to unpack here. To save time and become really good at VBA, make sure you get our free VBA Developer's Kit below. It's full of shortcuts, tips and pre-built macros to make writing VBA easier.
Instead of using the Value property of the ListBoxes, you could also use the ListIndex property. The ListIndex and the Value properties both behave the same way in the above examples.
Either way, if April was selected in List Box 1, lbValue would return an integer index position of 4, since the 4th item in the listbox was selected. The first item in your listbox is given an index value of 1 and the Nth item is given an index of N, where N is a positive integer.
If no items are selected, the .Value and .ListIndex properties both return a value of 0.
Return Positions with Multiple Selections
What if your Selection Type is set to multi or extend? The best way to return all the selected items is to loop through each list item in your listbox. The ListCount property returns the total number of items in your listbox.
Method 1
Method 2
ListBox Selected Position
Notice the ControlFormat method we used for single selection types doesn't work for multi or extended selection types. Just like with checkboxes and option buttons, the ControlFormat method has limitations.
The .Selected approach works for any Selection Type, but the .Value approach only works for the Single Selection Type. It's like the old adage a square is a rectangle, but a rectangle is not a square. When in doubt, use a loop with the .Selected approach.
Jump to Table of Contents
Return Name of Selected Item in ListBox
Returning the integer relative position of the selected item is nice, but what if you wanted the actual text of the item selected? To determine the text string of the selected items using VBA, you would wrap the integer index position in a List property:
Return Name with Single Selection
https://iceboras1984.mystrikingly.com/blog/rapidweaver-8-1-7-0. If only one item is selected, you can use these methods:
Method 1
Method 2
Method 3
Again, you could also replace .Value with .ListIndex in the above macro examples.
Return Names with Multiple Selections
Acid pro 7 crack download. If multiple items are selected, you'll need to loop through each item to find out which items in the list are selected:
Method 1
Method 2
If April is selected, a message box with the string 'April' will appear instead of just the integer '4.'
ListBox Selected Item
Jump to Table of Contents
Changing which ListBox Items are Selected
If you want to change which items in your Form Control ListBox are selected, try one of the following:
Method 1 - Single Selection
Method 2 - Single Selection
Both of these VBA macros change the selected item to 'March' in our calendar form.
ListBox Change Selected Item
If you were to add a line below with a .Selected = 2, the 3rd item would become deselected and only the 2nd item would be selected. This is true whether or not you have multiple selections enabled. So, how would you select multiple items with VBA? Let's take a look.
Method 1 - Multiple Selections
Method 2 - Multiple Selections
ListBox Select Multiple Items
As you probably guessed, setting an item to False in the multiple selection examples would deselect the item in our Excel listbox. These macros are great for setting a default value for your listbox.
Jump to Table of Contents
Assigning a Macro to your ListBox
The following VBA macros demonstrate how to assign a macro to a ListBox. When an item in the ListBox is clicked, the macro you assign will trigger and execute. This is the same as right clicking your ListBox and selecting Assign Macro.
These examples assign the macro DisplayMessage in Module1 to the ListBox titled ListBox 1 by using the .OnAction property:
Method 1
Method 2
Like Form Control Checkboxes, the ControlFormat method we demonstrated earlier will not work to assign a macro to a ListBox.
To unassign a macro to your ListBox, set the OnAction property equal to an empty string:
Unassign Macro
Jump to Table of Contents
Determine which ListBox called a Macro
If you have multiple ListBoxes linked to the same macro (i.e., more than one ListBox running the same macro when an item is selected or deselected), you can find out which listbox called the macro with Application.Caller.
In the Assigning a Macro example, we set our ListBox to run Module1.DisplayMessage. I made this macro to return the name of whatever ListBox called it.
When you click an item inside the List Box 1 listbox in your Excel spreadsheet, this macro executes and a message box pops up with the name of the listbox.
List Box 1
This is useful if you have one macro that you want to run down different branches depending on which list you're handling.
In case you were curious, the name that displays via Application.Caller is the .Name property of the list box, as demonstrated by the following macro:
Jump to Table of Contents
Enabling/Disabling your ListBox
Excel Form Control ListBoxes don't gray out when you disable them, but disabling them does prevent the user from clicking new selections. There's no way to tell your ListBoxes are disabled by looking at them - at least not in Excel 2010.
There aren't very many reasons to do thos, but I said this is a complete guide so here you go. Here are three ways to disable an Excel Form Control ListBox using VBA:
Method 1
Method 2
Method 3
Note that this does not disable the macro associated with your ListBox if you've already assigned one. All it does is prevent the user from making new selections. Your macro will still run when a user attemps to select a new item in your list, even though the item will not become selected.
To enable your listbox again, just set the .Enabled property back to True using whichever method you prefer. Here's an example:
Jump to Table of Contents
Adding/Deleting a ListBox
To add a Form Control List Box, you need to know where you want to place the list box and what size you want it to be. The following example adds a new ListBox to the range you currently have selected. It also adjusts the list boxes size to match the dimensions of the selected range.
Add a ListBox
Notice how this macro used the .Name property we briefly mentioned earlier to make a new ListBox with a custom name.
Creating a New ListBox with VBA
To delete the list box named 'NewListBox', run this VBA macro:
Delete a ListBox
Jump to Table of Contents
Add Items to your ListBox list
This is an important section. In this section, we'll show you how to dynamically add items to your listbox using VBA.
This is great for filling out a listbox when your workbook opens and you don't want to link it to a range.
Add Item to Bottom of ListBox
The following macros add items to the list using three different methods, all of which use the AddItem method. You can choose to use any method you want. Most people opt for the method with the least amount of coding!
Add an item to a ListBox with VBA
The commonality between each of these methods is they all use the AddItem property to add the new item and they all add the new item to the end of the listbox by default.
Add Item Anywhere in ListBox List
The beauty of the AddItem property is that you can change where you want to add an item. To do that, you just need to tell your form control listbox the index position where you want your new item. Here's an example illustrating how to add an item to the top of your list:
Add Item to Top of ListBox
Similarly, change the , 1 to a , 2 to add an item to the 2nd item in the list.
Add Static Items Based on Range
Let's say you want to populate a listbox based on the values in a range of cells in Excel, but you don't want the values in your listbox to change when the values in your range change. To hardcode the value to a range of values without linking the listbox to the cell, try the following macros:
Method 1
Method 2
Method 3
Because you didn't link the ListBox to the range itself, you can change the values in cells F1:F4 without the items in your listbox changing!
Note, you may run into errors trying this method when you have numbers stored in the range. Make sure your numbers are represented as text or loop through each cell in the range and convert the values to strings using the VBA cStr function before adding them to your ListBox.
Jump to Table of Contents
Link Range to a ListBox
To dynamically link a range to your ListBox such that the content of your listbox automatically updates when you change the cells in the linked range, use the ListFillRange keyword. Here are some examples:
Method 1
Method 2
Method 3
This macro links your ListBox to the range A1:A4 on Sheet Sheet4. When you change the values in cells A1:A4 on Sheet4, the values in your ListBox will automatically change.
Before
After
Notice that this is the same as right clicking your ListBox, clicking Format Control, navigating to the Control tab and changing the 'Input Range' field.
Fallout 4 inspect weapon.
Form Control ListBox Input Range
Jump to Table of Contents
Remove items from your ListBox
Remove Single Item in your ListBox
You can remove a single item with the RemoveItem command. Follow it with the number of the item you want to remove. For example, a 4 removes the 4th item in the list.
Method 1
Method 2
Method 3
Remove All Items in your ListBox
If you prefer to delete all the items in your list, use the RemoveAllItems command.
Method 1
Method 2
Method 3
Once you run one of these commands, you'll be left with an empty ListBox.
Jump to Table of Contents
Edit an Item in your ListBox
You can change an individual item in your ListBox using VBA by combining the .RemoveItem property and the .AddItem property. You first have to delete the 2nd item, then add a new item in its place. There's no way to directly alter the text of the item in the ListBox once it's already in there.
The following examples change the 2nd item in the ListBox list from Alaska to Montana. You must unlink your ListBox from a range in order for this to work!
Method 1
Method 2
Method 3
Sidify apple music converter 1 4 3rd. Here's the end product:
Change ListBox Item
Jump to Table of Contents
Selecting a ListBox
To select a ListBox titled List Box 2 on your spreadsheet, try the following macro:
Select 'List Box 2'
Jump to Table of Contents
Linking ListBox Selection to a Cell
To link the selected value in a ListBox to a cell using VBA, execute one of these macros:
Method 1
Method 2
Method 3
Cell A1 in Sheet1 will change to the ListIndex position associated with the selected item. In other words, if the third item in the ListBox is selected, the value in cell A1 in Sheet1 will become a 3. This only works well for the Single Selection Type.
If no item is selected, the cell value would be 0.
Jump to Table of Contents
Enable 3D Shading with VBA
To enable 3D shading for your ListBox using VBA, change the Display3DShading property to True. To disable it, change it to False.
Method 1
Method 2
3D shading makes your Form Control ListBoxes look kind of like ActiveX ListBoxes. They're beveled a bit near the edges to give it a sunken appearance. You cannot change the 3D shading by using the ControlFormat method.
Display3DShading True (Left) vs False (Right)
Jump to Table of Contents
Outlook 2016 Mac Property Is Not Valid For This Object Type In Word
Prevent ListBox from Printing
There are times when you may want your ListBox to be displayed on your excel sheet but you don't want it to print when you print your workbook. You can prevent your listboxes from printing by disabling the PrintObject property.
Method 1
Method 2
Method 3
Set PrintObject back to True if you want to print your listboxes, or set it to false to prevent your listboxes from printing with the rest of your sheet. This is equivalent to unchecking and checking the 'Print object' box when you right click your listbox > Format Control > Properties.
Outlook 2016 Mac Property Is Not Valid For This Object Type Code
Jump to Table of Contents
Control ListBox Object Positioning
Outlook 2016 Mac Property Is Not Valid For This Object Type In Spanish
Like other control objects, you can fix your ListBoxes to the screen so they don't move when you add cells, delete cells, adjust column widths or adjust row heights. To do this, you would right click your list box and go to Format Control > Properties. You would then adjust the options under 'Object positioning.'
To automate these actions with VBA, change the Placement property:
Method 1
Method 2
Outlook 2016 Mac Property Is Not Valid For This Object Type List
There are three possible values for the .Placement property, but you can only use 2 of them with the list box, as described below:
| Value | Description |
|---|---|
| xlMoveAndSize | Move and size with cells. This option is unavailable for Form Control List Boxes. |
| xlMove | Move but don't size with cells. When a cell is added or resized, the position of the control will change. |
| xlFreeFloating | Don't move or size the cells. When a cell is added or resized, the control will stay in the same position. The position of the control is locked. |
Jump to Table of Contents
Looping Through All ListBoxes
I'm going to close out this massive tutorial with an example macro that loops through all the Form Control List Boxes in your active and lets you take different actions depending on which items are selected.
Be sure to read the comment cards to determine how you can better use this VBA macro. As always, I encourage you to combine this loop with the List Box lessons you learned throughout this article to create your own powerful VBA macros.
Jump to Table of Contents
Closing Thoughts
This is one of my longest ActiveX and Form Control Guides - I hope you enjoyed it! When you're ready to take your VBA to the next level, subscribe using the form below and follow me on Twitter .
Oh, and if you have a question, post it in our VBA Q&A community.
The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That's why I'm giving away my VBA Developer's Kit for free to our newest subscribers.
