ComboBox Rowsource question

[Patrick C. Simonds]

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

 

[Nigel]

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?

 

[Nigel]

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 address removed)

 

[Patrick C. Simonds]

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"

 

[Patrick C. Simonds]

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"

 

[Nigel]

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 address removed)

 

[Dave Peterson]

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

Dim myRng as range
set myrng = activesheet.range("p2300")
....

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