Use Range Name in Macro
 

This seems so silly that I can hardly believe that I need to ask, but
....

How can I use my Named Range "PctOverall" (an overall % complete number
stored in the worksheet) in a macro?

All I want is to display the value in a text box on the screen for a
few seconds. I have the code to write and later delete the text box,
the text to appear inside as "boilerplate" and ... I can't use my
Range("PctOverall").Value

What's the text to replace the "W1" value below? I want this because
the sheet may be modified with inserted or deleted columns, and I don't
want to have to change "W1" as it moves around.

Selection.Characters.Text = "Overall Percent Complete = " & _
Application.Text(Range("W1").Value, "#0.####%")

Thanks,
Chris

To use a worksheet named range in VB use ([ and ]) like ([PctOverall]) eg
Sheet1.Range([PctOverall]).Copy
Regards,
Alan.
"Blue Hornet" wrote in message
ups.com...
This seems so silly that I can hardly believe that I need to ask, but
...

How can I use my Named Range "PctOverall" (an overall % complete number
stored in the worksheet) in a macro?

All I want is to display the value in a text box on the screen for a
few seconds. I have the code to write and later delete the text box,
the text to appear inside as "boilerplate" and ... I can't use my
Range("PctOverall").Value

What's the text to replace the "W1" value below? I want this because
the sheet may be modified with inserted or deleted columns, and I don't
want to have to change "W1" as it moves around.

Selection.Characters.Text = "Overall Percent Complete = " & _
Application.Text(Range("W1").Value, "#0.####%")

Thanks,
Chris

Why can't you use Range("PctOverall").Value?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Blue Hornet" wrote in message
ups.com...
This seems so silly that I can hardly believe that I need to ask, but
...

How can I use my Named Range "PctOverall" (an overall % complete number
stored in the worksheet) in a macro?

All I want is to display the value in a text box on the screen for a
few seconds. I have the code to write and later delete the text box,
the text to appear inside as "boilerplate" and ... I can't use my
Range("PctOverall").Value

What's the text to replace the "W1" value below? I want this because
the sheet may be modified with inserted or deleted columns, and I don't
want to have to change "W1" as it moves around.

Selection.Characters.Text = "Overall Percent Complete = " & _
Application.Text(Range("W1").Value, "#0.####%")

Thanks,
Chris

Bob,

I was hoping you guys could tell me "why?"

Here's the code that's failing:
MyPctOverall = Sheets("Data Sheet").Range("PctOverall").Value

also attempted in Alan's suggested syntax as:
MyPctOverall = Sheet5.Range("PctOverall").Value
and as
MyPctOverall = Sheet5.Range([PctOverall]).Value

(This is a simplified form of what I'm attempting, but this exact
syntax fails, and I don't know why.)

All times I get the incredibly helpful "Run-time error 1004": Method
'Range' of object '_Worksheet' failed. But PctOverall IS a named range
in Data Sheet / Sheet5, so I don't know what the problem is. I can
reference PctOverall on the sheet itself with no errors.

I'm not new to this stuff, and I'm sure I've done it before in exactly
the way you suggest, Bob, but I don't get this. Why is it not working
here?

Chris