Excel VBA fill a userform combo box RowSource with a string variable? 1

[knifey]

Hi,
I have a combo box on a userform that needs to be filled from a different range each time,
The code I have below looks like it should work fine. But it errors on the strRowSource = line. Please can anyone offer any advice on the syntax?
Thanks
K

strRowSource = meFileName & "=!" & Sheets("LOV").Range("$B$" & firstRow & ":$B$" & (firstRow + numWords))
'strRowSource = wb4 & "!" & Sheets("LOV").Range("$B$" & firstRow & ":$B$" & (firstRow + numWords))
'strRowSource = meFileName & "!" & Sheets("LOV").Range("$B$" & firstRow & ":$B$" & (firstRow + numWords))

With frmChooseMonth.cmbChooseMonth
'Clear old ListBox RowSource
.RowSource = vbNullString
'Parse new one
.RowSource = strRowSource
End With

 

[Gruuuu]

strRowSource = meFileName & [b][red]"!"[/red][/b] & Sheets("LOV").Range("$B$" & firstRow & ":$B$" & (firstRow + numWords))

I think that = is throwing you off?

 

[knifey]

Hi Gruu,
I ran your code but it comes up with a run tim error 13, type mismatch.
Any ideas on what could be causing this?
Thanks,
K

 

[Gruuuu]

Post all of the code that this pertains to?
I see the problem, but need to see the whole code to direct you to the solution.

 

[SkipVought]

Isn't the result something like...
[tt]
Sheet!A2:A36
[/tt]
If so...

with Sheets("LOV")
  strRowSource = .Name & "!" & .Range("$B$" & firstRow & ":$B$" & (firstRow + numWords)).Address
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I'd try this:

strRowSource = "LOV!B" & firstRow & ":B" & (firstRow + numWords)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[knifey]

Hi PHV,
I just saw another post you answered and went with that code (more or less the same):

strRowSource = meFileName & "!" & Sheets("LOV").Range("$B$" & firstRow & ":$B$" & (firstRow + numWords)).Address

Works perfectly,
Cheers,
K