auto run a macro
in [Programming]
|
Prev: change colors in a range of excel cells based on another range of cells
Next: Code to make a Spinner button work with Filters on a Pivot Table
From: macroman on 8 Apr 2010 20:58 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
From: broro183 on 8 Apr 2010 21:48 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/showthread.php?t=194201 http://www.thecodecage.com/forumz
From: macroman on 9 Apr 2010 09:59 Broro 183, thank you for your help with this. Your approach worked perfectly using the full name approach. "broro183" <broro183.494oqi(a)thecodecage.com> wrote in message news:broro183.494oqi(a)thecodecage.com... > > 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/showthread.php?t=194201 > > http://www.thecodecage.com/forumz >
From: broro183 on 9 Apr 2010 18:28
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/showthread.php?t=194201 http://www.thecodecage.com/forumz |