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" <[email protected]> wrote in message
news:%[email protected]...
> 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

[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



Bob Phillips wrote:
> Can we see all the code?
>
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

[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" <[email protected]> wrote in message
news:[email protected]...
> *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
>
>
>
> Bob Phillips wrote:
>> Can we see all the code?
>>
> 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

[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 wrote:
> 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 Nanavati]

Kate:

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

--

Vasant



"Kate" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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 ...
>>

[Kate]

Vasant, the second combo was created just as the first, from the
worksheet using the toolbox.

Vasant Nanavati wrote:
> Kate:
>
> Perhaps the second ComboBox is from the Forms Toolbar rather than the
> Control Toolbox. Does it look different from the first one?
>