On workbook open

menor59

Well-known Member
Joined
Oct 3, 2008
Messages
574
Office Version
  1. 2021
Platform
  1. Windows
Hello All I have a workbook Called Template....

What i want to do is when the User Opens the Work book....It should Prompt to say..."you are working on the template workbook...please enter a new Name...

The user enters a NEW Name...And a workbook from the template workbook is created from the users input, and is saved where ever the Template workbook resides...
Then it should close the template and open the New workbook (if possible)

When that reader Opens the new work book it should ignore the macro because its not the Template...

I hope this makes sence.

Thank you
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Maybe try something like this:
Code:
[COLOR=#0000ff]Private Sub [/COLOR]Workbook_Open()


[COLOR=#0000ff]    Dim [/COLOR]str [COLOR=#0000ff]As String[/COLOR]
    
    Application.DisplayAlerts =[COLOR=#0000ff] False[/COLOR] [COLOR=#008000]'Turn off Alerts[/COLOR]
    str = InputBox("Please Enter Workbook Name", "MyPrompt")
    [COLOR=#0000ff]If [/COLOR]str = vbNullString [COLOR=#0000ff]Then Exit Sub[/COLOR]
    Me.SaveAs "C:\Users\mmickle1\My Documents\" & str, 51[COLOR=#008000] 'Save file as .xlsx i.e. File Format 51[/COLOR]
    Application.DisplayAlerts = [COLOR=#0000ff]True[/COLOR] [COLOR=#008000]'Turn on Alerts[/COLOR]


[COLOR=#0000ff]End Sub[/COLOR]
 
Upvote 0
Assuming you've called the template "template.xlsm", try this:
Code:
Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Template. xlsm" then Application.Dialogs(xlDialogSaveAs).Show
If ActiveWorkbook.Name = "Template. xlsm" then ActiveWorkbook.Close (False)
End Sub
This checks to see if its the template. If it is, it displays the file save as window, so you can save it as something else. Because you've renamed it, the original isn't still open. The second line checks the name again - if its still the template, it means that you cancelled the save as, so it closes the template anyway.
If you need to edit the template later, you'll need to disable macros before opening it!
 
Upvote 0
Maybe try something like this:
Code:
[COLOR=#0000ff]Private Sub [/COLOR]Workbook_Open()


[COLOR=#0000ff]    Dim [/COLOR]str [COLOR=#0000ff]As String[/COLOR]
    
    Application.DisplayAlerts =[COLOR=#0000ff] False[/COLOR] [COLOR=#008000]'Turn off Alerts[/COLOR]
    str = InputBox("Please Enter Workbook Name", "MyPrompt")
    [COLOR=#0000ff]If [/COLOR]str = vbNullString [COLOR=#0000ff]Then Exit Sub[/COLOR]
    Me.SaveAs "C:\Users\mmickle1\My Documents\" & str, 51[COLOR=#008000] 'Save file as .xlsx i.e. File Format 51[/COLOR]
    Application.DisplayAlerts = [COLOR=#0000ff]True[/COLOR] [COLOR=#008000]'Turn on Alerts[/COLOR]


[COLOR=#0000ff]End Sub[/COLOR]

Thank you mike..

Question, If i didnt want it to save to a set location...i would want it to save where ever the Template workbook resides...
 
Upvote 0
Just use:

Code:
Me.SaveAs ThisWorkbook.Path & "\" & str, 51


What are the different code types...my workbook is a Xlsm

Thank you again Mike,

Also last thing...If this was to run....

Will it check the Workbook name to see if its named Template...if it is named template...run the macro...if not dont run the macro..
 
Upvote 0
What are the different code types...my workbook is a Xlsm

Thank you again Mike,

Also last thing...If this was to run....

Will it check the Workbook name to see if its named Template...if it is named template...run the macro...if not dont run the macro..

Use 52 for xlsm. A better option then a workbook open macro might be if you save the Workbook as an .xltm. Then the user can't overwrite it.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top