Macro to add data in a file (or alternative) 2

[robertsfd]

I want to create a macro in a spreadsheet template that, when the spreadsheet template is first opened, generates a time and date stamp and places it in a cell. I only want the macro to run when the file is first opened, thereafter the macro would be disabled.

1. Is it possible to disable the macro after running once (or twice, thrice, etc)?

2. Is there a way to do this without a macro? I want it to happen without whoever is opening the file being aware of it, and most have their security level such that macros are not enabled unless they say so.

Thanks in advance, Doug

 

[SkipVought]

Doug,

Run the macro from the Workbook_Open event.

Macros must be enabled. If the user disables macros, can't automatically do it.

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[DaleWatson123321]

Doug,

Here's a work-around you might want to consider...

1) In your Template file, have an "Introduction" sheet in which you describe the need to set their macro security level at "Medium", and to "Enable Macros" when opening your Template file. Phrase the wording such that they expect the macro-enabling is required in order to "activate" the Template sheet.

2) Hide the Template sheet, and on the Introduction sheet, place a macro button, which when clicked, will:
a) Un-Hide the Template sheet, and
b) Place your the date/time stamp in a concealed cell - perhaps on another hidden sheet.

3) You'll want to password-protect where necessary, including the VBA Module.

Hope this helps.

Regards, Dale Watson

 

[robertsfd]

OK, these are good ideas. Thanks. But still...

Is it possible to disable the macro after running once (or twice, thrice, etc)? In other words, I do not want it to run except the first time it is opened - is this possible?

As a workaround, I guess I could have it insert the date/time stamp in an adjacent cell - although I do not need a history of all worksheet openings.

 

[SkipVought]

Sure. After it runs, write a value in some hidden sheet cell and never allow the macro to complete, if that value is present.

Sub Your Macro
If [TheDreddedValue] then Exit Sub
'now your run-once code
[TheDreddedValue] = TRUE
End Sub
/code]


Skip,
[sub]
[glasses] [b][red]Sign above the facsimile apparatus at the music publisher:[/red][/b]
If it ain't baroque...
[b]Don't FAX it![/b][tongue][/sub]

 

[DaleWatson123321]

Thanks for the assist, Skip.

As Skip knows, I'm quite busy these days, so don't get much time to spend on Tek-Tips.

Having written the following, I thought I should offer it.

In "ThisWorkbook", copy this:

Private Sub Workbook_Open()
    If [date_cell] = "" Then
        GetDate_UnHideTemplate
    End If
End Sub

... where "date_cell" is a range name you assign to a cell - in a hidden sheet if you like.

In a Module, copy this:

Sub GetDate_UnHideTemplate()
    If [date_cell] = "" Then
        ActiveWorkbook.Unprotect "123"
        [date_cell] = Now()
        Worksheets("Template").Visible = True
        ActiveWorkbook.Protect "123"
        ActiveWorkbook.Save
    End If
End Sub

You'll need to name your Template sheet "Template", or change the reference in the above code.

Naturally also change the password to your preference.

The "ActiveWorkbook.Save" is used to force the file to be saved. If the user were to open the file and NOT save, then the Workbook_Open event would "trigger" the next time the file is opened (because the "date_cell" would still be BLANK.

Also, you should password protect "ThisWorkbook" and your Module. Right-click, and select "VBAProject Properties".

Hope this helps. Have to run. If you have any further needs, I'm sure Skip will help you out. Hi Skip. ;-)

Regards, Dale Watson

 

[robertsfd]

Thanks so much. These both indeed help.