Assign RowSource of Excel combobox to Non-Active Page 2

[TheAceMan1]

Howdy All . . .

Is it possible to do something like:

[blue]Sheets("PageName").Range(B54:B60)[/blue]

... using Excel 2003. My intent is to have a user input form with various controls. Several of which will be comboboxes. Trying to keep from having lists on each sheet.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

Yes. I generally put lists on a separate hidden sheet.

Skip,

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

 

[TheAceMan1]

SkipVought . . .

Thanks for the reply.

When I manually enter ...

[blue]   Sheets("Sample").Range("B54:B60")[/blue]

... I get the error message [blue]Could not set the RowSource property. Invalid property value.[/blue]

What am I missing? . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

I ALWAYS use Named Ranges. You can then do all sorts of off-sheet stuff that is not possible using the Sheet!Range reference.

Skip,

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

 

[PHV]

Provided tha the CodeName of the sheet is really Sample:
Sample!B56:B60

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

 

[SkipVought]

Another reason that I would argue against using a range reference like Sample!B56:B60 for the row sources is because such lists are often dynamic. In fact, if you use Excel 2007+ AND INSERT your list as a TABLE, you could do something like this and make it dynamic...

Private Sub UserForm_Activate()
    With [TableName[ListHeadingName]]
        ComboBox1.RowSource = .Parent.Name & "!" & .Address
    End With
End Sub

Skip,

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