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
(E-Mail Removed)



"Patrick C. Simonds" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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
(E-Mail Removed)



"Nigel" <(E-Mail Removed)> wrote in message
news:O4U$(E-Mail Removed)...
> 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
> (E-Mail Removed)
>
>
>
> "Patrick C. Simonds" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message
news:O4U$(E-Mail Removed)...
> 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
> (E-Mail Removed)
>
>
>
> "Patrick C. Simonds" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:O4U$(E-Mail Removed)...
>> 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
>> (E-Mail Removed)
>>
>>
>>
>> "Patrick C. Simonds" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>>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
(E-Mail Removed)



"Patrick C. Simonds" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:O4U$(E-Mail Removed)...
>> 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
>> (E-Mail Removed)
>>
>>
>>
>> "Patrick C. Simonds" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>>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("p2300")
....

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

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

(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