"Excel VBA Help"


Play Friv Now - Play Friv Games
Our Info Pages:-
The Code Cage - Microsoft Office help


» Sponsored Links
Sponsored Links
Play Friv Now - Play Friv Games
Columbus Website Designers
http://www.local-ranking.com -

Contact Us to Advertise here!

» Tutorial Library


Latest Content

Combobox Uses

Using comboboxes to prevent user's 'mistakes'

If you want to prevent users to make 'mistakes' you can offer them ranges of valid choices.
A long as they make a choice from the options you offer they can be sure they make a valid choice.
I prefer that method to bothering users with messageboxes afterwards that they did something 'wrong'

A very useful tool for supporting users' choices is the combobox.
A combobox can reside in a userform or in a worksheet.
I will restrict the illustration to comboboxes in a userform.

Basically there a 2 kinds of comboboxes:
- a combobox in which the user can add new choices
- a combobox with a limited set of choices from which the user can only select one or none.
The most error proof combobox is the 'dropdownlist' combobox.

If you add a combobox to a userform or sheet it's default is the combobox in which the user can add options.

If you prefer a 'dropdownlist' combobox you can change the combobox's behaviour by changing it's property 'style' to fmStyledropdownlist.


In Design Mode you can adapt the default property for a combobox.
When loading the userform the combobox will now be a dropdownlist automatically.
It saves you coding and memory use during execution to set this property in DesignMode..

Populating the combobox

The easiest way to populate a combobox is to use it's property .List
You can populate a combobox by assigning a one-dimensional or multidimensional array to it.

An array can consist of:
- numbers
- single characters
- textstrings
- dates

You can use Excel's builtin arrays (customlists) or produce your own.

A very handy tool to design an array is the method 'Evaluate'.
You can consider it to be the VBA equivalent of Excels' array formulae.
In can be written as Evaluate("...") or in shorthand, using vertical brackets: [....]
In the attachment only the shorthand method is being employed.

The attachment

In the attachment you will find VBA code to populate a combobox, using only a oneliner at a time.
The data to populate the comboboxes with will only be produced using VBA. So no data in the workbook, nor elsewhere will be read and used.

The attachment contains comboboxes, populated with:

- positive numbers
- negative numbers

- lowercase characters
- uppercase characters

- textstrings
- textstrings containing a variable substring
- textstrings containing numbers
- years in the future
- years in the past
- monthnames (short & full)
- weekdaynames (short & full)
- dates in the future
- dates in the past
- dates in a specified future

Linked File: www.thecodecage.com/fill%20combobox%20or%20picklist.zip

Questions asked on Excel VBA by our members

.....Read More (entire tutorial!)
» Recent Threads
Title, Username, & Date Forum
Paste column 2 of array
Today 16:30
Microsoft Excel Forum
How to manipulate (outlying) data
1st September 2015 08:53
Microsoft Excel Forum
Merge 2 arrays
25th August 2015 19:14
Microsoft Excel Forum
Outlook 2007 mail problem
1st September 2015 14:39
Microsoft Outlook Forum
Cannot open rtf attachment in Outlook
29th August 2015 15:11
Microsoft Outlook Forum
Index Match and Copy Paste Values
31st August 2015 23:12
Microsoft Excel Forum
Unwanted word wrap in macro to save email as .txt
28th August 2015 15:53
Microsoft Outlook Forum
VBA to list and hyperlink files
23rd August 2015 22:30
Microsoft Excel Forum
Insert function via VBA
26th August 2015 20:27
Microsoft Excel Forum
Loop Through Array
20th August 2015 19:49
Microsoft Excel Forum
Appending data to another sheet based on column...
19th August 2015 15:57
Microsoft Excel Forum
Outlook inbox repair tool
20th August 2015 17:31
Microsoft Outlook Forum
press and hold CTRL button VBA
7th August 2015 20:09
Microsoft Excel Forum
How to Sum Range Based on a Date?
20th August 2015 13:39
Microsoft Excel Forum
» News
Aug 27, 2015 - 8:53 PM - by Simon Lloyd
Hi all,
it appears that some fool is sending out emails impersonating the code cage server, they are sending spam emails containing an attachment, please do not open or even save the attachment.

The spammer is using this email... [Read More]
Powered by vBadvanced CMPS v3.1.0
The Code Cage Affilliates

http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 22:23.

Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2015, Jelsoft Enterprises Ltd.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft