Need someone really clever...

[Gordon Cartwright]

Hi

Is their code that can change the name of a macro...

eg. I want an auto_run code to run only when a file is opened for the first
time. After that I want the auto_run disabled.

help

Gordon

[Dave Jaeger]

'keep this in its own module because it deletes a module

Sub auto_open()
Dim x As Object

' This is the code that I want to run once.
msgbox "You will only see this once"

Set x = Application.VBE.ActiveVBProject.VBComponents

' Delete the current module.
x.Remove VBComponent:=x.Item("Module1") 'be sure to properly name
your module...you don't want to delete the wrong one!!!

End Sub

HTH

DJ
Canada

"Gordon Cartwright" <gor...@gcartwright.com> wrote in message
news:a4o7ms$3t6$1...@paris.btinternet.com...

[Bob Flanagan]

Gordon, just write an entry to the registry indicating that the auto open
macro has run. Then the auto open macro can check to see if it has run and
if so, exit without running the remainder of its code. The following is an
example of modifying the registry via vba code:

Sub Put_Setting_In_Registry()
Dim InvoiceNo As Long
InvoiceNo = 133
'this puts the value in variable InvoiceNo into the registry and
'identifies it as "CurrentNo"
SaveSetting "XLInvoices", "Invoices", "CurrentNo", InvoiceNo
End Sub

Sub Get_Value_From_Register()
Dim InvoiceNo As Long
'the following returns the invoice number stored in the registry,
'or 1000 if no number stored

InvoiceNo = _
GetSetting("XLInvoices", "Invoices", "CurrentNo", 1000)
MsgBox InvoiceNo
End Sub

Sub Delete_Registry_Entry()
'this removes the registry entry
DeleteSetting "XLInvoices", "Invoices"
End Sub

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Gordon Cartwright" <gor...@gcartwright.com> wrote in message
news:a4o7ms$3t6$1...@paris.btinternet.com...

[Dave Jaeger]

I think you need to go to the VB editor and Insert a module. It will insert
a module named Module1.

Then put the code in this Module1 rather than in Sheet1 Module.

Hope this helps.

DJ
Canada

Hi Dave...

WOW..great reply...

Just need a hand configuring this code for my needs...the file it's for is
called Invoice.xls and currently has just one module in sheet1 for the
auto_run macro...

I'm uncertain which values are to replace 'x' if any...

Thanks in adavnce

Gordon
UK

"Dave Jaeger" <daver...@rogers.com> wrote in message
news:OTDlDc7tBHA.2528@tkmsftngp03...