Hi,
I'm working on a form that displays all the sheets on a workbook (so far 30,
the number can grow or shrink). The user will check which sheet(s) he/she
wants to save as a new file.
At the begining I was manually naming the checkboxes, now that the number of
sheets is growing, it became a waste of time.

Is there any way I can display automatically ALL sheets in a form? Can this
form be created on a sheet and hide the rest of the sheets? What is actually
doing is displaying just one sheet (with a menu button), click on the button
and displays the form, where the user selects which sheets to save. on click
the page displays. On unclick, goes hidden again.

Any ideas of better methods are more than welcome!
Thanks in advance

I would use a list box. With a Listbox named ListBox1, use code like
the following in the form's code module:

Private Sub UserForm_Initialize()
Dim WS As Worksheet
With Me.ListBox1
For Each WS In ThisWorkbook.Worksheets
.AddItem WS.Name
Next WS
.MultiSelect = fmMultiSelectExtended
.ListIndex = 0
End With
End Sub

Private Sub CommandButton1_Click()
Dim N As Long
Dim S As String
With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
S = S & .List(N) & vbCrLf
End If
Next N
End With
MsgBox S
End Sub


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

On Wed, 25 Feb 2009 10:51:01 -0800, Gaba
wrote:

Hi,
I'm working on a form that displays all the sheets on a workbook (so far 30,
the number can grow or shrink). The user will check which sheet(s) he/she
wants to save as a new file.
At the begining I was manually naming the checkboxes, now that the number of
sheets is growing, it became a waste of time.

Is there any way I can display automatically ALL sheets in a form? Can this
form be created on a sheet and hide the rest of the sheets? What is actually
doing is displaying just one sheet (with a menu button), click on the button
and displays the form, where the user selects which sheets to save. on click
the page displays. On unclick, goes hidden again.

Any ideas of better methods are more than welcome!
Thanks in advance

Thanks so much Chip. I think I'm missing something... I have all the sheets
hidden except One. When clicking on the list, the page should be unhidden.
How can I achieve this? it is possible to do multiple selections?

Thanks in advance,
Gaba

"Chip Pearson" wrote:

I would use a list box. With a Listbox named ListBox1, use code like
the following in the form's code module:

Private Sub UserForm_Initialize()
Dim WS As Worksheet
With Me.ListBox1
For Each WS In ThisWorkbook.Worksheets
.AddItem WS.Name
Next WS
.MultiSelect = fmMultiSelectExtended
.ListIndex = 0
End With
End Sub

Private Sub CommandButton1_Click()
Dim N As Long
Dim S As String
With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
S = S & .List(N) & vbCrLf
End If
Next N
End With
MsgBox S
End Sub


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

On Wed, 25 Feb 2009 10:51:01 -0800, Gaba
wrote:

Hi,
I'm working on a form that displays all the sheets on a workbook (so far 30,
the number can grow or shrink). The user will check which sheet(s) he/she
wants to save as a new file.
At the begining I was manually naming the checkboxes, now that the number of
sheets is growing, it became a waste of time.

Is there any way I can display automatically ALL sheets in a form? Can this
form be created on a sheet and hide the rest of the sheets? What is actually
doing is displaying just one sheet (with a menu button), click on the button
and displays the form, where the user selects which sheets to save. on click
the page displays. On unclick, goes hidden again.

Any ideas of better methods are more than welcome!
Thanks in advance

At least one sheet has to be visible at any time. I'm guessing that that one
sheet that you have visible is a key/instruction sheet.

If that's true, this may get you further:

Option Explicit
Const NameOfSheetThatIsAlwaysVisible As String = "Sheet1"
Private Sub UserForm_Initialize()
Dim WS As Object
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
For Each WS In ThisWorkbook.Sheets
If LCase(WS.Name) = LCase(NameOfSheetThatIsAlwaysVisible) Then
'skip it, don't even allow a choice
Else
.AddItem WS.Name
End If
Next WS
.ListIndex = 0
End With
End Sub
Private Sub CommandButton1_Click()
Dim N As Long

'this sheet is always visible (make it visible -- just in case!)
ThisWorkbook.Sheets(NameOfSheetThatIsAlwaysVisible ).Visible =
xlSheetVisible

With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetVisible
Else
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetHidden
End If
Next N
End With
End Sub


Gaba wrote:

Thanks so much Chip. I think I'm missing something... I have all the sheets
hidden except One. When clicking on the list, the page should be unhidden.
How can I achieve this? it is possible to do multiple selections?

Thanks in advance,
Gaba

"Chip Pearson" wrote:

I would use a list box. With a Listbox named ListBox1, use code like
the following in the form's code module:

Private Sub UserForm_Initialize()
Dim WS As Worksheet
With Me.ListBox1
For Each WS In ThisWorkbook.Worksheets
.AddItem WS.Name
Next WS
.MultiSelect = fmMultiSelectExtended
.ListIndex = 0
End With
End Sub

