On workbook open

[menor59]

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

 

[mrmmickle1]

Maybe try something like this:

[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]

 

[Trevor_S]

Assuming you've called the template "template.xlsm", try this:

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!

 

[menor59]

Maybe try something like this:

[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...

 

[mrmmickle1]

Just use:

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

 

[menor59]

Just use:

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..

 

[mrmmickle1]

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.