problem assigning range returned by function, to combobox rowsource
 

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

problem assigning range returned by function, to combobox rowsource
 

Can we see all the code?

--

HTH

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


"Kate" wrote in message
...
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

problem assigning range returned by function, to combobox rowsource
 

*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

problem assigning range returned by function, to combobox rowsource
 

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" wrote in message
...
*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

problem assigning range returned by function, to combobox rowsource
 

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 ...

problem assigning range returned by function, to combobox rowsource
 

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" wrote in message
...
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 ...

problem assigning range returned by function, to combobox rowsource
 

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?