Hello i have a spreadsheet with a button with code attached, i am trying to
remove the button and the code. the code was easy, just copy the sheet. but
the button is not easy. this is my current code for the removal of the button
and stuff:

'create a new workbook to email
Sheets("Weekly Time Record").Select
Sheets("Weekly Time Record").Copy

'remove button and 2nd row in email version
Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete
Rows("2:2").Select
Selection.Delete Shift:=xlUp

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "Time Card " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName

the line:

Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete

keeps crapping out and giving me a out of memory error. i have also tried:

activeworkbook.Shapes.SelectAll
Selection.Delete

this gives me the same problem.

any ideas out there?

If you only have one shape, maybe just:
Sheets("Weekly Time Record").Shapes(1).delete

If you have lots of shapes--some to delete and some to keep, you could delete by
name:

Sheets("Weekly Time Record").Shapes("shape01namehere").delete
....etc...

You could even delete by certain properties.

Ron de Bruin shares lots of code that is more discerning when deleting:
http://www.rondebruin.nl/controlsobjectsworksheet.htm



DawnTreader wrote:

Hello i have a spreadsheet with a button with code attached, i am trying to
remove the button and the code. the code was easy, just copy the sheet. but
the button is not easy. this is my current code for the removal of the button
and stuff:

'create a new workbook to email
Sheets("Weekly Time Record").Select
Sheets("Weekly Time Record").Copy

'remove button and 2nd row in email version
Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete
Rows("2:2").Select
Selection.Delete Shift:=xlUp

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "Time Card " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName

the line:

Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete

keeps crapping out and giving me a out of memory error. i have also tried:

activeworkbook.Shapes.SelectAll
Selection.Delete

this gives me the same problem.

any ideas out there?

--

Dave Peterson

Hello

here is an additional question. will code crap out on a protected worksheet?
this is a protected sheet that i am doing this on. do i have to unprotect
first?

"DawnTreader" wrote:

Hello i have a spreadsheet with a button with code attached, i am trying to
remove the button and the code. the code was easy, just copy the sheet. but
the button is not easy. this is my current code for the removal of the button
and stuff:

'create a new workbook to email
Sheets("Weekly Time Record").Select
Sheets("Weekly Time Record").Copy

'remove button and 2nd row in email version
Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete
Rows("2:2").Select
Selection.Delete Shift:=xlUp

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "Time Card " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName

the line:

Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete

keeps crapping out and giving me a out of memory error. i have also tried:

activeworkbook.Shapes.SelectAll
Selection.Delete

this gives me the same problem.

any ideas out there?

Depends on how the objects were protected when you protected the worksheet.

maybe...

'create a new workbook to email
Sheets("Weekly Time Record").Copy

'remove button and 2nd row in email version
with activesheet
.unprotect password:="topSecRet"
.Shapes.SelectAll
Selection.Delete
.Rows(2).Delete
.protect password:="topSecRet"

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "Time Card " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName
end with



DawnTreader wrote:

Hello

here is an additional question. will code crap out on a protected worksheet?
this is a protected sheet that i am doing this on. do i have to unprotect
first?

"DawnTreader" wrote:

Hello i have a spreadsheet with a button with code attached, i am trying to
remove the button and the code. the code was easy, just copy the sheet. but
the button is not easy. this is my current code for the removal of the button
and stuff:

'create a new workbook to email
Sheets("Weekly Time Record").Select
Sheets("Weekly Time Record").Copy

'remove button and 2nd row in email version
Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete
Rows("2:2").Select
Selection.Delete Shift:=xlUp

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "Time Card " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName

the line:

Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete

keeps crapping out and giving me a out of memory error. i have also tried:

activeworkbook.Shapes.SelectAll
Selection.Delete

this gives me the same problem.

any ideas out there?

--

Dave Peterson

Hello

when i recorded the macro to get what i was going to use to program this it
specified the button, but it was number 4. i figured that each time i copied
the worksheet to a new book it added a number and so i just decided to try
and remove them all.

i will try your suggestions tomorrow and let you know what happens. thanks!

"Dave Peterson" wrote:

If you only have one shape, maybe just:
Sheets("Weekly Time Record").Shapes(1).delete

If you have lots of shapes--some to delete and some to keep, you could delete by
name:

Sheets("Weekly Time Record").Shapes("shape01namehere").delete
....etc...

You could even delete by certain properties.

Ron de Bruin shares lots of code that is more discerning when deleting:
http://www.rondebruin.nl/controlsobjectsworksheet.htm



DawnTreader wrote:

Hello i have a spreadsheet with a button with code attached, i am trying to
remove the button and the code. the code was easy, just copy the sheet. but
the button is not easy. this is my current code for the removal of the button
and stuff:

'create a new workbook to email
Sheets("Weekly Time Record").Select
Sheets("Weekly Time Record").Copy

'remove button and 2nd row in email version
Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete
Rows("2:2").Select
Selection.Delete Shift:=xlUp

If Not fs.FolderExists(DirString) Then
fs.CreateFolder DirString
End If
strFName = "Time Card " & SunDT & " " & USRNM & ".xls"
.Parent.SaveAs DirString & "\" & strFName

the line:

Sheets("Weekly Time Record").Shapes.SelectAll
Selection.Delete

keeps crapping out and giving me a out of memory error. i have also tried:

activeworkbook.Shapes.SelectAll
Selection.Delete

this gives me the same problem.

any ideas out there?

--

Dave Peterson