CommandButtons and OptionButtons on Userform

[Guest]

Hello

I am having trouble with a Select Case Statement. I have about 6 reports
that I would like one command button to preveiw based on which Optionbutton
is
Selected. This is a code that I use in Access but not having any luck in
excel
can someone tell if this can been done and how

Thanks



Private Sub cmdPrint_Click()

Dim strWhere As String

'strWhere = (DatePicker?)
On Error GoTo PrintPreviewError
Select Case Me.ReportsGroup

Case 1
DoCmd.OpenReport "Payroll-Full Time", acViewPreview, , strWhere
DoCmd.Maximize
Case 2
DoCmd.OpenReport "Payroll-Part-time", acViewPreview, , strWhere
DoCmd.Maximize

Case Else
MsgBox "Select A Report!!!!!!!"
End Select

PPNormalExit:
Exit Sub

PrintPreviewError:

Resume PPNormalExit
Exit Sub

End Sub

 

[Guest]

Hi Mike -

The problem stems from the fact that Excel handles the value of grouped
optionsbuttons differently than Access. In Access, the grouped option
buttons are automatically assigned an incremental value and the value of the
selected button is passed to the group (as its value). Excel doesn't do this
overhead bookkeeping for you. The only thing you get by grouping
optionbuttons in Excel is mutual exclusivity (can only select one of the
group). You have to manually evaluate the buttons to see which is selected.

Here is one approach to this evaluation demonstrated by modifying your
original code to branch properly in the Select Case statement.

Private Sub cmdPrint_Click()
Dim oBtn As Object
Dim strWhere As String

'strWhere = (DatePicker?)
On Error GoTo PrintPreviewError
i = 0
For Each oBtn In Me.Controls
If TypeName(oBtn) = "OptionButton" Then
If oBtn.Value = True Then i = i + 1: Exit For
End If
Next 'oBtn

Select Case i

Case 1
MsgBox "Option Button 1 selected"

' DoCmd.OpenReport "Payroll-Full Time", acViewPreview, , strWhere
' DoCmd.Maximize
Case 2
MsgBox "Option Button 2 selected"

' DoCmd.OpenReport "Payroll-Part-time", acViewPreview, , strWhere
' DoCmd.Maximize

Case Else
MsgBox "Select A Report!!!!!!!"
End Select

PPNormalExit:
Exit Sub

PrintPreviewError:

Resume PPNormalExit
Exit Sub

End Sub

 

[Guest]

Jay

I have tested your code but if i select Optionbutton2 the MsgBox still says
MsgBox "Option Button 1 selected" now if i don't select any MsgBox says
MsgBox "Select A Report!!!!!!!"

Any more suggestions

 

[Jon Peltier]

You could also use this, which is a bit less cumbersome, and doesn't depend
on the order of the controls:

Select Case True
Case optButton1.Value
' optButton1 selected
Case optButton2.Value
' optButton2 selected
End Select

- Jon

 

[Guest]

Sorry Mike -

Here's a working version. Sorry for the inconvenience.

Private Sub cmdPrint_Click()
Dim oBtn As Object

On Error GoTo subError
oBtnidx = 0: i = 0
For Each oBtn In Me.Controls
If TypeName(oBtn) = "OptionButton" Then
oBtnidx = oBtnidx + 1
If oBtn.Value = True Then i = oBtnidx: Exit For
End If
Next 'oBtn

Select Case i
Case 1
MsgBox "Option Button 1 selected"
Case 2
MsgBox "Option Button 2 selected"
Case Else
MsgBox "No Option Button selected..."
End Select

NormalExit:
Exit Sub

subError:
Resume NormalExit

End Sub

 

[Guest]

Mike -

I agree with Jon and suggest that you heed the advice given in his post.
His is a more fault-resistant (fault-proof) approach to referencing and
evaluating option buttons. As Jon describes, our current approach relies on
the proper order of option button names which can be out of sequence when
many edits have been made during form construction.

If you are committed to the logic in the current approach for a some reason,
just make sure each optionbutton is named in sequence. Using the property
sheet, check their names and arrange them in order (OptionButton1,
OptionButton2, etc, to synchronize with your screen labels and your Select
Case statement).

 

[Guest]

Jay,
I did try using his approch but failed not sure where but did I would like
to use
but not sure where Im going wrong
Think you could help

 

[Guest]

Hi Mike -

Here's an example of Jon's approach with your original code stripped out and
some dummy names for the reports:

Private Sub cmdPrint_Click()
'Using Jon Peltier's suggestions

'On your form's option group, check that OptionButton1 is associated
'with the "Invoices Report" label, OptionButton2 is associated with
'the "Profits Report" label, and so on.

Select Case True
Case Me.OptionButton1.Value
MsgBox "Invoices Report selected"
Case Me.OptionButton2.Value
MsgBox "Profits Report selected"
Case Else
MsgBox "No Report selected..."
End Select

End Sub

 

[Peter T]

Do you have groups of optionbuttons, eg GroupName's, in frames or
multipage's. IOW is it possible you have two or more optionbuttons on your
form checked.

Regards,
Peter T

 

[Guest]

Thank you everyone who replied to my post

Hi Mike -

Here's an example of Jon's approach with your original code stripped out and
some dummy names for the reports:

Private Sub cmdPrint_Click()
'Using Jon Peltier's suggestions

'On your form's option group, check that OptionButton1 is associated
'with the "Invoices Report" label, OptionButton2 is associated with
'the "Profits Report" label, and so on.

Select Case True
Case Me.OptionButton1.Value
MsgBox "Invoices Report selected"
Case Me.OptionButton2.Value
MsgBox "Profits Report selected"
Case Else
MsgBox "No Report selected..."
End Select

End Sub