Excel VBA macros to save then disable

[Loading]

I have created an excel spreadsheet to use as a template for documentation. I have locked the workbook from being changed, with the exception of the cells that need info entered. I then added a macro the counts up every time the file in opened, with the password protection being unlock then macro update and password protection enabled again. This is for my company's own internal document tracking. The problem I am running into is having the file auto save right after that macro executes and then have another macro disable the save feature only allowing the save as feature. I don't want the file to be accidentally overwritten.

also id like a macro to place a command button beside the file that can Save As with a standard excel format and uses cell data for file name if its possible.

This is what I have so far, I am very new to this.

 Private Sub Workbook_Open()
    ActiveSheet.Unprotect Password:="PassWord"
    
        Sheets(1).[I9] = Sheets(1).[I9] + 1
    
    ActiveSheet.Protect Password:="PassWord"
    
    ActiveWorkbook.Save
    
    End Sub

Thank You in advance for your time.

 

[Smitty]

Welcome to the Board!

If you save the workbook as a Template it will automatically force a Save As.

 

[Loading]

ok that worked for my save problem, and I made a macro to auto save over the original due to the counter need to be up to date. Although when I open it up I now get a message pop up that say file already exist do you want to save over it which I want the macro to do. is there any way to stop that?

Private Sub Workbook_Open()

ActiveSheet.Unprotect Password:="PassWord"

    Sheets(1).[I9] = Sheets(1).[I9] + 1

ActiveSheet.Protect Password:="PassWord"

    ChDir "C:\Users\blahblah\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\blahblah\Desktop\Detail Log Proto.xltm", FileFormat:= _
        xlOpenXMLTemplateMacroEnabled, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        

End Sub

 

[Smitty]

You can try Application.DisplayAlerts = False

But I've never tried it with a template workbook, so I don't know if it will suppress the message.

 

[Loading]

This is my code so far

Private Sub Workbook_Open()

ActiveSheet.Unprotect Password:="PassWord"

    Sheets(1).[I9] = Sheets(1).[I9] + 1

ActiveSheet.Protect Password:="PassWord"

Application.DisplayAlerts = False

    ChDir "C:\Users\BlahBlah\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\BlahBlah\Desktop\Quote Detail Log Proto.xltm", FileFormat:= _
        xlOpenXMLTemplateMacroEnabled, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

Application.DisplayAlerts = True

End Sub

the problem is now when save button or Ctrl+S is used it just saves overtop again, even though its a template format and should pop up a save as box.

my other road block is command button Save As using cells A1 , G9, and I9 as the file name. all the codes I have tried either don't work, or most likly I copied wrong/entered wrong info and just don't understand it at all.

 

[Smitty]

It is possible to save a Template over itself if you know its location, which it looks like you're doing.

As for using cell references for file name values that's easy:

Dim fName as String
fName = Range("A1").Value & " - " & Range("G9").Value & " - " & Range("I9").Value

The & " - " part assumes you want hyphens between them. Note that if one of those cells has a date you'll need to use a different format:

Format(Range("A1").Value, "mm-dd-yy")

Or something along those lines to remove the /.

 

[Loading]

so after lots of trial and error I had to come at this with a different approach and this is what I got

Private Sub Workbook_Open()

ActiveSheet.Unprotect Password:="PassWord"

    Sheets(1).[I9] = Sheets(1).[I9] + 1

ActiveSheet.Protect Password:="PassWord"
 
    MsgBox "Be sure to click the black and red box immediately after dismissing this message."
    Cancel = True


End Sub

that's for the main page for the ticker to update

then I have 2 command button on the page

Private Sub CommandButton1_Click()

ActiveSheet.Unprotect Password:="PassWord"

Application.DisplayAlerts = False

Dim fName As String
Dim FPath As String
 fName = Range("A1").Value & " " & Range("G9").Value & " " & Range("I9").Value
 FPath = "Y:\Engineering\Customer Projects\1 - Quote Log\Quote Detail Logs"
ThisWorkbook.SaveAs fileName:=FPath & "\" & fName, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

    ActiveSheet.Shapes.Range(Array("CommandButton2")).Select
    ActiveSheet.Shapes.Range(Array("CommandButton2", "CommandButton1")).Select
    Selection.Delete
    
ActiveSheet.Protect Password:="PassWord"

Application.DisplayAlerts = True
     
End Sub


Private Sub CommandButton2_Click()
'
' SaveAs1 Macro
'

'
Application.DisplayAlerts = False

    ChDir "Y:\Engineering\Customer Projects\1 - Quote Log\Quote Detail Logs"
    ActiveWorkbook.SaveAs fileName:= _
        "Y:\Engineering\Customer Projects\1 - Quote Log\Quote Detail Logs\Quote Detail Log.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
        
Application.DisplayAlerts = True

End Sub

Which seems to work ok but I had to modify the sheet some due to special characters in the file name, mainly a "#" and ":". Is the some way to add a code and have them removed when saving. Coding for that one is command button 1.

 

[Smitty]

Is the some way to add a code and have them removed when saving.

One you load the fName variable, you can use REPLACE to get rid of the illegal characters. See: MS Excel: REPLACE Function (VBA)