Excel and ComboBox 2

[pbt1234]

I have an excel spreadsheet that contains a combo box whose list fill range is a table in the spreadsheet. In order to make this easy to maintain I would like to be able to set the list fill range to something like A1:A1000, that way as items are added to the table the combo box picks them up, thus simplifying the process of maintaining the list. The problem is that if I do this and there are "blank" cells in the defined range (i.e. A1:A1000) blanks are created in the combo box which is confusing. Is there a way to select only the data and not the empty cells?

Thanks in advance!
pbt1234

 

[PaulRD]

Here is the closest I could figure that I think is 'relatively' easy.

First, copy the following into the 'view code' for the sheet

Sub ComboBox_DropButtonClick()
  Dim LastRow As String
  LastRow = Range("B" & Rows.Count).End(xlUp).Address
  ActiveWorkbook.Names.Add Name:="ComboRange", RefersTo:="='Sheet1'!$B$3:" & LastRow
End Sub

Place the code in the sheet that your combo box occurs on (right click the tab, select view code, paste). you will need to change the "B" to whatever row your data is in.

Next, your data will need to be set to a named range. Select all you data then click in the Name Box (to the left of where you type) and set the name to ComboRange.

Last, set the properties of your control by right clicking on it. set the range to ComboRange and the rest as you see fit. Again right click and assign the macro to the control.

This will almost do what you want, the only flaw is it appears the macro does not run until after the combo box is used. This means if you adjust your range, you need to use the box twick to make it refresh to the correct list.

All of this said, I am assuming you are using the forms version and not the control toolbox version of the combobox. The control toolbox version can easily do what you want and much more but requires a 'bit' more coding to make work. As I first stated, I tried to keep it simple

Paul D
pimpin' aint easy

 

[01Patrik]

Why don't you use validation instead of combobox? Choose Data | Validation - allow list

=A1:A1000

check the box "ignore empty cells"

All done!

The only differences vs combobox are:

* To trigger a macro, you must use a worksheet_change event with application.intercept

* You must use vlookup / hlookup / index+match function on the cell with the validation, instead of whatever function you might use on the cell reference of the combobox.


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[pbt1234]

Patrik - -
Could not use validation because data is in another sheet.

++++++++++++++++++++
Paul D - -

I was able to get this to work by doing the following . . .

Private Sub cboBPI_DropButtonClick()
Dim LastRow As String
LastRow = (Sheets("Input Data").Range("L1").Value) + 3
cboBPI.ListFillRange = "'Input Data'!$E$3:$E$" & LastRow
End Sub

A crude variation of what you suggested originally. I added a count of the records in the table (L1) to get the count and then added 3 to count, allowing for header rows, etc.

Thanks to everyone for your help! Another problem solved thanks to the great help here in this forumn!!!

pbt1234

 

[01Patrik]

pbt: You can still use validation, all you have to do is name the range used in the validation.

1: Name A1:A1000 "myRange"
2: Validation: Allow list: =myRange

Works no matter in which sheet you have the named range.


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[SkipVought]

Check out How can I rename a table as it changes size faq68-1331.

You can use the OFFSET function in Insert/Name/Define to define a DYNAMIC NAMED RANGE.

That Name can be used with Data/Validation or a ComboBox ListFillrange even if the data is on another sheet.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[SkipVought]

I failed to notice the statement about BLANK cells.

First you must filter out the blank cells -- use Data/Advanced Filter - UNIQUE VALUES, copy to another location. Use this new list as your listfillrange.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[jfhewitt]

Name your range "database" and enter new data with Data/Form/New. This will make the table dynamic, and carry over to the combo box list. Blank cells in the table could be a problem, but this is a problem endemic to such tables as box lists.