E
excelmonkey
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
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