This routine populates the listbox:
Private Sub Worksheet_Activate()
Dim intsheets As Integer
ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear
intsheets = 1
Do While intsheets < (Sheets.Count + 1)
ListBox1.AddItem Worksheets(intsheets).Name
intsheets = intsheets + 1
Loop
End Sub
You could change it to:
Private Sub Worksheet_Activate()
Dim intsheets As Integer
ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear
intsheets = 1
Do While intsheets < (Sheets.Count + 1)
if worksheets(intsheets).visible = xlsheetvisible then
ListBox1.AddItem Worksheets(intsheets).Name
end if
intsheets = intsheets + 1
Loop
End Sub
(Untested, so watch out for typos!)
Steph wrote:
Kev,
Can I ask one follow up question? The event macro that populated the list
box with the sheet names - is there a way to only have the listbox populated
with Visible sheets, ignoring the hidden sheets? Thanks!
"kev_06" wrote in
message ...
I don't think there is any way to select mulitple items in a combobox,
but I do have code for printing out multiple items in a listbox. It
requires a command button and a listbox. The listbox is loaded with the
sheet names when sheet1 is activated. Here it is:
Private Sub CommandButton1_Click()
Dim myArr() As String
Dim wctr As Long
Dim Ndx As Long
Dim strname As String
Dim msg As String
With Me.ListBox1
wctr = 0
ReDim myArr(1 To .ListCount)
For Ndx = 0 To .ListCount - 1
If .Selected(Ndx) = True Then
wctr = wctr + 1
myArr(wctr) = .List(Ndx)
End If
Next Ndx
End With
If wctr = 0 Then
msg = MsgBox("Please select sheet(s) to print.",
vbExclamation)
Exit Sub
Else
ReDim Preserve myArr(1 To wctr)
Worksheets(myArr).PrintOut
End If
Sheet1.Select
End Sub
Private Sub Worksheet_Activate()
Dim intsheets As Integer
ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.Clear
intsheets = 1
Do While intsheets < (Sheets.Count + 1)
ListBox1.AddItem Worksheets(intsheets).Name
intsheets = intsheets + 1
Loop
End Sub
There's many ways to manipulate the code to load different values in
other than the sheet names (such as values from cells in the
worksheets). If you have any problems, let me know.
--
kev_06
------------------------------------------------------------------------
kev_06's Profile:
http://www.excelforum.com/member.php...o&userid=35046
View this thread: http://www.excelforum.com/showthread...hreadid=556973
--
Dave Peterson