Thread: Delete VB Codes in new workbook

hi,
I use something similar...see if it helps you

Sub Button5_Click()
sFilename = sPath & ActiveSheet.Name & ".xlsx"

'copy the active sheet into new workbook
ActiveSheet.Copy
ActiveWorkbook.SaveAs _
Filename:=sFilename, _
FileFormat:=51, _
Password:="", _
CreateBackup:=False
ActiveSheet.Shapes("Button 5").Delete
    ActiveSheet.Shapes("Button 3").Delete
ActiveSheet.Shapes("CommandButton1").Delete
ActiveSheet.Shapes("Button 2").Delete
    ActiveSheet.Shapes("Button 1").Delete
End Sub

Is the code in the worksheet module?

I am sorry, I forgot to mention that is attached to a button.

But is the code in the sheet module? If you don't know then attach the workbook

no, is not in a sheet module.

Hi John,

Thanks for the codes.

As royUK mentioned I attached my codes to the sheet module.

Is there any way to delete them?

Thanks
jwswks

Hello jwswks,

This code revision should work for you.

Sub Button5_Click()

  Dim VBcomp As Object
  Dim VBproj As Object
  Dim Wkb As Workbook
  
    sFilename = sPath & ActiveSheet.Name & ".xlsx"

    'copy the active sheet into new workbook
     ActiveSheet.Copy
     ActiveWorkbook.SaveAs _
     Filename:=sFilename, _
     FileFormat:=51, _
     Password:="", _
     CreateBackup:=False
     
     ActiveSheet.Shapes("Button 5").Delete
     ActiveSheet.Shapes("Button 3").Delete
     ActiveSheet.Shapes("CommandButton1").Delete
     ActiveSheet.Shapes("Button 2").Delete
     ActiveSheet.Shapes("Button 1").Delete
    
   ' Set the workbook variable to point to the new workbook
     Set Wkb = ActiveWorkbook
    
   ' Access the VBProject for the new workbook
     Set VBproj = Wkb.VBProject
     
   ' Get the code module for the active sheet of the new workbook
     Set VBcomp = VBproj.VBComponents(ActiveSheet.Name)
    
   ' Delete all the macro code
     With VBcomp.CodeModule
       .DeleteLines 1, .CountOfLines
     End With
     
   ' Save the changes
     Wkb.Save SaveChanges:=True
    
End Sub

Thanks!!

I will give it a try :D

jwswks

Hello jwswks,

I am confused by this post as it is different from the previous one. Perhaps if you explain what you are doing, I can write a macro to accommodate your needs.

Hi Leith,

Sorry that I confuse you.

I actually have a excel sheet containing customer information.

The purpose of the program is to let user to:
1) Search for records according to 1 or more filter keywords that is selected by the user.
2) Display the searched records on "Records" sheet
3) Have a button on "Records" sheet to allow user to save them in a new workbook in the event they need to pass the information around.

Im not actually using macros(I think thats what confusing?) but rather using just VB editor to code the whole project.

I have completed 1 & 2 and the code above is for 3.

The above code is written in "Records" worksheet

After a copy of the worksheet (which appear as a new workbook), I deleted the buttons and the 1st row in the new workbook so that it would appear like a normal excel file.(As I place the button on the 1st row of "Records" Sheet)

Just that the codes in the sheet get copied as well and when I tried to open it again, the macros security prompt shows up.

So now I am trying to delete the codes in the new workbook, to make it look like a normal excel file to prevent the prompt from showing up.

Sorry again if I confuse you as sometimes my brain starts running around when I try to think things through

PS: Flow would be like

1) User select keywords
2) Display records on "Records" Sheet
3) User click save button on "Records" Sheet
4) Sheet copied to new workbook
5) User save the new workbook

Hi Guys,

After searching the net for some solutions, managed to get it to work by declaring it as

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule

But the prompt for the new workbook still appears though

Hello jwswks,

If the prompt is still appearing Then not all of the modules are clear of text. Check each sheet module and this workbook of the copied workbook and look for any text in them. Even an empty Sub procedure will still trigger the macro warning.

You are using macros.

Do you intend protecting the VBA project/ If you do then the code to delete the code won't work.

I would simply copy the results to Word or a pdf