VBA code comboboxes (skip blanks & avoid repeats)

[pbobadilla]

Hello,

I have roughly 50 comboboxes in sheet "TR-Metric Pg2" that pull information off of another sheet called "CTC-Form." I would like to know if there is a way to ignore blank cells so that they do not show up in the combobox drop down menus. Also, is it possible to write VBA code so that you cannot pick the same data twice between all 50 comboboxes? Any help would be appreciated.


Thanks,

Pablo

[Tinbendr]

Crossposted. It is considered rude to post the same question to multiple boards UNLESS you provide a link to the other board.

The first is easy. Just compare to nothing.

[vba]If Cells(1,2).value <> "" then
Combobox1.Additem Cells(1,2).value
[/vba]
2. You will have to compare the contents of each combox with the item to add. If there are a lot of items to add, this will bog down after half way.

You could use a Collection to create your unique items and store the range of items for each combobox.

[pbobadilla]

I apologize about the cross post. It shows I am a newbie . I tried placing this code into both the combobox1 code and the code for worksheet "TR-Metric Pg2" and both did not work. Is there something I am doing incorrectly? The range the combo box is gathering data from is BJ11;BJ100 in another worksheet called "CTC-Form". I apologize if I am missing something obvious, but I am just now trying to learn this stuff.

thanks,

[Tinbendr]

Can you rough out a sample workbook for us. I'm having a little trouble following what you're describing. (And only include about 5 comboboxes.

[pbobadilla]

Can you rough out a sample workbook for us. I'm having a little trouble following what you're describing. (And only include about 5 comboboxes.

I have already created the workbook. I am attaching the original workbook and feel free to just look at like 5 comboboxes. It looks like the file is too big and the file type is not supported by this forum. I have uploaded it to the following address sendspace.com/file/30efnk

Thanks,

Pablo

[snb]

to ignore empty cells:

[vba]
sub snb
combobox1.list=filter([transpose(if(CTC-Form!BJ11:BJ100="","~",CTC-Form!BJ11:BJ100))],"~",false)
end sub
[/vba]

[pbobadilla]

Also, this file pulls information from another file in our shared drive through formulas. Therefore, you will get errors int he formula sicne the links are broken.

I set this file up so that our quality department enters quality data into the CTC-Form sheet. They enter information from left to right. Once they enter a quality status for each drum in column A, then a formula in column BJ decides whether it should reference a drum # from column C or to leave it blank. This is done through an IF statement in column BJ for each row of data. Combo boxes in sheet Tr-Metric Pg 2 pull from a range BJ12:BJ10000 in sheet CTC-Form.

[pbobadilla]

to ignore empty cells:

[vba]
sub snb
combobox1.list=filter([transpose(if(CTC-Form!BJ11:BJ100="","~",CTC-Form!BJ11:BJ100))],"~",false)
end sub
[/vba]

I tried entering this code into the General code for TR-Metric Pg 2 and it still did not skip blanks for combobox1. I also tried to just assign a button and run the snb macro, but I got an error. What am I doing incorrectly?

Thanks,

[snb]

Post a sample workbook please.

[pbobadilla]

Post a sample workbook please.

Here is a sample of what it does with the combo boxes.

[snb]

There's no code in it ??

[Kenneth Hobs]

Use single quotes for the sheet name just as you would for standard formulas.

[VBA]Sub snb()
ComboBox1.List = Filter([Transpose(IF('CTC-Form'!BJ12:BJ21="","~",'CTC-Form'!BJ12:BJ21))], "~", False)
End Sub

Private Sub UserForm_Initialize()
snb
End Sub
[/VBA]

[snb]

1. do not use the property 'listfillrange.
2. remove the values in the property 'listfillrange' in each combobox first.
3. Next step; use:

[vba]
Private Sub Workbook_Open()
Sheets("Tr-metric Pg2").ComboBox1.List = Filter([Transpose(If('CTC-Form'!BJ12:BJ21="","~",'CTC-Form'!BJ12:BJ21))], "~", False)
for j=2 to 50
Sheets("Tr-metric Pg2").OleObjects("ComboBox" & j).Object.List = Sheets("Tr-metric Pg2").ComboBox1.List
next
End Sub
[/vba]

[pbobadilla]

1. do not use the property 'listfillrange.
2. remove the values in the property 'listfillrange' in each combobox first.
3. Next step; use:

[vba]
Private Sub Workbook_Open()
Sheets("Tr-metric Pg2").ComboBox1.List = Filter([Transpose(If('CTC-Form'!BJ12:BJ21="","~",'CTC-Form'!BJ12:BJ21))], "~", False)
for j=2 to 50
Sheets("Tr-metric Pg2").OleObjects("ComboBox" & j).Object.List = Sheets("Tr-metric Pg2").ComboBox1.List
next
End Sub
[/vba]

. Still not working for me. I removed all the list fill ranges for each combo box. Then I pasted this code into the general code section of the VBA window for sheet TR-Metric Pg2. What am I doing wrong?

Thanks,

Pablo

[snb]

Pasting it in the wrong codemodule.

[pbobadilla]

Pasting it in the wrong codemodule.

I tried pasting this in the "this workbook" code section and when I opened the workbook again it gave me an error saying the following section of the code was not found (highlighted in yellow):

Sheets("TR-Metric Pg2").ComboBox1.List = Filter([Transpose(If( 'CTC-Form'!BJ12:BJ10000="","~",'CTC-Form'!BJ12:BJ10000))], "~", False)

[Kenneth Hobs]

2. remove the values in the property 'listfillrange' in each combobox first.