Add Tab names to Userform

[SLW612]

Hi,
I have a worksheet that has a Master page for all data entries. Column A's
validation set to one of ten values, and I have a macro set up to
automatically create a new sheet for each of the values appearing in Column A
and transfer that specific data to it. Example:

Master list
A B C D
Chicago Johnson Steve Virgo
Boston O'Leary Sean Taurus
Chicago Smith Jack Aries
Dallas Hart Peter Pisces
Boston Petrov Harry Aries

Three new sheets are created, titled "Chicago", "Boston", and "Dallas", and
Chicago has two lines carried over, Boston two, and Dallas one.

The Master gets updated through the month and we always need the most recent
copy of each sheet printed out, but the way I have my macro set up now it
will print out all of the sheets whether they have been updated or not.

What I want to do is create a dynamic userform with checkboxes that would
display the names of the tabs as checkboxes, and you can click the ones you
would like to print. Is there an easy way to do this?

Thank you!

 

[Chip Pearson]

The following code in the code module of a UserForm will create a checkbox
for each worksheet and then when CommandButton1 is clicked, it will print
the checked worksheets.


Private Sub CommandButton1_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''
' Print worksheets
''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Ctrl As MSForms.Control
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Value <> False Then
If SheetExists(Ctrl.Caption) = True Then
' ThisWorkbook.Worksheets(Ctrl.Caption).PrintOut
Debug.Print "Print: " & Ctrl.Caption
End If
End If
End If
Next Ctrl
End Sub

Function SheetExists(SheetName As String) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns TRUE if SheetName exists. False otherwise.
''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
SheetExists = CBool(Len(ThisWorkbook.Worksheets(SheetName).Name))
End Function


Private Sub UserForm_Initialize()
''''''''''''''''''''''''''''''''''''''''''''''''''
' Create the checkboxes.
''''''''''''''''''''''''''''''''''''''''''''''''''

Dim WS As Worksheet
Dim T As Double
Dim L As Double
Dim H As Double
Dim W As Double
Dim CHK As MSForms.CheckBox
Dim R As Long
Dim C As Long

' Height and Width
H = 18
W = 90
' Top and Left
T = 6
L = 6

' Row/Column indices for check boxes
C = 0
R = 0
For Each WS In ThisWorkbook.Worksheets
Set CHK = Me.Controls.Add("Forms.CheckBox.1", _
Replace(WS.Name, Chr(32), vbNullString), True)
CHK.Caption = WS.Name
CHK.Top = T + (R * H)
CHK.Left = L + (C * W)
If C = 1 Then
C = 0
R = R + 1
Else
C = C + 1
End If
Next WS

End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[SLW612]

Fantastic! Many thanks Chip.

The following code in the code module of a UserForm will create a checkbox
for each worksheet and then when CommandButton1 is clicked, it will print
the checked worksheets.


Private Sub CommandButton1_Click()
''''''''''''''''''''''''''''''''''''''''''''''''''
' Print worksheets
''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Ctrl As MSForms.Control
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is MSForms.CheckBox Then
If Ctrl.Value <> False Then
If SheetExists(Ctrl.Caption) = True Then
' ThisWorkbook.Worksheets(Ctrl.Caption).PrintOut
Debug.Print "Print: " & Ctrl.Caption
End If
End If
End If
Next Ctrl
End Sub

Function SheetExists(SheetName As String) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''
' Returns TRUE if SheetName exists. False otherwise.
''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
SheetExists = CBool(Len(ThisWorkbook.Worksheets(SheetName).Name))
End Function


Private Sub UserForm_Initialize()
''''''''''''''''''''''''''''''''''''''''''''''''''
' Create the checkboxes.
''''''''''''''''''''''''''''''''''''''''''''''''''

Dim WS As Worksheet
Dim T As Double
Dim L As Double
Dim H As Double
Dim W As Double
Dim CHK As MSForms.CheckBox
Dim R As Long
Dim C As Long

' Height and Width
H = 18
W = 90
' Top and Left
T = 6
L = 6

' Row/Column indices for check boxes
C = 0
R = 0
For Each WS In ThisWorkbook.Worksheets
Set CHK = Me.Controls.Add("Forms.CheckBox.1", _
Replace(WS.Name, Chr(32), vbNullString), True)
CHK.Caption = WS.Name
CHK.Top = T + (R * H)
CHK.Left = L + (C * W)
If C = 1 Then
C = 0
R = R + 1
Else
C = C + 1
End If
Next WS

End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)