Is it possible to delete macros using a macro?

[Ste_Moore01]

Hi,

I have a series of macros in a workbook.

One of the macros saves the file and I was wondering is it possible to creat a macro that deleted all other macros before the file was saved?

 

[DominicB]

Good afternoon Ste_Moore01

Chip Pearson has written an extensive article about this very subject. You can read it here :

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

HTH

DominicB

 

[Norie]

Yes it is possible.

Check this link Programming To The Visual Basic Editor .

By the way have you considered just saving the data in the file?

The above link will help but there can be some security implications when using code on code.

 

[Ste_Moore01]

I was hoping to delete the code because I have input boxes running when ever you open my workbook.

When you open my workbook it runs a series of input boxes and then once it has finished it creates a pricelist from the data that was entered. After the pricelist has been made it is then saved using a unique filename (created depending on the data inputted) and it also prints out the pricelist.

The problem I have come across is that when I open the saved pricelist it brings up the input boxes again and repeats everything.

I was hoping that once the pricelist had been created the macros in the ThisWorkbook area would be deleted and that would stop the problem.

I have tested the code on the links you gave me and they don't work. It brings up an error message when I run the macro saying, "Run-time error '1004': Method 'VBProject' of object '_workbook' failed." that is when I try to delete a module and also when I try to delete code.

I've even set up the reference to the "Microsoft Visual Basic For Applications Extensibility 5.3" like it said.

Can anyone figure this out for me or suggest another way of doing this?

I'd be most grateful!

Thanks!

 

[Norie]

Why not as I suggested just save the data?

How are you creating the pricelist?

 

[Ste_Moore01]

The code that automatically runs when the file is open is

Private Sub Workbook_Open()

    Workbooks.Open Filename:="P:\Downloads\PRICEPACKS\Excel Stuff\pw.xls" 'Opens pw.xls for password
    Dim name As String
    name = Environ("username") 'Gets username of person using sheet
    Dim dateform As String
    dateform = Sheets("USER DETAILS").[B24] 'Gets formula for todays date
    Dim nameform
    nameform = Sheets("USER DETAILS").[B25] 'Gets formula to work out users real name
       
'Input box for Company Name, if nothing entered it loops
    Dim go_on1 As Boolean, mycount1 As String
    go_on1 = False
    Do While go_on1 = False
    Dim Company_Name As String
    Company_Name = InputBox("Please type in the Company Name", "Company Name")
    If Company_Name = "" Then
    Call Beeper1
    Beep
    MsgBox "You did not enter a company name!  Please try again!"
    Else: go_on1 = True
    Sheets("DETAILS ENTRY").[c1] = Company_Name
    End If
    Loop

'Input box for Town
    Dim Town As String
    Town = InputBox("Please type in the Company's Town/City if it is needed.", "Area")
    Sheets("DETAILS ENTRY").[c4] = Town
    
'Input box for Your Name
    Dim Your_Name As String
    Your_Name = InputBox("Your name is automatically put on the pricepack, if you wish to change the name then please input it here.", "Your Name")
    If Your_Name = "" Then
    Sheets("USER DETAILS").[B1] = name
    Sheets("DETAILS ENTRY").[c8] = nameform
    Else
    Sheets("DETAILS ENTRY").[c8] = Your_Name
    End If

'Input box for Pricelist date
    Dim Da_te As String
    Da_te = InputBox("What date do you want on this pricepack?", "Date")
    If Da_te = "" Then
    Sheets("DETAILS ENTRY").[k8] = dateform
    Else
    Sheets("DETAILS ENTRY").[k8] = Da_te
    End If

'Input box for the customers Sales Rep
    Dim Sales_Rep As String
    Sales_Rep = InputBox("Please type in the Sales Reps name.", "Sales Representative")
    Sheets("DETAILS ENTRY").[k1] = Sales_Rep
    
'Input box for Sales Reps tel. no.
    Dim Sales_Rep_Tel As String
    Sales_Rep_Tel = InputBox("Please type in the Sales Reps telephone number.", "Sales Representative Telephone Number")
    Sheets("DETAILS ENTRY").[k4] = Sales_Rep_Tel
    
'Input box for Account Number, if nothing entered it loops
    Dim go_on2 As Boolean, mycount2 As String
    go_on2 = False
    Do While go_on2 = False
    Dim Account_number As String
    Account_number = InputBox("Please type in the Customers Account Number or buying group.", "Customer Account Number/Buying Group")
    If Account_number = "" Then
    Call Beeper1
    Beep
    MsgBox "You did not enter an account number or buying group!  Please try again!"
    Else: go_on2 = True
    Sheets("DETAILS ENTRY").[k10] = Account_number
    End If
    Loop
    
'Input box to check if the company is based in Europe or the UK
    Dim Euro As String
    Euro = InputBox("Does this company deal with us in Euros?", "Euros?")
    Sheets("DETAILS ENTRY").[C6] = Euro
    
'Input box for Euro exchange rate
    Dim Exchange As String
    Exchange = InputBox("Please type in the Euro Exchange Rate.", "Exchange Rate")
    Sheets("DETAILS ENTRY").[k6] = Exchange
    
    Application.Calculate
    
    Call Pricepack

End Sub

It uses another series of code called "pricepack". The "Pricepack" code creates a filename and saves the file and I was hoping to add the "delete code" into it so that it deleted all the above code to make sure it didn't run again when re-opened.