I have a UserForm with a ComboBox. The current RowSource for the ComboBox is
shown below. Is it possible to make the RowSource be the range $P$2:$P$300
of the active worksheet? I have 12 different worksheets that can call this
one UserForm.

'Names'!$P$2:$P$300

Define/re-define the row source with

ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

When you do this needs to be controlled, maybe the worksheet activate event?

--

Regards,
Nigel




"Patrick C. Simonds" wrote in message
...
I have a UserForm with a ComboBox. The current RowSource for the ComboBox
is shown below. Is it possible to make the RowSource be the range
$P$2:$P$300 of the active worksheet? I have 12 different worksheets that
can call this one UserForm.

'Names'!$P$2:$P$300

Just noticed you say the UserForm is called from one of 12 sheets, therefore
put this in the UserForm initialize event.


Private Sub UserForm_Initialize()
ComboBox1.RowSource = ActiveSheet.Name & "!$P$2:$P$300"
End Sub


--

Regards,
Nigel




"Nigel" wrote in message
...
Define/re-define the row source with

ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

When you do this needs to be controlled, maybe the worksheet activate
event?

--

Regards,
Nigel




"Patrick C. Simonds" wrote in message
...
I have a UserForm with a ComboBox. The current RowSource for the ComboBox
is shown below. Is it possible to make the RowSource be the range
$P$2:$P$300 of the active worksheet? I have 12 different worksheets that
can call this one UserForm.

'Names'!$P$2:$P$300

I tried this a few different ways, no of which worked:

Private Sub Worksheet_Activate()
ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

End Sub

Private Sub Worksheet_Activate()
With UserForm2
ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"
End With
End Sub

Both of which returned an "Object Required error"

And then within the UserForm Initialization, but it returned "Could not set
the RowSource property. Invalid property value"

"Nigel" wrote in message
...
Define/re-define the row source with

ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

When you do this needs to be controlled, maybe the worksheet activate
event?

--

Regards,
Nigel




"Patrick C. Simonds" wrote in message
...
I have a UserForm with a ComboBox. The current RowSource for the ComboBox
is shown below. Is it possible to make the RowSource be the range
$P$2:$P$300 of the active worksheet? I have 12 different worksheets that
can call this one UserForm.

'Names'!$P$2:$P$300

Thanks

With your help I was able to use what you gave me and I was better able to
refine my internet search. It seems that if you put no worksheet reference,
it defaults to the active worksheet.

ComboBox1.RowSource = "$P$7:$P$3000"

"Patrick C. Simonds" wrote in message
...
I tried this a few different ways, no of which worked:

Private Sub Worksheet_Activate()
ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

End Sub

Private Sub Worksheet_Activate()
With UserForm2
ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"
End With
End Sub

Both of which returned an "Object Required error"

And then within the UserForm Initialization, but it returned "Could not
set the RowSource property. Invalid property value"

"Nigel" wrote in message
...
Define/re-define the row source with

ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

When you do this needs to be controlled, maybe the worksheet activate
event?

--

Regards,
Nigel




"Patrick C. Simonds" wrote in message
...
I have a UserForm with a ComboBox. The current RowSource for the ComboBox
is shown below. Is it possible to make the RowSource be the range
$P$2:$P$300 of the active worksheet? I have 12 different worksheets that
can call this one UserForm.

'Names'!$P$2:$P$300

If your active sheet name has spaces then you need to enclose the name in
single quotes, but as your later post shows leaving it blank will act on the
active sheet anyway. Always assuming the active sheet is a worksheet.


--

Regards,
Nigel




"Patrick C. Simonds" wrote in message
...
I tried this a few different ways, no of which worked:

Private Sub Worksheet_Activate()
ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

End Sub

Private Sub Worksheet_Activate()
With UserForm2
ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"
End With
End Sub

Both of which returned an "Object Required error"

And then within the UserForm Initialization, but it returned "Could not
set the RowSource property. Invalid property value"

"Nigel" wrote in message
...
Define/re-define the row source with

ComboBox1.RowSource = ActiveSheet.Name & "!$P$1:$P$300"

When you do this needs to be controlled, maybe the worksheet activate
event?

--

Regards,
Nigel




"Patrick C. Simonds" wrote in message
...
I have a UserForm with a ComboBox. The current RowSource for the ComboBox
is shown below. Is it possible to make the RowSource be the range
$P$2:$P$300 of the active worksheet? I have 12 different worksheets that
can call this one UserForm.

'Names'!$P$2:$P$300

I like to let excel do the heavy lifting...

Dim myRng as range
set myrng = activesheet.range("p2:p300")
....

... = myrng.address(external:=true)

------------
If you want to supply the string yourself:
... = "'" & activesheet.name & "'!p2:p300"

(Some names will require those apostrophes.)

"Patrick C. Simonds" wrote:

I have a UserForm with a ComboBox. The current RowSource for the ComboBox is
shown below. Is it possible to make the RowSource be the range $P$2:$P$300
of the active worksheet? I have 12 different worksheets that can call this
one UserForm.

'Names'!$P$2:$P$300

--

Dave Peterson