Change from Check box to Option button

[Mel]

Hello Excel Experts

Someone gave me the following code and I have been trying
to convert it so instead of using Checkboxes I can use
Optionbuttons instead. I have tried everything....please
help (im losing my rag here!!!)

Sub Select_Sheets()

Application.ScreenUpdating = False
Set oWb = Workbooks.Open(fname)

'Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0

'Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)

'Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13

End If
Next i

'Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

'Set dialog height, width, and caption
With PrintDlg.DialogFrame
..Height = Application.Max(68, PrintDlg.DialogFrame.Top +
TopPos - 34)
..Width = 230
..Text = "Please Select Only One Sheet and Click Select:"
..Caption = "Select Sheet to Import"
End With

'Change tab order of OK and Cancel buttons
'so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

'Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
osh = cb.Caption
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

'Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

'Reactivate original sheet
CurrentSheet.Activate

End Sub

 

[Tom Ogilvy]

Sub Select_Sheets()

Application.ScreenUpdating = False
Set oWb = Workbooks.Open(fname)

'Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0

'Add the Optionbuttons
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)

'Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13

End If
Next i

'Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

'Set dialog height, width, and caption
With PrintDlg.DialogFrame
..Height = Application.Max(68, _
PrintDlg.DialogFrame.Top + TopPos - 34)
..Width = 230
..Text = "Please Select Only One Sheet and Click Select:"
..Caption = "Select Sheet to Import"
End With

'Change tab order of OK and Cancel buttons
'so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

'Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.OptionButtons
If cb.Value = xlOn Then
osh = cb.Caption
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If

'Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

'Reactivate original sheet
CurrentSheet.Activate

End Sub

 

[Mel]

Hiya

Thanks for your post Tom but there seems to be a problem,
when I run the macro it comes up with the error
message "TYPE MISMATCH" and when i click Debug it goes to
the line
For Each cb In PrintDlg.OptionButtons
Do you know why it is doing this? I noticed the for each
cb loop - could this be it?

Thanks

 

[Tom Ogilvy]

The code as posted works fine - note that you had no DIM statements in the
code you posted. If you have a declaration

Dim cb as Checkbox

then that would cause the problem you stated. You would need to change it
to

Dim cb as OptionButton

if you put the code in a new workbook with no changes, it runs as posted.
(tested in xl2000)

 

[Bob Phillips]

Mel,

I also tried it. As Tom says, because of no Dims I had to remove my Option
Explicit but then it ran fine.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)