Vb Application to locate a worksheet depending on the radio box selected
 

I need to know the code for a vb application that will pop up upon
opening a workbook and ask you to select a radio button and go to a
worksheet depending on your selection. Any ideas? Thanks

Vb Application to locate a worksheet depending on the radio box selected
 

Shawn,

Here is some code to go into ThisWorkbook code module

Private Sub Workbook_Open()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim ob As OptionButton

Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' 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 sheets
SheetCount = SheetCount + 1
PrintDlg.OptionButtons.Add 78, TopPos, 150, 16.5
PrintDlg.OptionButtons(SheetCount).text = _
CurrentSheet.Name
If Worksheets(i).Visible < xlSheetVisible Then
PrintDlg.OptionButtons(SheetCount).Value = True
End If
TopPos = TopPos + 13
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
.Caption = "Select sheets to print"
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 ob In PrintDlg.OptionButtons
If ob.Value = xlOn Then
Sheets(ob.Caption).Activate
Exit For
End If
Next ob
End If
Else
MsgBox "All worksheets are empty."
End If

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

End Sub




--

HTH

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

"Shawn" wrote in message
...
I need to know the code for a vb application that will pop up upon
opening a workbook and ask you to select a radio button and go to a
worksheet depending on your selection. Any ideas? Thanks