problem assigning range returned by function, to combobox rowsource

[Kate]

Hi, I have modified Dave Peterson's lovely 'mvlookup' function for
returning multiple objects from a lookup function, to instead return a
range rather than a concatenated string. I renamed it 'rvlookup'.

I initially encountered an object error as I attempted to set the
function's return value to a range rather than a string. After adding
a 'set' in front of the final return value statement, no more error there.

However, when I invoke the function code to set a rowsource property
of a combobox TO this range, I get the 'type mismatch error 13" message.

Rvlookup has inputs of a lookup value, the array in which to look, and
which column contains the values to be returned. These are the
essential code snippets:

Public Function rvlookup(lookupValue As Variant, tableArray As Range,
colIndexNum As Long) As Range
Dim myRes() As Variant
Dim i As Long

.....(code that sets myres(i) to all the matching values in the lookup
range. The code below then places those value into cells in a worksheet.)

'select Lookups worksheet
Sheets("lookups").Select
'select starting cell to paste mill codes
Range("l2").Select
For i = LBound(myRes) To UBound(myRes)
ActiveCell.Value = myRes(i)
ActiveCell.Offset(1, 0).Select
Next i

Set rvlookup = ActiveSheet.Range("l2:l" & (i - 1))
End Function

The above function is invoked when a value is chosen from a combobox
(cboCompany), and the rowsource of a second combobox (cboMills) is
being set to the range that is returned by rvlookup:

Sub cboCompany_Change()
frmMills.cboMills.RowSource = _
rvlookup(Worksheets("data").Range("c3"), _
Worksheets("Lookups").Range("C2:J139"), 2).Value

frmMills.cboMills.ControlSource = Worksheets("data").Range("d1")
frmMills.Show
End Sub

I get the type mismatch error when attempting to set the rowsource of
the cboMills combobox. Apparently it doesn't recognize that this is a
range? I added the '.value' at the end of the rowsource setting but
that didn't help.

Any ideas? Thanks to all,

Kate

 

[Bob Phillips]

Can we see all the code?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Kate]

*sigh* okay, here's all the code. I have made a few changes since
posting, after realizing that the combobox rowsource needs to be a
string rather than a range, but it still doesn't work. In the
function rvlookup, there are times when the return value of the
function is set to an error code which is a violation of the value
being a range, but that isn't why it's failing.


Sub cboCompany_Change()
Dim strSource As String
Dim rngSource As Range

Set rngSource = rvlookup(Worksheets("data").Range("c3"),
Worksheets("Lookups").Range("C2:d139"), 2)
'put range address into string
strSource = rngSource.Name & "!" & rngSource.Address
frmMills.cboMills.RowSource = strSource
frmMills.cboMills.ControlSource = Worksheets("data").Range("d1")
frmMills.Show
End Sub

Public Function rvlookup(lookupValue As Variant, tableArray As Range,
colIndexNum As Long) As Range
'this function returns an array of values
Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim initTableCols As Long
Dim i As Long

'clear contents of previous rowsource for selected mills
Sheets("lookups").Select
Columns("L:L").Select
Range("L6").Activate
Selection.ClearContents

Set initTable = Nothing
On Error Resume Next
Set initTable = Intersect(tableArray, _
tableArray.Parent.UsedRange.EntireRow)
On Error GoTo 0

If initTable Is Nothing Then
Set rvlookup = CVErr(xlErrRef)
Exit Function
End If

initTableCols = initTable.Columns.Count

i = 0
Do
myRowMatch = Application.Match(lookupValue,
initTable.Columns(1), 0)

If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum -
1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0) _
.Resize(initTable.Rows.Count -
myRowMatch, _
initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop

If i = 0 Then
Set rvlookup = CVErr(xlErrNA)
Exit Function
End If

'select Lookups worksheet
Sheets("lookups").Select
'select starting cell to paste mill codes
Range("l2").Select
For i = LBound(myRes) To UBound(myRes)
ActiveCell.Value = myRes(i)
ActiveCell.Offset(1, 0).Select
Next i

Set rvlookup = ActiveSheet.Range(Cells(2, 12), Cells((i - 1), 12))
End Function

Can we see all the code?

Hi, I have modified Dave Peterson's lovely 'mvlookup' function for

 

[Vasant Nanavati]

Haven't worked through the entire code, but perhaps:

strSource = rngSource.Name & "!" & rngSource.Address

should be:

strSource = rngSource.Parent.Name & "!" & rngSource.Address

Also, ControlSource (like RowSource) takes a string parameter rather than a
range parameter.

Just some thoughts ...

--

Vasant

 

[Kate]

Vasant, thank you for responding. Your change in the strSource
worked! Bless you.

But I'm still having a problem with referencing the second combobox
from code. Maybe you can shed some light on this. I'm new to working
with Excel VBA (experienced with Access VBA, however). I'm not sure
how the referencing works. I have two combo boxes on a sheet which is
code-named 'facility.' Within VBA, I can see the object of the first
combo box when working with the sheet (e.g., if I type in 'Facility.'
I can see cboCompany in the list of objects that belong to the sheet,
but I do not see cboMills (the other combo box) in the list!
Therefore, in trying to set its rowsource property, I had to find by
using a debug.print statement, which shape# corresponded to its name.
Why doesn't the second combo box show up as an object in the
worksheet???

Thanks again,
Kate

 

[Vasant Nanavati]

Kate:

Perhaps the second ComboBox is from the Forms Toolbar rather than the
Control Toolbox. Does it look different from the first one?