Private Sub CommandButton1_Click()
Dim N As Long
Dim S As String
With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
S = S & .List(N) & vbCrLf
End If
Next N
End With
MsgBox S
End Sub


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

On Wed, 25 Feb 2009 10:51:01 -0800, Gaba
wrote:

Hi,
I'm working on a form that displays all the sheets on a workbook (so far 30,
the number can grow or shrink). The user will check which sheet(s) he/she
wants to save as a new file.
At the begining I was manually naming the checkboxes, now that the number of
sheets is growing, it became a waste of time.

Is there any way I can display automatically ALL sheets in a form? Can this
form be created on a sheet and hide the rest of the sheets? What is actually
doing is displaying just one sheet (with a menu button), click on the button
and displays the form, where the user selects which sheets to save. on click
the page displays. On unclick, goes hidden again.

Any ideas of better methods are more than welcome!
Thanks in advance


--

Dave Peterson

Watch out for line wrap on this line:

ThisWorkbook.Sheets(NameOfSheetThatIsAlwaysVisible ).Visible _
= xlSheetVisible

Dave Peterson wrote:

At least one sheet has to be visible at any time. I'm guessing that that one
sheet that you have visible is a key/instruction sheet.

If that's true, this may get you further:

Option Explicit
Const NameOfSheetThatIsAlwaysVisible As String = "Sheet1"
Private Sub UserForm_Initialize()
Dim WS As Object
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
For Each WS In ThisWorkbook.Sheets
If LCase(WS.Name) = LCase(NameOfSheetThatIsAlwaysVisible) Then
'skip it, don't even allow a choice
Else
.AddItem WS.Name
End If
Next WS
.ListIndex = 0
End With
End Sub
Private Sub CommandButton1_Click()
Dim N As Long

'this sheet is always visible (make it visible -- just in case!)
ThisWorkbook.Sheets(NameOfSheetThatIsAlwaysVisible ).Visible =
xlSheetVisible

With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetVisible
Else
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetHidden
End If
Next N
End With
End Sub

Gaba wrote:

Thanks so much Chip. I think I'm missing something... I have all the sheets
hidden except One. When clicking on the list, the page should be unhidden.
How can I achieve this? it is possible to do multiple selections?

Thanks in advance,
Gaba

"Chip Pearson" wrote:

I would use a list box. With a Listbox named ListBox1, use code like
the following in the form's code module:

Private Sub UserForm_Initialize()
Dim WS As Worksheet
With Me.ListBox1
For Each WS In ThisWorkbook.Worksheets
.AddItem WS.Name
Next WS
.MultiSelect = fmMultiSelectExtended
.ListIndex = 0
End With
End Sub

Private Sub CommandButton1_Click()
Dim N As Long
Dim S As String
With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
S = S & .List(N) & vbCrLf
End If
Next N
End With
MsgBox S
End Sub


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

On Wed, 25 Feb 2009 10:51:01 -0800, Gaba
wrote:

Hi,
I'm working on a form that displays all the sheets on a workbook (so far 30,
the number can grow or shrink). The user will check which sheet(s) he/she
wants to save as a new file.
At the begining I was manually naming the checkboxes, now that the number of
sheets is growing, it became a waste of time.

Is there any way I can display automatically ALL sheets in a form? Can this
form be created on a sheet and hide the rest of the sheets? What is actually
doing is displaying just one sheet (with a menu button), click on the button
and displays the form, where the user selects which sheets to save. on click
the page displays. On unclick, goes hidden again.

Any ideas of better methods are more than welcome!
Thanks in advance


--

Dave Peterson

--

Dave Peterson

Dave,
Thanks so much. It looks great. The only think I can't get to work is when
you "click" one or more sheets, to make them visible. This way when saving
as, is saving only the visible sheets and not the whole workbook. Any ideas?
Thanks in advance
Gaba

"Dave Peterson" wrote:

At least one sheet has to be visible at any time. I'm guessing that that one
sheet that you have visible is a key/instruction sheet.

If that's true, this may get you further:

Option Explicit
Const NameOfSheetThatIsAlwaysVisible As String = "Sheet1"
Private Sub UserForm_Initialize()
Dim WS As Object
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
For Each WS In ThisWorkbook.Sheets
If LCase(WS.Name) = LCase(NameOfSheetThatIsAlwaysVisible) Then
'skip it, don't even allow a choice
Else
.AddItem WS.Name
End If
Next WS
.ListIndex = 0
End With
End Sub
Private Sub CommandButton1_Click()
Dim N As Long

'this sheet is always visible (make it visible -- just in case!)
ThisWorkbook.Sheets(NameOfSheetThatIsAlwaysVisible ).Visible =
xlSheetVisible

