MsgBox including range value

[jeff]

I've played around with this, and looked at other posts. Still can't
get this right.
I want to show a MsgBox with a message & also the value of a range.
Here's what I've got so far. Any help would be appreciated. I'm
getting a 1004 error. Method 'range of object'_Global' failed.
I've tried variations of this:

MsgBox "Miles remaining until next oil change: " & Range
(NextOilChg_Chev).Value

Note: This is all in 1 line in the module.
Thanks
j.o.

 

[Mike H]

Jeff,

For this to work you must have a named range set on the worksheet called
NextOilChg_Chev

MsgBox "Miles remaining until next oil change: " &
Range("NextOilChg_Chev").Value

and in the code it must be in quotes ""

Mike

 

[jeff]

Jeff,

For this to work you must have a named range set on the worksheet called
NextOilChg_Chev

MsgBox "Miles remaining until next oil change:  " &
Range("NextOilChg_Chev").Value

and in the code it must be in quotes ""

Mike







- Show quoted text -

Thanks Mike.
I already had the range named. BUT, I didn't even notice the quotes
for the range name was missing....
I appreciate the fast response.

 

[Jim Thomlinson]

Named ranges are a bit tricky to refer to in code. What you have here

Range("NextOilChg_Chev").Value
is translated to
Activesheet.Range("NextOilChg_Chev").Value

If NextOilChg_Chev is not on the acive sheet then the code crashes. To refer
to global named ranges you are best off to use

application.names("NextOilChg_Chev").referstorange.value

This avoids the sheet reference entirely. So your code should be

MsgBox "Miles remaining until next oil change: " & _
application.names("NextOilChg_Chev").referstorange.value

 

[jeff]

Named ranges are a bit tricky to refer to in code. What you have here

Range("NextOilChg_Chev").Value
is translated to
Activesheet.Range("NextOilChg_Chev").Value

If NextOilChg_Chev is not on the acive sheet then the code crashes. To refer
to global named ranges you are best off to use

application.names("NextOilChg_Chev").referstorange.value

This avoids the sheet reference entirely. So your code should be

MsgBox "Miles remaining until next oil change:  " & _
 application.names("NextOilChg_Chev").referstorange.value

--
HTH...

Jim Thomlinson







- Show quoted text -

Thanks for the help, Jim. I did not know this. Very efficient! I
appreciate it.

 

[Mike H]

Jim,

Range("NextOilChg_Chev").Value
is translated to
Activesheet.Range("NextOilChg_Chev").Value

That isn't strictly correct. In a standard module the simplified version I
gave will always work no matter which sheet is active. Likewise in worksheet
code 'provided' the named range is in the same sheet as the code.

I agree it will fail as worksheet code if the named range is in another sheet.

Mike

 

[Mike H]

Glad I could help and thanks for the feedback

Thanks Mike.
I already had the range named. BUT, I didn't even notice the quotes
for the range name was missing....
I appreciate the fast response.