Public Const & RowSource

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen53 - 30 Nov 2007 18:43 GMT

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?

Signature

Thanks for your help.
Karen53

Reply to this Message

Jim Thomlinson - 30 Nov 2007 19:13 GMT

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.

Signature

HTH...

Jim Thomlinson

> Hi,
>
[quoted text clipped - 13 lines]
>
> Is it possible to do this?

Reply to this Message

Karen53 - 30 Nov 2007 19:51 GMT

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)

Signature

Thanks for your help.
Karen53

> 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
[quoted text clipped - 26 lines]
> >
> > Is it possible to do this?

Reply to this Message

Jim Thomlinson - 30 Nov 2007 20:11 GMT

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.

Signature

HTH...

Jim Thomlinson

> Hi Jim,
>
[quoted text clipped - 38 lines]
> > >
> > > Is it possible to do this?

Reply to this Message

Karen53 - 30 Nov 2007 20:13 GMT

Thanks, Jim! I'm off and running, well, at least skipping.

Signature

Thanks for your help.
Karen53

> 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.
[quoted text clipped - 41 lines]
> > > >
> > > > Is it possible to do this?

Reply to this Message