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

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

--
Jay


"Mike" wrote:

> 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

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
"Jay" wrote:

> 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
>
> --
> Jay
>
>
> "Mike" wrote:
>
> > 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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Jay" <(E-Mail Removed)> wrote in message
news:3DBCD41F-8DFA-40FD-A952-(E-Mail Removed)...
> 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
>
> --
> Jay
>
>
> "Mike" wrote:
>
>> 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

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

--
Jay


"Mike" wrote:

> 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
> "Jay" wrote:
>
> > 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
> >
> > --
> > Jay
> >
> >
> > "Mike" wrote:
> >
> > > 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

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).
--
Jay


"Mike" wrote:

> 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
> "Jay" wrote:
>
> > 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
> >
> > --
> > Jay
> >
> >
> > "Mike" wrote:
> >
> > > 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

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

"Jay" wrote:

> 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).
> --
> Jay
>
>
> "Mike" wrote:
>
> > 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
> > "Jay" wrote:
> >
> > > 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
> > >
> > > --
> > > Jay
> > >
> > >
> > > "Mike" wrote:
> > >
> > > > 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

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

--
Jay


"Mike" wrote:

> 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
>
> "Jay" wrote:
>
> > 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).
> > --
> > Jay
> >
> >
> > "Mike" wrote:
> >
> > > 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
> > > "Jay" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Jay
> > > >
> > > >
> > > > "Mike" wrote:
> > > >
> > > > > 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

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

"Mike" <(E-Mail Removed)> wrote in message
news:1F52C920-E7D7-4C5C-8218-(E-Mail Removed)...
> 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
>
> "Jay" wrote:
>
> > 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).
> > --
> > Jay
> >
> >
> > "Mike" wrote:
> >
> > > 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
> > > "Jay" wrote:
> > >
> > > > 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
> > > >
> > > > --
> > > > Jay
> > > >
> > > >
> > > > "Mike" wrote:
> > > >
> > > > > 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

Thank you everyone who replied to my post

"Jay" wrote:

> 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
>
> --
> Jay
>
>
> "Mike" wrote:
>
> > 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
> >
> > "Jay" wrote:
> >
> > > 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).
> > > --
> > > Jay
> > >
> > >
> > > "Mike" wrote:
> > >
> > > > 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
> > > > "Jay" wrote:
> > > >
> > > > > 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
> > > > >
> > > > > --
> > > > > Jay
> > > > >
> > > > >
> > > > > "Mike" wrote:
> > > > >
> > > > > > 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