Disable save controls

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
532
I have a workbook file that I have saved with the word "Template" in the title. I have a set of steps the user completes, and then a save macro that will change the name and save the file in the intended file location. I would like to disable all save controls until the save macro runs and then have the last step of the save macro be to call the macro that restores all the save controls. This would be the Ctrl+S, File menu Save and Save As, F12 key, and ribbon and quick access icons. Is there a way to disable all of these functions using the Workbook_Open macro, and then what is the code to restore them all? I would also like to have a message box appear notifying the user that the file cannot be saved unless the save button is used.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
313
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,
Thanks a lot that you uploaded your file.
I've checked your code and made changes to the two following methods: DOC_SAVE_AS and CREATE_SHORTCUT. Here I enclose the changes I've made. Paste them into your workbook and it should work now. There are comments inside of what I've changed.

Here are changes to the DOC_SAVE_AS
VBA Code:
'SNTI: Method Adjusted
Sub DOC_SAVE_AS()

'   Enables the 'Save' controls
    Sheets("Intro Page").Range("AA15").Value = "Enabled"
    SaveAUI = True

Application.ScreenUpdating = False
    
Dim Fname As String
Dim Fpath As String
Dim FSaveName As String
'Dim ValidPath As String

Fname = Sheets("Intro Page").Range("AA1").Value
Fpath = Sheets("Intro Page").Range("B20").Value
FSaveName = Fpath + Fname

If Sheets("Intro Page").Range("B20").Value = "" Then
    MsgBox "A save location has not been selected." & vbCrLf & "Select a folder before proceeding." & vbCrLf & vbCrLf & _
    "When The field next to File Path is filled, select the Disk icon again"
    
    Application.ScreenUpdating = True
    Call GetFolder_QC_sheets
        
Else
    Application.DisplayAlerts = False 'SNTI: Change - set application display alerts in order to override existing file withou asking user
    ActiveWorkbook.SaveAs Filename:=FSaveName, FileFormat:=52
    MsgBox "The file has been saved as: " & Fname & vbCrLf & _
            "In this location: " & Fpath
    Application.DisplayAlerts = True 'SNTI: Turn on back the application alerts

    Sheets("Electrical - Panel Assembly").Visible = True
    Sheets("Corrections").Visible = True
    Sheets("Intro Page").Activate
    
    Application.ScreenUpdating = True
End If

Call CREATE_SHORTCUT

Application.ScreenUpdating = True

End Sub

Here are changes to CREATE_SHORTCUT
Code:
'SNTI: Method adjusted
Sub CREATE_SHORTCUT()

Dim DTPath As String
Dim oWSH As Object
Dim oShortcut As Object
Dim sPathDeskTop As String
Dim MyFile$


DTPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Active Jobs\"

'SNTI: Change
If Not (Len(Dir(DTPath, vbDirectory)) > 0) Then MkDir DTPath

MyFile = DTPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name))

Set oWSH = CreateObject("WScript.Shell")

'Set oShortcut = oWSH.CreateShortCut(DTPath & "\" & _
'ActiveWorkbook.Name & ".lnk")
Set oShortcut = oWSH.CreateShortCut(MyFile & ".lnk")
'SNTI://end

With oShortcut
.TargetPath = ActiveWorkbook.FullName
.Save
End With
Set oWSH = Nothing


End Sub
 

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
313
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Hi, have you tried the code for thread #12. Is that what you need?
 

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
532
I have implemented the updates to the code and no issues so far. Thanks for the help. Sorry I didn't respond sooner, it took me a while to get back to that project.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,968
Messages
5,767,375
Members
425,409
Latest member
Whatisanexcel

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
Top