How Do I Populate A ComboBox RowSource With VBA

[Minitman]

Greetings,

I have a UserForm with a ComboBox(CB1) and 15 TextBoxes(TB1 thru TB15)
on it. The RowSource for CB1 is a 12column wide named range. I tried
to add a search button to find any match in column A instead of just
the first letters typed into CB1 (I forgot about the 12 column
width!).

Here is the code for the search button:
*****************************************************************************
Private Sub SearchButton1_Click()
Dim lCount As Long
Dim rFoundCell As Range
Dim lLoop As Long
Dim wSheet As Worksheet
Dim StrFind As String
Set wSheet = Worksheets("Food_List")
Set rFoundCell = wSheet.Range("A1")
StrFind = CB1.Value
lLoop = WorksheetFunction.CountIf(wSheet.Columns(1), "*" & _
StrFind & "*")
If lLoop > 0 Then
CB1.RowSource = vbNullString
CB1.Value = vbNullString
End If
For lCount = 1 To lLoop
Set rFoundCell = wSheet.Columns(1).Find(What:=StrFind, _
After:=rFoundCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
CB1.AddItem rFoundCell
Next lCount
End Sub
*****************************************************************************
My Question is, how do I make rFoundCell 12 columns wide?

Any help or is appreciated.

TIA

-Minitman

 

[Dave Peterson]

You have a problem.

If you use a range to populate the combobox, then you can use all 256 columns in
combobox.

But if you don't tie the combobox back to a range (.rowsource), you're limited
to 10 columns in the combobox.

From xl2003 VBA's help for columncount:

Setting ColumnCount to 0 displays zero columns, and setting it to -1 displays
all the available columns. For an unbound data source, there is a 10-column
limit (0 to 9).

=====
If you really need to use 12 columns, you could add a dummy worksheet and then
populate that with your nice contiguous data. And use that in the rowsource.

 

[Minitman]

Hey Dave,

Your right, I have a problem.

The question is, if I cannot load the found items into CB1.RowSource
directly (too many columns), how do I load these same items into a
named range called FoodList_3 on a sheet called "Food_List" starting
in row 2, column N? I can load a named range into the RowSource.

Any suggestions or help in greatly appreciated.

-Minitman

 

[Dave Peterson]

First, I'd use a dedicated worksheet (I used one named Temp) that no one can
see. And I'd just plop the values into A:L (12 columns). Worksheets are cheap
<bg> and you don't have to worry about damaging anything important. (I used a
worksheet named Temp. And I hid that worksheet, too!)

Then use that range to as the row source for the combobox.
Option Explicit
Private Sub UserForm_Initialize()

Dim TempWks As Worksheet
Dim RealWks As Worksheet
Dim DestCell As Range
Dim myStr As String
Dim FirstAddress As String
Dim FoundCell As Range
Dim OkToContinue As Boolean
Dim myRowSource As Range

Set TempWks = Worksheets("Temp")
Set RealWks = Worksheets("Food_List")

myStr = "A" 'whatever you want (CB1.Value)

With TempWks
'put headers in row 1 if you want columnheads.
'do it manually and don't ever clear those cells
.Rows("2:" & .Rows.Count).ClearContents
Set DestCell = .Range("a2")
End With

OkToContinue = True
With RealWks
With .Range("a2:a" & .Cells(.Rows.Count, "A").End(xlUp).Row)
Set FoundCell = .Cells.Find(what:=myStr, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "No match"
OkToContinue = False
Else
FirstAddress = FoundCell.Address
Do
FoundCell.EntireRow.Copy _
Destination:=DestCell
Set DestCell = DestCell.Offset(1, 0)
Set FoundCell = .FindNext(FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With
End With

If OkToContinue Then
With TempWks
'A:L is 12 columns
Set myRowSource _
= .Range("a2:L" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
With Me.ComboBox1
.ColumnCount = 12
.ColumnHeads = True
.RowSource = myRowSource.Address(external:=True)
End With
End If

Me.ComboBox1.Enabled = OkToContinue

End Sub

 

[Minitman]

hey Dave,

Ok, I made a new worksheet and renamed it "Temp". I moved "FoodList_3
to "Temp" and then hid "Temp"

Here's the code for "FoodList_3" from the named range window:
***************************************************************************
=OFFSET(Temp!$A$2,0,0,COUNTA(Temp!$A:$A),12)
***************************************************************************
After that it gets a bit more involved.

When the UserForm opens, CB1.RowSource is set as "FoodList_2", which
is the master food list. It is only when the user cannot find a match
by typing in CB1, that the user types in a key word or phrase instead.
at this point, CB1 does not find a match so the user presses the
"Search" button.

It is here that vba will find any occurrence of the key word or phrase
in "FoodList_2" and copy each row into the named range called
"FoodList_3", all 12 columns. After this, the RowSource for CB1 is
emptied and reset to "FoodList_3" (the default RowSource is otherwise
"FoodList_2").

CB1.Value is still blank until the user picks one of the items in the
drop down list. When there is a change in CB1.Value, then the other
11 TextBoxes get their values from CB1's RowSource.
I'm not sure if UserForm_Initialize is the best place to put this
code, since I only use this search button for problem entries.

Dave Hawley over at Ozgrid, gave me this solution (I forgot to mention
the 12 columns so it did not account for them)"
****************************************************************************
Private Sub SearchButton1_Click()
Dim lCount As Long
Dim rFoundCell As Range
Dim lLoop As Long
Dim wSheet As Worksheet
Dim StrFind As String
Set wSheet = Worksheets("Food_List")
Set rFoundCell = wSheet.Range("A1")
StrFind = CB1.Value
lLoop = WorksheetFunction.CountIf(wSheet.Columns(1), "*" & _
StrFind & "*")
For lCount = 1 To lLoop
Set rFoundCell = wSheet.Columns(1).Find(What:=StrFind, _
After:=rFoundCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
Next lCount
End Sub
**************************************************************************
And the sub to load the TextBoxes is:
**************************************************************************
Private Sub CB1_Change()
'Loads TB5 thru TB15 from CB1's RowSource
i = 0
If Not CB1.ListIndex < 0 Then
For i = 1 To 11
Me.Controls("TB" & i + 4).Text = _
CB1.List(CB1.ListIndex, i)
Next i
End If
End Sub
**************************************************************************
I can send a sample workbook, if that would make it easier for you.

-Minitman

 

[Dave Peterson]

The code that I suggested was put into the userform_initialize event. Try
moving that into the Search routine that you used.

See if that works ok.

 

[Minitman]

Hey Dave,

It took me a couple of days to get it working and it works very well.

I did change a couple of items. I used dynamic named ranges listed in
previous post, it simplified things.

If anyone would like a copy of my finale code, I will gladly post or
send an email attachment. Just let me know.

Again, thank you Dave, for your help, it is greatly appreciated.

-Minitman

 

[Susan]

minitman -
i've been banging my head against the wall for hours now, trying
various different approaches to this problem via previous posts.....
copied & pasted dave's code & changed my sheetnames & still won't work.
doesn't come up with any compile (or any other!) errors, but the
combobox is empty.
could you pls. post your (working) code?
thanks
susan