VBA ComboBox code help

Microsoft Microsoft office excel 2007 ac...
September 9, 2010 at 11:39:44
Specs: Windows Vista
I have a combo box named cmbManagers with the codes of some Managers. Each time I select a different code this should happen:
(Each manager has an area code, email, and name cell)

Insert code for the cmbManagers_Change event as follows:
NOTE: Variable declarations have already been entered.
To clear any previous data from cells C6 – C17.
To store the selected area (from the combobox) in the variable areaCode.
To start the variable EmployeeRow at 6.
To loop through each of the employee area codes on worksheet StaffData.
If the employee’s area code is the same as the value in the variable areaCode:
Place the employee’s number in the current cell pointed to.
Increment the variable EmployeeRow by 1.

Also I need the email of the selected manager each time to show on the cell next to the list.

Please help me out!!!


See More: VBA ComboBox code help

Report •

#1
September 10, 2010 at 07:09:43
Hi,

Some questions:
1. When you refer to I select a different code is this the same as area code
2. Does "area code" have anything to do with North American phone "Area Codes" such as 405.
3. What does Variable declarations have already been entered mean. If you are asking for some help on your macro, it is really not possible without knowing what you have already entered/coded. Why not post what you have already done.
4. You say you want to store the selected area - do you mean "store the selected area code"
5. You say you have a variable "area code" - what has this been declared as - is it an integer, a string, a variant, or what.
6. What do you mean by To start the variable EmployeeRow at 6 - how can you 'start' a variable. Do you mean you want to use this variable in a loop control such as: For EmployeeRow = 6 to x, or do you just want to set EmployeeRow to 6 -> EmployeeRow=6
7. loop through each of the employee area codes on worksheet StaffData - you need to say which cells contain the data, and do the number of cells containing data change - in other words will the range of cells containing data be hard coded such as "A1:C101" or will there need to be code to find the last cell containing data.
8. Place the employee’s number in the current cell pointed to Where is the employees number stored - is this number the same as the "area code" and what do you mean by "pointed to". The combo box will be selected when this macro runs - so what is pointing to the current cell, or do you mean the selected cell.
9. I need the email of the selected manager each time to show on the cell next to the list. - What list. You didn't mention a list before. All you have asked for is that the selected employees number (based on the area code selected in the combo box) is placed in a cell.

It would really be helpful if you started by saying what it is you wish to achieve - not what you think the steps in a macro should be.
For example:
I want to create a list of managers names on a worksheet named "Staff", with the list starting at row 6, with the name in column C, a code number ("Area code") in column D and an email address in column E.
The list would be built up by selecting the area codes from a drop-down list and as each area code is selected, the name and email associated with the code are retrieved from a master list on a worksheet named "StaffData" and placed on the next row on the "Staff" worksheet. This master list starts on row 1 with codes in column A, names in column B and email addresses in column C.
The number of rows containing data changes over time.
There are no duplicate code numbers.

With this sort of information, it would be possible to propose a solution.

Regards


Report •
Related Solutions


Ask Question