delete commandbuttons on copied sheet

[qerj]

I have a procedure run by a commandbutton on 1 worksheet that copies
another worksheet to a 3rd worksheet. The procedure then is supposed
to delete the commandbuttons on the newly copied worksheet. It
deletes the 1st one ok but ends in a 1004 error when trying to delete
the 2nd button. What am I doning wrong...thx


here is the code for the deletion of the objects...

Option Compare Text
Dim strMainWks As String
Dim strTmpWks As String
Dim wsCurWks As Worksheet
Dim rSortRange As Range
Dim rMyRange As Range
Dim rMyCell As Range
Dim rMyDeletion As Range


Sub deletebtns_Click()
Dim wsCurWks As Worksheet
Dim Obj As OLEObject
Set wsCurWks = ActiveSheet


For Each Obj In ActiveSheet.OLEObjects

If InStr(1, Obj.ProgId, "CommandButton") <> 0 Then
'MsgBox Obj.ProgId
Obj.Delete
End If
Next Obj
End Sub

 

[Tom Ogilvy]

Is the button trying to delete itself?

 

[qerj]

Tom...

me again...

thx for the input on the InStr issue...

No, I do not believe it is trying to delete itself. Just as with the
other procedure you helped with, I have a command button on one
worksheet that is performing actions on another worksheet. It 1st
copies a third worksheet to another so I can do some temporary actions
on it while saving the original. The copy works ok, the sort and
deletion of certain rows now works ok, but when I try and delete the 2
commandbuttons that are copied over, it hangs on deleting the 2nd one.
As you may know, when excel 97 copies a worksheet with controls on
it, it gives them new names and does not copy the supporting code. The
result is that I have 2 command buttons that I want to get rid of.

Long story but here is the code again..

Sub btnSortBySRC_Click()

Dim strMainWks As String
Dim strTmpWks As String
Dim wsCurWks As Worksheet
Dim rSortRange As Range
Dim Obj As OLEObject

TakeFocusOnClick = False
strMainWks = "Main Data"
strTmpWks = "Sort Workspace"
ActiveWorkbook.Sheets(strMainWks).Activate
Sheets(strMainWks).Copy After:=Sheets(strMainWks)
ActiveSheet.Name = strTmpWks

Set wsCurWks = ActiveSheet


For Each Obj In wsCurWks.OLEObjects
'MsgBox Obj.ProgId & " " & Obj.Name
If InStr(1, Obj.ProgId, "CommandButton") <> 0 Then
Obj.Delete
End If
Next Obj

Set rSortRange = wsCurWks.Range("A3:S96")
rSortRange.Select
rSortRange.Sort Key1:=rSortRange.Columns(8), Order1:=xlAscending

End Sub

 

[Tom Ogilvy]

I created your environment and ran your code and the commandbuttons were
both deleted. I only had two commandbuttons on the sheet and no other OLE
Objects. I am using Excel 97 SR2 - US English.

Since I can't duplicate the problem, I can't really say why you are having
it.

You command
TakeFocusOnClick isn't really doing anything. It sees TakeFocusOnclick as a
variable. You need to set it to

btnSortBySRC.TakeFocusOnClick = false

but it is really too late at that point. I would right click on
btnSortBySRC in design mode and change the property manually. It is
possible that it is true and that is the source of your problem. I had
manually set my property to False.

or you can try changing to

Sub btnSortBySRC_Click()

Dim strMainWks As String
Dim strTmpWks As String
Dim wsCurWks As Worksheet
Dim rSortRange As Range
Dim Obj As OLEObject

ActiveCell.Activate
' TakeFocusOnClick = False

 

[qerj]

just some more info...

since I was having problems deleting the command buttons I figured I
would just make the visible property = false

worked fine on the 1st button, did not end in error, but the 2nd
button was still visible! I checked it's properties and Visible was
set to "False" but it still was visible...Friday the 13th carried over
to the 15th??!!

Eric

 

[Tom Ogilvy]

That isn't unusual. This can be a redraw problem. It can often be fixed by
putting in lines like these at the end of the macro

Application.ScreenUpdating = False
Application.ScreenUpdating = True

this will often cause the screen to refresh. If that doesn't work, you
might have to revert to an API call.

 

[qerj]

Tom,

Hard to believe but that was it...set Application.ScreenUpdating =
False before the obj loop and then turned it on ... after and the
buttons were gone.

I reset the code to delete instead of setting .visible to false and
both buttons were deleted...go figure...

Thx much,
Eric