MsgBox including range value
 

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.

MsgBox including range value
 

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" wrote:

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.

MsgBox including range value
 

On Sep 25, 12:58*pm, Mike H wrote:
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" wrote:
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.- Hide quoted text -

- 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.

MsgBox including range value
 

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


"jeff" wrote:

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.

MsgBox including range value
 

On Sep 25, 1:13*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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").referstorang e.value

--
HTH...

Jim Thomlinson



"jeff" wrote:
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.- Hide quoted text -

- Show quoted text -

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

MsgBox including range value
 

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

"Jim Thomlinson" wrote:

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


"jeff" wrote:

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.

MsgBox including range value
 

Glad I could help and thanks for the feedback

"jeff" wrote:

On Sep 25, 12:58 pm, Mike H wrote:
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" wrote:
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.- Hide quoted text -

- 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.