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

hi m,

Here's an example with two possible (untested!) approaches with one
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


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: 333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194201

http://www.thecodecage.com/forumz

Broro 183,

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


"broro183" wrote in message
...

hi m,

Here's an example with two possible (untested!) approaches with one
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


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: 333
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=194201

http://www.thecodecage.com/forumz

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

Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: 333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194201

http://www.thecodecage.com/forumz