Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Temporary Userform with checkboxes
I have a userform that I create on the fly which creates checkboxes based on the number of sheets in a workbook. The form opens up and the user can click off the checkboxes to choose certain sheets. I then evaluate the xlOn values. I then store the name of each checkbox(based on sheet names) along with a 1 or 0 (Based on XlOn Value) in a 2D array. I am using DialogSheets to do this. The sheets are temporarily created then deleted once the userform is completed. My understanding is that DialogSheets are stil supported in Excel but not used often 1) Is there another (better) way for me to do this in Excel (i.e. not using DialogSheets)? 2) The Userform can get very big vertically as the number of sheets increase (checkboxes are place vertically). Sometimes its so big you cannot see the entire form. Is there way to make this form or another as recommended in question 1 wrap the check boxes in the userform. 3) The form is triggered off two option buttons. One says "Exclude worksheets". Hence if this is chosen, the userform is created and the xlOn property of each option button is evaluated after the form is generated. After choosing the sheets, the user has two buttons "OK" and "Cancel". OK calls the routine that passes the checkbox.Names and xlOn values to the array. However, I have another Option button that says "Include all worksheets". For this to work properly, I have to physically generate the form again to create the checkbox values and xlOn properties. I need these values to pass the values on to the array properly. But its confusing, becuase the user, not wanting to exclude sheets could hit cancel. When the user chooses "Include all worksheets", I want to create the same useform, but not let the user see it and have its OK button clicked. This will then not confuse the user, as the will not see it, and it will ensure that values get generated for the array. Private Sub WorksheetSummary() Dim i As Integer Dim TopPos As Integer Dim TotalSheets As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim ChartSheet As Chart Dim cb As CheckBox Dim myWorkbook As Workbook Dim mySht As Worksheet Dim myInc As Long Dim cbCount As Integer 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 PrintDlg = ActiveWorkbook.DialogSheets.Add TotalSheets = ActiveWorkbook.Worksheets.Count ' Set up Array base on number of sheets counted ReDim SheetExcludeArray(0 To 1, 0 To TotalSheets) SheetCount = 0 ' Add the checkboxes TopPos = 40 For i = 1 To TotalSheets Set CurrentSheet = ActiveWorkbook.Worksheets(i) ' Skip empty sheets and hidden sheets 'If Application.CountA(CurrentSheet.Cells) < 0 And _ 'CurrentSheet.Visible Then SheetCount = SheetCount + 1 ' Create checkboxes for worksheets PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(SheetCount).Text = _ CurrentSheet.Name TopPos = TopPos + 13 ' Pass Sheet Names to first part of Array SheetExcludeArray(0, SheetCount - 1) = CurrentSheet.Name ' Debug.Print SheetExcludeArray(0, SheetCount - 1) ' When sheets are chosen below, a boolean will be passed ' to the second column of the Array (1 or 0) ' This boolean will then be tested in the main routine ' when the macro is looping through the sheets of the worksheet ' If the value is TRUE in the SheetExcludeArray Then a 1 ' will be passed to the array. This 1 will then stop the ' sheet from passing its evaluated cells to the summary sheet '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 .Caption = "Select sheets to EXCLUDE from the Audit" 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 cbCount = 0 For Each cb In PrintDlg.CheckBoxes cbCount = cbCount + 1 If cb.Value = xlOn Then SheetExcludeArray(1, cbCount - 1) = 1 Else SheetExcludeArray(1, cbCount - 1) = 0 End If Debug.Print SheetExcludeArray(0, cbCount - 1) & " " & SheetExcludeArray(1, cbCount - 1) Next cb End If Else MsgBox "All worksheets are empty." End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Temporary Userform with checkboxes
2) The Userform can get very big vertically as the number of sheets increase (checkboxes are place vertically). Sometimes its so big you cannot see the entire form. Is there way to make this form or another On a reasonably sized Userform, place a Label control and next create your Checkboxes on this control. Then set the Scrollheight to match the number of checkboxes. wrote in message oups.com... I have a userform that I create on the fly which creates checkboxes based on the number of sheets in a workbook. The form opens up and the user can click off the checkboxes to choose certain sheets. I then evaluate the xlOn values. I then store the name of each checkbox(based on sheet names) along with a 1 or 0 (Based on XlOn Value) in a 2D array. I am using DialogSheets to do this. The sheets are temporarily created then deleted once the userform is completed. My understanding is that DialogSheets are stil supported in Excel but not used often 1) Is there another (better) way for me to do this in Excel (i.e. not using DialogSheets)? 2) The Userform can get very big vertically as the number of sheets increase (checkboxes are place vertically). Sometimes its so big you cannot see the entire form. Is there way to make this form or another as recommended in question 1 wrap the check boxes in the userform. 3) The form is triggered off two option buttons. One says "Exclude worksheets". Hence if this is chosen, the userform is created and the xlOn property of each option button is evaluated after the form is generated. After choosing the sheets, the user has two buttons "OK" and "Cancel". OK calls the routine that passes the checkbox.Names and xlOn values to the array. However, I have another Option button that says "Include all worksheets". For this to work properly, I have to physically generate the form again to create the checkbox values and xlOn properties. I need these values to pass the values on to the array properly. But its confusing, becuase the user, not wanting to exclude sheets could hit cancel. When the user chooses "Include all worksheets", I want to create the same useform, but not let the user see it and have its OK button clicked. This will then not confuse the user, as the will not see it, and it will ensure that values get generated for the array. Private Sub WorksheetSummary() Dim i As Integer Dim TopPos As Integer Dim TotalSheets As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim ChartSheet As Chart Dim cb As CheckBox Dim myWorkbook As Workbook Dim mySht As Worksheet Dim myInc As Long Dim cbCount As Integer 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 PrintDlg = ActiveWorkbook.DialogSheets.Add TotalSheets = ActiveWorkbook.Worksheets.Count ' Set up Array base on number of sheets counted ReDim SheetExcludeArray(0 To 1, 0 To TotalSheets) SheetCount = 0 ' Add the checkboxes TopPos = 40 For i = 1 To TotalSheets Set CurrentSheet = ActiveWorkbook.Worksheets(i) ' Skip empty sheets and hidden sheets 'If Application.CountA(CurrentSheet.Cells) < 0 And _ 'CurrentSheet.Visible Then SheetCount = SheetCount + 1 ' Create checkboxes for worksheets PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(SheetCount).Text = _ CurrentSheet.Name TopPos = TopPos + 13 ' Pass Sheet Names to first part of Array SheetExcludeArray(0, SheetCount - 1) = CurrentSheet.Name ' Debug.Print SheetExcludeArray(0, SheetCount - 1) ' When sheets are chosen below, a boolean will be passed ' to the second column of the Array (1 or 0) ' This boolean will then be tested in the main routine ' when the macro is looping through the sheets of the worksheet ' If the value is TRUE in the SheetExcludeArray Then a 1 ' will be passed to the array. This 1 will then stop the ' sheet from passing its evaluated cells to the summary sheet '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 .Caption = "Select sheets to EXCLUDE from the Audit" 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 cbCount = 0 For Each cb In PrintDlg.CheckBoxes cbCount = cbCount + 1 If cb.Value = xlOn Then SheetExcludeArray(1, cbCount - 1) = 1 Else SheetExcludeArray(1, cbCount - 1) = 0 End If Debug.Print SheetExcludeArray(0, cbCount - 1) & " " & SheetExcludeArray(1, cbCount - 1) Next cb End If Else MsgBox "All worksheets are empty." End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userform Checkboxes | Excel Discussion (Misc queries) | |||
Excel Creating Temporary File After Saving | Excel Discussion (Misc queries) | |||
Userform CheckBoxes | Excel Programming | |||
Temporary Userform | Excel Programming | |||
UserForm Checkboxes | Excel Programming |