| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't get this to work
The following code worked until I changed how I started it. It originally
started with button on a worksheet (not a control button). I changed it to a control button now it doesn't work. Everything else in the procedure works fine. The code is: Set ycell = Sheets("Estimates DB").Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" The error I get is: Method 'Range" of object '_Worksheet' failed. Everything is set right. ycell as range, the sheet name and cell name are correct. I was wondering if it could be because I changed it to a control button and moved the procedure from a module to to an excel object. Any help on this would be greatly appreciated. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get this to work
What does "It don't work" mean? It won't start when the button is clicked?
It gives an error message? What specifically don't work? "ranswrt" wrote: The following code worked until I changed how I started it. It originally started with button on a worksheet (not a control button). I changed it to a control button now it doesn't work. Everything else in the procedure works fine. The code is: Set ycell = Sheets("Estimates DB").Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" The error I get is: Method 'Range" of object '_Worksheet' failed. Everything is set right. ycell as range, the sheet name and cell name are correct. I was wondering if it could be because I changed it to a control button and moved the procedure from a module to to an excel object. Any help on this would be greatly appreciated. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get this to work
Your issue revolves around the sheet reference. Ycell is on sheet Estimates
DB but when you use Range at the beginning of Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" if is the same as Activesheet.Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" which is probably not correct as Estimates DB is probably not the active sheet. Try this... with Sheets("Estimates DB") Set ycell = .Range("estdbno") ..Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" end with -- HTH... Jim Thomlinson "ranswrt" wrote: The following code worked until I changed how I started it. It originally started with button on a worksheet (not a control button). I changed it to a control button now it doesn't work. Everything else in the procedure works fine. The code is: Set ycell = Sheets("Estimates DB").Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" The error I get is: Method 'Range" of object '_Worksheet' failed. Everything is set right. ycell as range, the sheet name and cell name are correct. I was wondering if it could be because I changed it to a control button and moved the procedure from a module to to an excel object. Any help on this would be greatly appreciated. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get this to work
The procedure work when the button is clicked until it gets to this line and
I get the error message. Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" "JLGWhiz" wrote: What does "It don't work" mean? It won't start when the button is clicked? It gives an error message? What specifically don't work? "ranswrt" wrote: The following code worked until I changed how I started it. It originally started with button on a worksheet (not a control button). I changed it to a control button now it doesn't work. Everything else in the procedure works fine. The code is: Set ycell = Sheets("Estimates DB").Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" The error I get is: Method 'Range" of object '_Worksheet' failed. Everything is set right. ycell as range, the sheet name and cell name are correct. I was wondering if it could be because I changed it to a control button and moved the procedure from a module to to an excel object. Any help on this would be greatly appreciated. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get this to work
Probably u can do is:
Sheets("Estimates DB").Select Set ycell = Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" coz once i had a similar problem with using sheet("sheetname").range -- Ak "Jim Thomlinson" wrote: Your issue revolves around the sheet reference. Ycell is on sheet Estimates DB but when you use Range at the beginning of Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" if is the same as Activesheet.Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" which is probably not correct as Estimates DB is probably not the active sheet. Try this... with Sheets("Estimates DB") Set ycell = .Range("estdbno") .Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" end with -- HTH... Jim Thomlinson "ranswrt" wrote: The following code worked until I changed how I started it. It originally started with button on a worksheet (not a control button). I changed it to a control button now it doesn't work. Everything else in the procedure works fine. The code is: Set ycell = Sheets("Estimates DB").Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" The error I get is: Method 'Range" of object '_Worksheet' failed. Everything is set right. ycell as range, the sheet name and cell name are correct. I was wondering if it could be because I changed it to a control button and moved the procedure from a module to to an excel object. Any help on this would be greatly appreciated. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get this to work
While that will probably work it is a very bad practice to select sheets or
ranges. It makes the code slower to execute. It bloats the code and it makes it much more suseptible to failure in the future as you modify code and potentially change the active sheet around. Just my 2 cents... -- HTH... Jim Thomlinson "Ak Man" wrote: Probably u can do is: Sheets("Estimates DB").Select Set ycell = Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" coz once i had a similar problem with using sheet("sheetname").range -- Ak "Jim Thomlinson" wrote: Your issue revolves around the sheet reference. Ycell is on sheet Estimates DB but when you use Range at the beginning of Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" if is the same as Activesheet.Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" which is probably not correct as Estimates DB is probably not the active sheet. Try this... with Sheets("Estimates DB") Set ycell = .Range("estdbno") .Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" end with -- HTH... Jim Thomlinson "ranswrt" wrote: The following code worked until I changed how I started it. It originally started with button on a worksheet (not a control button). I changed it to a control button now it doesn't work. Everything else in the procedure works fine. The code is: Set ycell = Sheets("Estimates DB").Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" The error I get is: Method 'Range" of object '_Worksheet' failed. Everything is set right. ycell as range, the sheet name and cell name are correct. I was wondering if it could be because I changed it to a control button and moved the procedure from a module to to an excel object. Any help on this would be greatly appreciated. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't get this to work
Thanks that worked great. I new there would be an easy answer.
"Jim Thomlinson" wrote: Your issue revolves around the sheet reference. Ycell is on sheet Estimates DB but when you use Range at the beginning of Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" if is the same as Activesheet.Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" which is probably not correct as Estimates DB is probably not the active sheet. Try this... with Sheets("Estimates DB") Set ycell = .Range("estdbno") .Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" end with -- HTH... Jim Thomlinson "ranswrt" wrote: The following code worked until I changed how I started it. It originally started with button on a worksheet (not a control button). I changed it to a control button now it doesn't work. Everything else in the procedure works fine. The code is: Set ycell = Sheets("Estimates DB").Range("estdbno") Range(ycell.Offset(1, 0), ycell.Offset(estnum, 0)).Name = "estdbnorng" The error I get is: Method 'Range" of object '_Worksheet' failed. Everything is set right. ycell as range, the sheet name and cell name are correct. I was wondering if it could be because I changed it to a control button and moved the procedure from a module to to an excel object. Any help on this would be greatly appreciated. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
| flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
| HOW TO MAKE A LIST OF WORK SHEET IN WORK BOOK IN EXCEL 2007 | Excel Programming | |||
| If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
| Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Linear Mode
