delete ActiveX Button and it's procedure

[luolovepi]

Dear all,
I use the code below to delete the CommandButton1, But how could I delete its corresponding procedure "CommandButton1_Click" as well?

ActiveSheet.OLEObjects("CommandButton1").Delete

Thank you so much!
Best regards,
lolo

 

[pgc01]

Hi Lolo

Chip Pearson explains here how to manipulate modules and procedures. Check the section "Deleting A Procedure From A Module".

http://www.cpearson.com/excel/vbe.aspx

 

[Leith Ross]

Hello lolo,

This may save you some time and hair pulling. Copy this code into a standard VBA module in your workbook. You can then call RemoveCode ActiveSheet.Name, "CommandButton1" from your macro.

'Written: July 04, 2011
'Author:  Leith Ross
'Summary: Removes all procedure code from a given module for a given object.

Sub RemoveCode(ByVal ModuleName As String, ByVal ObjectName)

  Dim I As Long
  Dim LineCount As Long
  Dim VBproc As String
  Dim VBproj As Object
  
    Set VBproj = ThisWorkbook.Application.VBE.ActiveVBProject
    
      With VBproj.VBComponents(ModuleName).CodeModule
        For I = .CountOfLines To 1 Step -1
          VBproc = .ProcOfLine(I, 0)
          LineCount = .ProcCountLines(VBproc, 0)
            If InStr(1, VBproc, ObjectName) Then
               .DeleteLines .ProcStartLine(VBproc, 0), LineCount
            End If
          I = I - LineCount + 1
        Next I
      End With
      
End Sub

Sincerely,
Leith Ross

 

[luolovepi]

Hi thank you Leith.
Can I assign "Sheet1", which is under "Microsoft Excel Objects" instead of "Modules" in VBE, to "ModuleName"? In other words, is "Sheet1" treated as a module as well?

Best regards,
lolo

Hello lolo,

This may save you some time and hair pulling. Copy this code into a standard VBA module in your workbook. You can then call RemoveCode ActiveSheet.Name, "CommandButton1" from your macro.

'Written: July 04, 2011
'Author:  Leith Ross
'Summary: Removes all procedure code from a given module for a given object.
 
Sub RemoveCode(ByVal ModuleName As String, ByVal ObjectName)
 
  Dim I As Long
  Dim LineCount As Long
  Dim VBproc As String
  Dim VBproj As Object
 
    Set VBproj = ThisWorkbook.Application.VBE.ActiveVBProject
 
      With VBproj.VBComponents(ModuleName).CodeModule
        For I = .CountOfLines To 1 Step -1
          VBproc = .ProcOfLine(I, 0)
          LineCount = .ProcCountLines(VBproc, 0)
            If InStr(1, VBproc, ObjectName) Then
               .DeleteLines .ProcStartLine(VBproc, 0), LineCount
            End If
          I = I - LineCount + 1
        Next I
      End With
 
End Sub

Sincerely,
Leith Ross

 

[Leith Ross]

Hello Lolo,

Yes, you can do that because "Sheet1" is an object module of Excel. However, the macro itself must be in a standard VBA module. This is necessary for the code to be accessible by other objects in the project.

Sincerely,
Leith Ross