Programatically Delete command button and code.
 

I have a command button on a worksheet which has code associated with the
worksheet. How to delete the control and code from another macro? The reason
is this control is on a worksheet that is copied to another workbook. Once
the copy is done, I want to delete the control and code so the target
worksheet won't display the Security Warning about macros when it is opened.

I've seen some hints at it in other posts on how to delete code and
controls, but can't quite translate them to my situation.

Any help is appreciated.

Programatically Delete command button and code.
 

Put all of your code in the ThisWorkbook area and delete control like this,
when you copy the sheet the code placed in ThisWorkbook won't go with

Sheets("sheet1").Shapes("CommandButton1").Select
Selection.Delete
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"DocBrown" wrote:

I have a command button on a worksheet which has code associated with the
worksheet. How to delete the control and code from another macro? The reason
is this control is on a worksheet that is copied to another workbook. Once
the copy is done, I want to delete the control and code so the target
worksheet won't display the Security Warning about macros when it is opened.

I've seen some hints at it in other posts on how to delete code and
controls, but can't quite translate them to my situation.

Any help is appreciated.

Programatically Delete command button and code.
 

Try the following code. You MUST have "Trust access to the Visual Basic
Project" checked in the Trusted Source tab of the Macro Security dialog.

Dim CommandButtonName As String
Dim StartLine As Long
Dim CountLines As Long
Dim CodeMod As Object
Dim WS As Worksheet
CommandButtonName = "CommandButton1" '<<< CHANGE AS REQUIRED
Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED
With WS
.OLEObjects(CommandButtonName).Delete
Set CodeMod = ThisWorkbook.VBProject.VBComponents(.CodeName).Cod eModule
End With
'<<<<<
With CodeMod
StartLine = .ProcStartLine(procname:=CommandButtonName & "_Click",
prockind:=0)
CountLines = .ProcCountLines(procname:=CommandButtonName & "_Click",
prockind:=0)
.DeleteLines StartLine:=StartLine, Count:=CountLines
End With
'<<<<<<

If you want to delete ALL the code in the module, not just the button's
Click event, replace the lines above between the <<<< markers with the
following:

With CodeMod
.DeleteLines StartLine:=1, Count:=.CountOfLines
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"DocBrown" wrote in message
...
I have a command button on a worksheet which has code associated with the
worksheet. How to delete the control and code from another macro? The
reason
is this control is on a worksheet that is copied to another workbook. Once
the copy is done, I want to delete the control and code so the target
worksheet won't display the Security Warning about macros when it is
opened.

I've seen some hints at it in other posts on how to delete code and
controls, but can't quite translate them to my situation.

Any help is appreciated.

Programatically Delete command button and code.
 

Thanks for the quick reply. :-)

This works great to delete the control on the worksheet, but I still need to
delete the VBA code in the Sheet module.

Thanks,
John S.

"John Bundy" wrote:

Put all of your code in the ThisWorkbook area and delete control like this,
when you copy the sheet the code placed in ThisWorkbook won't go with

Sheets("sheet1").Shapes("CommandButton1").Select
Selection.Delete
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"DocBrown" wrote:

I have a command button on a worksheet which has code associated with the
worksheet. How to delete the control and code from another macro? The reason
is this control is on a worksheet that is copied to another workbook. Once
the copy is done, I want to delete the control and code so the target
worksheet won't display the Security Warning about macros when it is opened.

I've seen some hints at it in other posts on how to delete code and
controls, but can't quite translate them to my situation.

Any help is appreciated.