VBA apparently does not like to base a constant value on something that may
change later and refuses to cooperate. After a lot of futzing around I ended
up
defining it as a string variable rather than a constant and VBA allows that.

Just as a matter of programming practice though I'd rather use a constant. Is
there some way to persuade VBA to create a constant based on the value of a
variable at the moment the constant is created?

No, there isn't. A constant means just that ... a value that is KNOWN at the
time you write the code, and that will not change when the code runs.

CONST statements can include a limited number of "calculations", such as
adding together two other constants, etc, because the compiler can do a small
amount of arithmetic. But the compiler cannot do things like call functions in
libraries or execute a call to Excel's object model to determine the active
sheet. And the active sheet is not known until the code is actually running.