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!

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" <(E-Mail Removed)> wrote in message
news835C08E-2024-4A04-99CC-(E-Mail Removed)...
> 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!

Fantastic! Many thanks Chip.

"Chip Pearson" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news835C08E-2024-4A04-99CC-(E-Mail Removed)...
> > 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!
>