Disable all saving except...

[sowetoddid]

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!

[Leith Ross]

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

[sowetoddid]

Thank you Leigh > I will give it a try!

[sowetoddid]

Thanks, Leith....Another question please__

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

Please Login or Register  to view this content.

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?

[Leith Ross]

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

[sowetoddid]

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

Please Login or Register  to view this content.

[Leith Ross]

Hello Sowetoddid,

There a couple of things I noticed in your CommandButton2 code that are causing you problems. ComboBoxes can be tricky. The Text and Value properties do different things. Text is what is displayed and Value is the row that the user has selected. You should be using Text and not Value in this instance. The other problem is the disable "Save" should be placed in the UserForm_Terminate event not in the CommandButton2_Click event.
_________________________________________________________________
Code Changes:

Private Sub CommandButton2_Click()
Dim N, A, M, Y, Msg, Style
N = Trim(TextBox1.Text)
A = Trim(OpArea.Text)
M = Trim(Month.Text)
Y = Trim(Year.Text)

'Should display an message box if any of the input boxes are empty
If N = "" Or A = "" Or M = "" Or Y = "" 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

End Sub
_________________________________________________________________

Private Sub Userform1_Terminate()

'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
_________________________________________________________________

This should help. Let me know what happens.
Leith Ross

[sowetoddid]

Thanks, Leith. Unfortunately, I had no luck.....my code is now as follows.

Please Login or Register  to view this content.

In the code, "N" represents a text box entry and the other three variables are comboboxes. If I fill in all four entries, and click save, the "Else" statement successfully runs and the file is saved. However, if I neglect to put any inputs and click the save control button, nothing happens.


As for the "disable save" event, the excel's save abilities are inactive (grey) when the file opens and when the form opens, they remain grey. However, if I save the file using the userform, the userform hides after the save and all excel save abilities are suddenly active again.

Any ideas as to what is going on here?

[Leith Ross]

Hello Sowetoddid,

To dispaly a message you need to use the MsgBox function. You defined your message string, but forgot the MsgBox function. I have added it for you. It also gives the user the option of exiting the routine without saving the file. As for the disabling and re-enabling of Save, I am not sure what the sequence is. Could you clarify this?

Private Sub CommandButton2_Click()
Dim N, A, M, Y, Msg, Style, RetVal
N = Trim(TextBox1.Text)
A = Trim(OpArea.Text)
M = Trim(Month.Text)
Y = Trim(Year.Text)
'Display message box if any of the input boxes are empty
If N = "" Or A = "" Or M = "" Or Y = "" Then
Msg = "Please fill in each box before saving." & vbCrLf _
&"To Exit and Not Save, click Cancel."
RetVal = MsgBox(Msg, vbExclamation + vbOKCancel)
If RetVal = vbCancel Then Exit Sub
Style = vbOKOnly
Else
ActiveWorkbook.SaveAs N & "_" & A & "_" & M & "_" & Y
UserForm1.Hide
End If
End Sub

Sincerely,
Leith Ross

[sowetoddid]

Sure, Leith

The sequence for activating/deactivating save is ...

The user should never be allowed to save the file EXCEPT

if the user clicks on the "Save" control button that I have created. This control button opens userform1, which is basically a form that directs the user on how to name the file. Then, there is a save button on this form that will actually save the file.

I want to have excel's save abilities to be deactivated all of the time, but I thought if they were not activated when the user tried to saved via userform1 that userform1 would actually not work in saving the file. Is this true, or am I misinterpreting the save-disabling module?

My thinking is that I must disable all file saving unless the user does so through my userform. If they prematurely close the userform before saving, then save should be disabled again.

I may be taking the long route to do what I want...which is force the user to save with userform1 and nothing else.

[sowetoddid]

I was trying to make it harder than it needed to be....

I removed the activation/deactivation based on what the userform was doing.


Leith, instead of doing what I was doing, I put your deactivation of save code in the workbook open module. That was all it took. My "save" userform still works!


Thank you, thank you for you persistent help, Leith.