Required Selection to save or print
 

I have a form that has 3 radio button/check boxes. (Only 1 can be checked at
a time).

Is it possible to require a selection before someone can save or print the
spreadsheet?

Required Selection to save or print
 

Yes. Here's one way.

Disable the Save, SaveAs and Print Buttons

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
'disable Save(3) and SaveAs(748) and Print(4)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3)
Ctrl.Enabled = False
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=748)
Ctrl.Enabled = False
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=4)
Ctrl.Enabled = False
Next Ctrl
End sub

Then Reactivate them once either Button1, 2 or 3 has as value other
than FALSE (use the _CLICK event on each button)

Sub Button1_Click()
EnableControls
End Sub

Sub Button2_Click()
EnableControls
End Sub

Sub Button3_Click()
EnableControls
End Sub


Sub EnableControls()
'enable Save(3) and SaveAs(748) and Print(4)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=748)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=4)
Ctrl.Enabled = True
Next Ctrl
End sub

Required Selection to save or print
 

Consider adding this too:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'disable Save(3) and SaveAs(748) and Print(4)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=748)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=4)
Ctrl.Enabled = True
Next Ctrl
End sub

This will allow OTHER open workbooks to save and print

Required Selection to save or print
 

Does this get added as a seperate sub...or to the end of one of the other subs?

"Greg Glynn" wrote:

Consider adding this too:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
'disable Save(3) and SaveAs(748) and Print(4)
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=3)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=748)
Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=4)
Ctrl.Enabled = True
Next Ctrl
End sub

This will allow OTHER open workbooks to save and print

Required Selection to save or print
 

The 2 SUBs (WorkBook_WindowActivate and Workbook_WindowDeactivate) get
added to the WORKBOOK module

The three _CLICK SUBs are associated with your Radio Buttons

The EnableControls SUB you can create as a module.


Greg

Required Selection to save or print
 

I got it working...but now every spreadsheet I open has the Save/Save/Print
button disabled.

I put the "Private Sub Workbook_WindowActivate(ByVal Wn As Window)" in the
"This Workbook" item under Excel Objects...the EnableControls in a module in
that workbook only and assigned the _Click subs to the radio buttons.


"Greg Glynn" wrote:

The 2 SUBs (WorkBook_WindowActivate and Workbook_WindowDeactivate) get
added to the WORKBOOK module

The three _CLICK SUBs are associated with your Radio Buttons

The EnableControls SUB you can create as a module.


Greg