Disable all saving except...

I have pieced together codes and made a form that prompts the user for their name, location, and current date. After inputting this information, the user presses a control button that saves the file based on the inputs.

I would like to disable the ability to save this file unless the user uses my form to do so. Is this possible?

How would I disable save when the file opens, enable save when the user opens my "Save Form", and disable save if the form is closed prematurely? Or is there a better way?

Looking for some help. Thanks!

Hello Sowetoddid,

Here is some code to accomplish your task. This disables all "Saves" . To re-enable the "Saves" on the menu, change "False" in the code back to "True". You can place this in the Form_Activate() event for your user form. Re-enable the "Saves" when the Form is Deactivated and when the Workbook is Closed.

Disable Saves on Menus:

Dim CmdBar1 As CommandBar
Dim CmdBar2 As CommandBar

Set CmdBar1 = Excel.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar

For I = 1 To CmdBar1.Controls.Count
CtrlName = CmdBar1.Controls(I).Caption
If CtrlName = "&Save" Or Left(CtrlName, 4) = "Save" Then
CmdBar1.Controls(I).Enabled = False
End If
Next I

Set CmdBar2 = Excel.CommandBars("Standard")
CmdBar2.Controls("Save").Enabled = False

Sincerely,
Leith Ross

Thank you Leigh > I will give it a try!

Thanks, Leith....Another question please__

I pasted the following under "Workbook" in VB....

Private Sub Workbook_Open()

    Dim CmdBar1 As CommandBar
    Dim CmdBar2 As CommandBar
    
    Set CmdBar1 = Excel.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar
    
    For I = 1 To CmdBar1.Controls.Count
    CtrlName = CmdBar1.Controls(I).Caption
    If CtrlName = "&Save" Or Left(CtrlName, 4) = "Save" Then
    CmdBar1.Controls.Enabled  = False
    End If
    Next I
    
    Set CmdBar2 = Excel.CommandBars("Standard")
    CmdBar2.Controls("Save").Enabled = False
    
End Sub

When I save, and re-open the workbook, an error comes up saying

"Compile Error: Method or Data Member Not Found" and it highlights .Enabled in the code.


I pasted the code in the open event because I want the save function to be false the minute the workbook opens. When the user wants to save, he will click a command button that opens the userform. He will complete the entries and press the save button on the form. The minute the userform opens, all save functions should be re-enabled. The minute the userform closes, the save functions would be disabled again. How would you disable save when the workbook opens?

Hello Sowetoddid,

I apologize for my typing skill or lack of. The line should read...

Code Correction:
CmdBar1.Controls(I).Enabled = False

Sorry about that,
Leith Ross

The code to disable saving worked perfectly when I place it under Workbook_Open! Maybe you can help me decipher the problem in the rest of my userform code. The main purpose of the form is to request user input and then save the workbook based on the values of those inputs. When I run a test to see if the input boxes are empty, a message box should warn the user if they are empty. Instead, the form does nothing.

My other issues are in the comments of the code. If I said it "should...", that basically means I couldn't get it to work.

MANY THANKS!!!

Private Sub CommandButton2_Click()
    Dim N, A, M, Y, Msg, Style
    N = TextBox1.Value
    A = Trim(OpArea.Value)
    M = Trim(Month.Value)
    Y = Trim(Year.Value)
    
 'Should display an message box if any of the input boxes are empty
    If TextBox1.Value = "" Or OpArea.Value = "" Or Month.Value = "" Or Year.Value = "" Then
       Msg = "Please fill in each box before saving"
       Style = vbOKOnly
 'Saves the Workbook with the input box entries and closes the form.
    Else
       ActiveWorkbook.SaveAs N & "_" & A & "_" & M & "_" & Y
       UserForm1.Hide
    End If
    
 'Should disable the save option if the "x" is clicked to close the form or if the userform hides
    Dim CmdBar1 As CommandBar
    Dim CmdBar2 As CommandBar
    
    Set CmdBar1 = Excel.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar
    
    For I = 1 To CmdBar1.Controls.Count
    CtrlName = CmdBar1.Controls(I).Caption
    If CtrlName = "&Save" Or Left(CtrlName, 4) = "Save" Then
    CmdBar1.Controls(I).Enabled = False
    End If
    Next I
    
    Set CmdBar2 = Excel.CommandBars("Standard")
    CmdBar2.Controls("Save").Enabled = False
    
End Sub
'Tied to j-walk code for displaying a file browser
Private Sub CommandButton3_Click()
    Dim Msg As String
    Msg = "Please select a location for the backup."
    MsgBox GetDirectory(Msg)
End Sub
'Should enable the save option when the form is opened, allowing CommandButton2 to save the form
Private Sub UserForm_Activate()

    Dim CmdBar1 As CommandBar
    Dim CmdBar2 As CommandBar
    
    Set CmdBar1 = Excel.CommandBars("Worksheet Menu Bar").Controls("File").CommandBar
    
    For I = 1 To CmdBar1.Controls.Count
    CtrlName = CmdBar1.Controls(I).Caption
    If CtrlName = "&Save" Or Left(CtrlName, 4) = "Save" Then
    CmdBar1.Controls(I).Enabled = True
    End If
    Next I
    
    Set CmdBar2 = Excel.CommandBars("Standard")
    CmdBar2.Controls("Save").Enabled = True
    
'Establishes values for the combo boxes
    With OpArea
        .AddItem "AnadarkoOK"
        .AddItem "AnadarkoTX"
        .AddItem "ETXSouth"
        .AddItem "ETXNorth"
        .AddItem "Gloria"
        .AddItem "NTXEast"
        .AddItem "NTXWest"
        .AddItem "MidLa"
        .AddItem "Mississippi"
    With Month
        .AddItem "January"
        .AddItem "February"
        .AddItem "March"
        .AddItem "April"
        .AddItem "May"
        .AddItem "June"
        .AddItem "July"
        .AddItem "August"
        .AddItem "September"
        .AddItem "October"
        .AddItem "November"
        .AddItem "December"
    With Year
        .AddItem "2005"
        .AddItem "2006"
        .AddItem "2007"
        .AddItem "2008"
        .AddItem "2009"
        .AddItem "2010"
        .AddItem "2011"
        .AddItem "2012"
        .AddItem "2013"
        .AddItem "2014"
        .AddItem "2015"
        .AddItem "2016"
        .AddItem "2017"
        .AddItem "2018"
        .AddItem "2019"
        .AddItem "2020"
        
    End With
    End With
    End With
    
End Sub