Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to have two groups of radio buttons on a worksheet? | Excel Worksheet Functions | |||
Can I show a picture depending on what data is selected in a cell | Excel Discussion (Misc queries) | |||
How do I lock a radio button group if a N/A button is selected | Excel Discussion (Misc queries) | |||
return a data list depending on a value selected within a drop dow | Excel Discussion (Misc queries) | |||
VBA: Disable Frame and Radio Buttons based on Another Radio Button Being True | Excel Worksheet Functions |