Can't get this to work

[ranswrt]

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

 

[JLGWhiz]

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?

 

[Jim Thomlinson]

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

 

[ranswrt]

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"

 

[Ak Man]

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

 

[Jim Thomlinson]

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

 

[ranswrt]

Thanks that worked great. I new there would be an easy answer.