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

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

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

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

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

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

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