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

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

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

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

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

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

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