Is it possible to delete macros using a macro?

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
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?
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 13, 2005
Messages
467
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Why not as I suggested just save the data?

How are you creating the pricelist?
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
The code that automatically runs when the file is open is
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,846
Messages
5,598,425
Members
414,237
Latest member
tereres

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top