How best to extend user's options?

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

I currently use the following code to ask user if they wish
to print pages that have been previously totalled as '0.00'.

'Ask user if they want to print zero pages
Msg = "Do you want to print pages that total 0.00" & _
vbNewLine & _
"in the worksheet: " & ActiveSheet.Name & " ?"
Style = vbYesNo + vbDefaultButton2
Title = ActiveWorkbook.Name
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
PrintZeroPages = True
Else '''probably redundant since DefaultButton2 ?
PrintZeroPages = False
End If

A later test on 'PrintZeroPages' will decide whether to
exclude a particular range from the print routine, or not.

However, if the sheet has NOT been previously
calculated, then the particular cells in question will not
hold '0.00'......they will be Empty, and therefore these
empty ranges will also print.

What I'd like to ask user is:

i) Do you want to skip pages totalling '0.00'
ii) Do you want to skip pages where the total is Empty
iii) Do you want to skip both '0.00' and Empty

How can I best construct this, please?

Regards


How best to achieve the following, please:?

Regards.
 
Stuart,
Use three option buttons in a frame on a userform with two buttons - OK,
Cancel. See working sample code below. (I built it in the affirmative. You
can just as well turn it around to skip one or the other or both.)

'---------------------------------------
'Put this in a standard module
Option Explicit
Public mblnStartedWithMain As Boolean

Sub Main()
mblnStartedWithMain = True
Load UserForm1
With UserForm1
.Tag = ""
Call .Show
If .Tag = "ok" Then
Select Case True
Case .optZeros.Value
MsgBox "Do zeros"
'call zeros routine here
Case .optEmpties.Value
MsgBox "Do empties"
'call empties routine here
Case .optBoth.Value
MsgBox "Do both"
'call both here
End Select
Else
'user canceled
End If
End With
Unload UserForm1
End Sub

'---------------------------------------
'Put this in userform module.
'Expects three option buttons
'named optZeros, optEmpties, optBoth *in a frame*
'on a userform named UserForm1
'with two command buttons named
'cmdOK and cmdCancel.

Option Explicit

Private Sub cmdCancel_Click()
Me.Hide
Me.Tag = "cancel"
End Sub

Private Sub cmdOK_Click()
Me.Hide
Me.Tag = "ok"
End Sub

Private Sub UserForm_Activate()
If Not mblnStartedWithMain Then
Unload Me
MsgBox "Start program using Sub Main"
Else
optBoth.Value = True
End If
End Sub
 
Many thanks.

I've set it up so that it pops up for each worksheet, for now.
Say first time through, I choose the option to print both
zero and blank pages. Next time through when the form is
displayed, that same option is already selected, even
though I unload the form after each use.

Am I right to believe that unloading the form should clear
user choices?

The other thing is that I can't find a way to unselect, such
that I can use Ok to exit the form when no options are
selected.

Could you help a little further, please?

Regards.
 
Stuart,
Am I right to believe that unloading the form should clear
user choices?
Click to expand...

Yes, but.please keep in mind that UserForm1.Hide will make UserForm1
disappear from view but not unload it from computer memory. Hiding the form
and leaving it memory allows your code to use the values of controls on the
form even though the user thinks of it as closed. If you then call
UserForm1.Show, you reopen the same form in the state it was in when it was
hidden. Notice in my example, I hide the form regardless of whether you
click OK or Cancel. I only actually unload it at the end of Sub Main.

Unload UserForm1 both hides the form and removes it from memory. It is
sometimes said that the form is "destroyed". You should call Unload for your
form when your application is finished with that "instance' of the form. If
the form is reappearing with the same selections as previously, either the
form was hidden but not unloaded or the selection is getting set in code.
Notice in my example that I set optBoth.Value = True each time the form
activates.
The other thing is that I can't find a way to unselect, such
that I can use Ok to exit the form when no options are
selected.
Click to expand...

I am not clear about what you mean here. Option buttons are designed so that
one or the other of a set (in a frame) are always selected, like buttons on
an old car radio. A checkbox control is designed to be checked or not
independently of its neighbors. If this is the behaviour you prefer, use
checkboxes instead of option buttons. Also, in my example, when you click
the Cancel button, the code just ignores the selections so it doesn't matter
which option is selected.
 
I do UnLoad the form after I loop through each sheet, but since I have set
the selection in code, I will try resetting before the next worksheet in the
loop.

Thanks also for the explanation regarding Option Buttons/CheckBoxes.

Thanks again for your help.

Regards.
 
Back
Top