auto run a macro

[macroman]

Hello,

I wanted to create a macro to generate a unique number that can be used as a
"qute number".

My spreadsheet has a "now function" in it. My macro coverts this function
to a number format with four decimal points. So everytime I open the
spreadsheet it generates a new number.

I want the macro to work in the quote spreadsheet but be disabled once the
file has been saved to a new name.
I thought this would happen if I saved the macro in the "this workbook" but
when I save the file to a new name then close all workbooks and open the
saved file the quote number changes which does not help me.

I am using version 2003 with Office XP Professional

Is there a way to make this macro work on just the one spreadsheet?

thank you for your time

m

 

[broro183]

hi m,

Here's an example with two possible (untested!) approaches with on
commented out - nb, you'll have to change the strings as needed...



VBA Code:
--------------------


Option Explicit
Private Sub Workbook_Open()
With ThisWorkbook
' If .FullName = "C:\Users\Robert\Documents\Excel\Excel Forum.com\Blah.xls" Then
If .Name = "Blah.xls" Then
'change quote number
With .ActiveSheet.Range("a1")
.FormulaR1C1 = "=NOW()"
.Calculate
.Value = .Value
.NumberFormat = "0.0000"
End With
Else
'do nothing
End If
End With
End Sub
--------------------




hth
Rob

 

[macroman]

Broro 183,

thank you for your help with this. Your approach worked perfectly using the
full name approach.

 

[broro183]

Thanks for the feedback - I'm pleased I could help

Rob