| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Including in a range | Excel Discussion (Misc queries) | |||
| Copy Range including Formats and Forumulas | Excel Programming | |||
| multiple lookup including date range | Excel Worksheet Functions | |||
| MAX/MIN in range potantially including text | Excel Worksheet Functions | |||
| Average range including blank cells: #DIV/0! | Excel Worksheet Functions | |||
Linear Mode
