Solved: add to combobox if not already present

[gazzababbo]

Hi guys, hope someone here can put me out of my misery with this one that's driving me nuts. I'm using VBA in a userform in excel that populates two comboboxes from lists in a sheet in the document. Is there a way to: *select an item from combobox1 *check in the combobox2 to see if it contains the selected item from combobox1 *add the combobox1 item to combobox2 if it is not already there else *do nothing if combobox1 item is already in the combobox2 list. Does this make sense? I am using Visual Basic 6.3 according to the 'about' info in the VB menus Thanks

[lucas]

Welcome to the forum

Couple of ideas:

Can they read from the same range?

How are the ranges called? Are they named ranges?

Can you post your workbook? Hit the post reply button at the bottom left of the last post and when the page loads add your message and then scroll down till you find the button that says "manage attachments"

[georgiboy]

An example would help but this should give you something to play with.

[gazzababbo]

Hi Lucas, I'll try and give a more detailed explanation as I am reluctant to post workbook due to it containing student information. (I am a teacher and this is my electronic markbook - I hope you are not offended).

Anyway, the issue concerns a media file organiser within the marbook. I have an embedded media player in my userform and I can successfully call an .avi or whatever when running the userform.

I would like to organise my .avi files so that from the main list of unsorted video clips in combobox1, I can add a selected clip to another list that populates a second combobox2.

Combobox1 range is not named but the row source address is set to a specific cell on the sheet. (the reason I have not named the range is that it is a growing list that is added to frequently. I have the row source address cell on the sheet set up to automatically count what is underneath it and produce a range>)

Combobox2 row source is set to a specific 20 cells in the sheet but again the range is not named. When other changes are made earlier on in the userform, the userform populates combobox2 based on those changes, so the actual range or row source of combobox2 is changing.

if you are asking can I select the item from combobox1 and then check a specific area of a sheet to see if the combobox1 item is present here then yes I can.

I know it's a pain without the workbook - sorry

[gazzababbo]

This looks promising georgiboy, I'll give it a go now. It's doing what i asked i'll just have to try and cram it into my userform.

Thanks for your help so far guys, I'll let you know the outcome. :thumb

[gazzababbo]

Sorted!

I took on board the replies to my post and have come up with a solution that I am happy with. Thanks very much to both Lucas and Georgiboy :thumb


Anyway for what it's worth here is the solution I have come up with. I know some pros will baulk at this code but I'll be honest and say I've only been using VBA for about a year off and on and I have never had formal tuition. (does it show? :doh:)

It first checks if there is space in the list for the file to be added (max 20 in this case)

If there is room it then checks to see if the value is already present in the second combobox and acts accordingly after that.

Thanks again guys


Private Sub ComboBox13_Change()
Dim r As Long
r = mediarow.Value
Dim c As Long
c = mediacol.Value
ActiveWorkbook.Sheets("Planner").Activate
If WorksheetFunction.CountIf(Range(Cells(r, c), Cells(r + 19, c)), "") < 1 Then
MsgBox "Unit List Has Maximum File Entries"
Else
If WorksheetFunction.CountIf(Range(Cells(r, c), Cells(r + 19, c)), ComboBox13.Value) = 0 Then
Cells(r, c).Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell = ComboBox13.Value

ComboBox7.Clear

ComboBox7.AddItem Cells(r, c)
ComboBox7.AddItem Cells(r + 1, c)
ComboBox7.AddItem Cells(r + 2, c)
ComboBox7.AddItem Cells(r + 3, c)
ComboBox7.AddItem Cells(r + 4, c)
ComboBox7.AddItem Cells(r + 5, c)
ComboBox7.AddItem Cells(r + 6, c)
ComboBox7.AddItem Cells(r + 7, c)
ComboBox7.AddItem Cells(r + 8, c)
ComboBox7.AddItem Cells(r + 9, c)
ComboBox7.AddItem Cells(r + 10, c)
ComboBox7.AddItem Cells(r + 11, c)
ComboBox7.AddItem Cells(r + 12, c)
ComboBox7.AddItem Cells(r + 13, c)
ComboBox7.AddItem Cells(r + 14, c)
ComboBox7.AddItem Cells(r + 15, c)
ComboBox7.AddItem Cells(r + 16, c)
ComboBox7.AddItem Cells(r + 17, c)
ComboBox7.AddItem Cells(r + 18, c)
ComboBox7.AddItem Cells(r + 19, c)
MsgBox "File Added to Unit List"


Else
MsgBox "File Already in Unit List"

End If
End If

End Sub

[mikerickson]

ComboBox7.AddItem Cells(r, c)
ComboBox7.AddItem Cells(r + 1, c)
ComboBox7.AddItem Cells(r + 2, c)
ComboBox7.AddItem Cells(r + 3, c)
ComboBox7.AddItem Cells(r + 4, c)
ComboBox7.AddItem Cells(r + 5, c)
ComboBox7.AddItem Cells(r + 6, c)
ComboBox7.AddItem Cells(r + 7, c)
ComboBox7.AddItem Cells(r + 8, c)
ComboBox7.AddItem Cells(r + 9, c)
ComboBox7.AddItem Cells(r + 10, c)
ComboBox7.AddItem Cells(r + 11, c)
ComboBox7.AddItem Cells(r + 12, c)
ComboBox7.AddItem Cells(r + 13, c)
ComboBox7.AddItem Cells(r + 14, c)
ComboBox7.AddItem Cells(r + 15, c)
ComboBox7.AddItem Cells(r + 16, c)
ComboBox7.AddItem Cells(r + 17, c)
ComboBox7.AddItem Cells(r + 18, c)
ComboBox7.AddItem Cells(r + 19, c)
Can be replaced with
ComboBox7.List = Cells(r,c).Resize(20,1).Value

[lucas]

gazzababbo, when you get this resoved be sure to mark your thread solved using the thead tools at the top of the page.

[gazzababbo]

ComboBox7.AddItem Cells(r, c)
Can be replaced with
[vba]ComboBox7.List = Cells(r,c).Resize(20,1).Value Thanks for that mikerickson, I'll give it a go. I tend to find solutions over a period of time that work for me (by trial and error usually) but I am aware that there is probably a faster, more efficient and proper way to do most of the stuff that I do. I am always pleased to improve or find short cuts so thanks again.