Public Const & RowSource

K

Karen53

Hi,

I am trying to set these as public constants so I don't have them retyped
all over the place. I use the same form and reset the RowSource depending on
what I'm doing. So, I wanted to set them in one place and use them as a
variable. This is what I tried

Public Const CAMExtLIRowSource As String =
"Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _

"(""CAMLineItemsExterior"").Address(external:=True)"

then I use it with .rowsource = CAMExtLIRowSource

I get an error "Could not set rowsource. Invalid Property Value"

Is it possible to do this?
 
J

Jim Thomlinson

What you are attempting to do will not work. If I understand what you want
you are trying to set you rowsource to an address (like Sheet1!A1). What you
are actually doing is setting it to the string
Worksheets(Replace(Tablespg.Name, """", "" ''"")).Range" & _

"(""CAMLineItemsExterior"").Address(external:=True)"

What you want to do is to change your const to a regular variable and then
set up a procedure to populate that variable when the book is opened. Note
that the address function only returns the address and not the sheet. You
need to concatenate the sheet name to the address when you populate the
variable.
 
K

Karen53

Hi Jim,

Thank you for your prompt reply.

I am unsure how the sheet name is to be concatenated to the address. Isn't
the sheet name already there with the (Replace(Tablespg.Name, "", " ' ' "))?
Since I'm using Sheet code names, I was told this translates it into the
sheet name. Would this be all I would need?

Worksheets(Replace(Tablespg.Name, "", " ' ' ")).Range _
("CAMLineItemsExterior").Address(external:=True)
 
J

Jim Thomlinson

Sorry... You are correct. You have external set to true so it will return the
entire address. I guess I just did not read closely enough.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Changed variable value 6
RowSource 4
Dynamic rowsource for ComboBox 6
Range of Object Worksheet failed 2
Help in sorting data 2
Userform Not working PLEASE HELP 3
Constants 2
Public Variable dilemma 23

Top