With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetVisible
Else
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetHidden
End If
Next N
End With
End Sub


Gaba wrote:

Thanks so much Chip. I think I'm missing something... I have all the sheets
hidden except One. When clicking on the list, the page should be unhidden.
How can I achieve this? it is possible to do multiple selections?

Thanks in advance,
Gaba

"Chip Pearson" wrote:

I would use a list box. With a Listbox named ListBox1, use code like
the following in the form's code module:

Private Sub UserForm_Initialize()
Dim WS As Worksheet
With Me.ListBox1
For Each WS In ThisWorkbook.Worksheets
.AddItem WS.Name
Next WS
.MultiSelect = fmMultiSelectExtended
.ListIndex = 0
End With
End Sub

Private Sub CommandButton1_Click()
Dim N As Long
Dim S As String
With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
S = S & .List(N) & vbCrLf
End If
Next N
End With
MsgBox S
End Sub


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

On Wed, 25 Feb 2009 10:51:01 -0800, Gaba
wrote:

Hi,
I'm working on a form that displays all the sheets on a workbook (so far 30,
the number can grow or shrink). The user will check which sheet(s) he/she
wants to save as a new file.
At the begining I was manually naming the checkboxes, now that the number of
sheets is growing, it became a waste of time.

Is there any way I can display automatically ALL sheets in a form? Can this
form be created on a sheet and hide the rest of the sheets? What is actually
doing is displaying just one sheet (with a menu button), click on the button
and displays the form, where the user selects which sheets to save. on click
the page displays. On unclick, goes hidden again.

Any ideas of better methods are more than welcome!
Thanks in advance


--

Dave Peterson

File|Save As will save all the sheets--visible or not.

If you want to save a copy of the workbook, you can either delete the sheets you
don't want and then save save that--or you can move/copy the worksheets to a
different workbook and save that.

But I'm really not sure what part isn't working for you.

Gaba wrote:

Dave,
Thanks so much. It looks great. The only think I can't get to work is when
you "click" one or more sheets, to make them visible. This way when saving
as, is saving only the visible sheets and not the whole workbook. Any ideas?
Thanks in advance
Gaba

"Dave Peterson" wrote:

At least one sheet has to be visible at any time. I'm guessing that that one
sheet that you have visible is a key/instruction sheet.

If that's true, this may get you further:

Option Explicit
Const NameOfSheetThatIsAlwaysVisible As String = "Sheet1"
Private Sub UserForm_Initialize()
Dim WS As Object
With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
For Each WS In ThisWorkbook.Sheets
If LCase(WS.Name) = LCase(NameOfSheetThatIsAlwaysVisible) Then
'skip it, don't even allow a choice
Else
.AddItem WS.Name
End If
Next WS
.ListIndex = 0
End With
End Sub
Private Sub CommandButton1_Click()
Dim N As Long

'this sheet is always visible (make it visible -- just in case!)
ThisWorkbook.Sheets(NameOfSheetThatIsAlwaysVisible ).Visible =
xlSheetVisible

With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetVisible
Else
ThisWorkbook.Sheets(.List(N)).Visible = xlSheetHidden
End If
Next N
End With
End Sub


Gaba wrote:

Thanks so much Chip. I think I'm missing something... I have all the sheets
hidden except One. When clicking on the list, the page should be unhidden.
How can I achieve this? it is possible to do multiple selections?

Thanks in advance,
Gaba

"Chip Pearson" wrote:

I would use a list box. With a Listbox named ListBox1, use code like
the following in the form's code module:

Private Sub UserForm_Initialize()
Dim WS As Worksheet
With Me.ListBox1
For Each WS In ThisWorkbook.Worksheets
.AddItem WS.Name
Next WS
.MultiSelect = fmMultiSelectExtended
.ListIndex = 0
End With
End Sub

Private Sub CommandButton1_Click()
Dim N As Long
Dim S As String
With Me.ListBox1
For N = 0 To .ListCount - 1
If .Selected(N) = True Then
S = S & .List(N) & vbCrLf
End If
Next N
End With
MsgBox S
End Sub


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

On Wed, 25 Feb 2009 10:51:01 -0800, Gaba
wrote:

Hi,
I'm working on a form that displays all the sheets on a workbook (so far 30,
the number can grow or shrink). The user will check which sheet(s) he/she
wants to save as a new file.
At the begining I was manually naming the checkboxes, now that the number of
sheets is growing, it became a waste of time.

Is there any way I can display automatically ALL sheets in a form? Can this
form be created on a sheet and hide the rest of the sheets? What is actually
doing is displaying just one sheet (with a menu button), click on the button
and displays the form, where the user selects which sheets to save. on click
the page displays. On unclick, goes hidden again.

Any ideas of better methods are more than welcome!
Thanks in advance


--

Dave Peterson


--

Dave Peterson