Prev: Question Responses
Next: Code to send SMS text message?
From: Christopher on 20 Feb 2010 12:14
OssieMac,
Would it be possible to provide a file of the code you posted here?
I'm using Excel 2003, and even though I'm copying and psating the code, the Combo Boxes end up being empty when I run/submit the form with the 3 Combo Boxes.
Thank you, OssieMac.



OssieMac wrote:

Hi again Brian,I see you have posted for more help no doubt due to me being
18-Feb-10

Hi again Brian,

I see you have posted for more help no doubt due to me being slow to get
back to you but as I have written this code I thought I might as well post
it. If you get a better answer from someone else then go for it.

For the following code you need 2 additional worksheets in your workbook.
One called Raw Data and the other called Combo Data.

In the Raw Data sheet you need your list of data in the first 3 columns.
(That could be changed but better to keep it simple if it just means copying
the 3 columns.)

The first column will contain the data for the first combo box
The second column has data for the 2nd combo box
Third column has data for the third combo box.

Now this data requires every row in each column to have data. Therefore the
data should look something like the following.(I am not sure which one is the
customer and which one is the office in you sample data so I have assumed the
first is customer and last is office but I am sure you can sort that out.)

It does not matter if the customer is repeated and appears in multiple
states and/or multiple offices just so long as every cell in each column
contains data like the following. (Some of the states I have used are
ficticious because I am not from Nth America and i do not know the states or
abbreviations for them).

Customer State Office
ACWOGAMA GA F5341
AGSTGAAU GA R3547
AGSTGABM GA R6341
ACWOGAMA NJ A2345
AGSTGAAU SF B123
AGSTGABM AL C6789

Basically the system copies a unique sorted list of the data in column 1 of
Raw Data and places it in Column 1 of Combo Data when the form is loaded and
this becomes the Rowsource for Combo1.

When a selection is made in Combo1 then AutoFilter is applied to the first
column in Raw Data based on the value in Combo1. A unique sorted list of the
visible cells in column 2 is then copied to Column 2 of Combo Data and it
becomes the Rowsource for Combo2.

When a selection is made in Combo2 then AutoFilter is applied to the 2nd
column in Raw Data based on the value in Combo2. A unique sorted list of the
visible cells in column 3 is then copied to Column 3 of Combo Data and it
becomes the Rowsource for Combo3.

'********************************
'This sub between the asteeisk lines
'in module that Shows userform
'Last command is Userform1.Show

Sub ShowTheUserform()
Dim rngFilter As Range

'Next line is optional
Application.ScreenUpdating = False

With Sheets("Combo Data")
'Clear existing data
..Columns("A:C").ClearContents

'Name start cells for each ComboBox RowSource
..Range("A1").Name = "Cbo1List"
..Range("B1").Name = "Cbo2List"
..Range("C1").Name = "Cbo3List"
End With

'Create unique list of first column
'of data for 1st ComboBox RowSource
With Sheets("Raw Data")
'Turn off AutoFilter
..AutoFilterMode = False
'Set rng variable to all data in column A
Set rngFilter = .Range(.Cells(1, "A"), _
..Cells(.Rows.Count, "A").End(xlUp))
End With

'Copies unique data to Sheets("Combo Data")
rngFilter.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("Cbo1List"), _
Unique:=True

'Name the column of data for 1st ComboBox
'Starts row 2; excludes column header
With Sheets("Combo Data")
..Range(.Cells(2, "A"), _
..Cells(.Rows.Count, "A") _
..End(xlUp)).Name = "Cbo1Source"

'Clear any existing Sort Parameters
..Sort.SortFields.Clear

'Set the Sort Parameters
..Sort.SortFields.Add _
Key:=Range("Cbo1Source"), _

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Putting Twitter Realtime Search to Work
http://www.eggheadcafe.com/tutorials/aspnet/6100d85b-2f27-472d-af24-c9960b55b669/putting-twitter-realtime.aspx
 | 
Pages: 1
Prev: Question Responses
Next: Code to send SMS text message?