Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to access muliple tabs of sheet as variable of an Array
I created a file with multiple Worksheets and defined an Array:
Dim arrRtTab(1 To 12) As String arrRtTab(1) = "MS" arrRtTab(2) = "MW" arrRtTab(3) = "MM" arrRtTab(4) = "MU" arrRtTab(5) = "MDS3" arrRtTab(6) = "XS" arrRtTab(7) = "XN" arrRtTab(8) = "QN" arrRtTab(9) = "QE" arrRtTab(10) = "QS" arrRtTab(11) = "NS" arrRtTab(12) = "LI" Depending on results of my computations I need to be able select multiple tabs of the file and format or print them. How to build the variable to be able to select tabs for instance MS, MM, XN or QS, LI for printing them? Please Help Georgee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to access muliple tabs of sheet as variable of an Array
Perhaps a UserForm could solve your problem?
Sub ShowDialog() UserForm1.Show End Sub Code for the UserForm: Private Sub CommandButton1_Click() Dim arr() As String Dim N As Integer N = 0 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then N = N + 1 ReDim Preserve arr(1 To N) arr(N) = ListBox1.List(i) End If Next i If N = 0 Then MsgBox "You must choose at least one sheet - or click x" Exit Sub End If ThisWorkbook.Worksheets(arr).PrintOut Unload Me Sheets(1).Select End Sub Private Sub UserForm_Initialize() For Each ws In ActiveWorkbook.Sheets If ws.Visible = True Then Me.ListBox1.AddItem (ws.Name) End If Next End Sub "GorKo" wrote in message oups.com... I created a file with multiple Worksheets and defined an Array: Dim arrRtTab(1 To 12) As String arrRtTab(1) = "MS" arrRtTab(2) = "MW" arrRtTab(3) = "MM" arrRtTab(4) = "MU" arrRtTab(5) = "MDS3" arrRtTab(6) = "XS" arrRtTab(7) = "XN" arrRtTab(8) = "QN" arrRtTab(9) = "QE" arrRtTab(10) = "QS" arrRtTab(11) = "NS" arrRtTab(12) = "LI" Depending on results of my computations I need to be able select multiple tabs of the file and format or print them. How to build the variable to be able to select tabs for instance MS, MM, XN or QS, LI for printing them? Please Help Georgee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to access muliple tabs of sheet as variable of an Array
Thanks for offerd help but, I think that you are offering me a way to
manually enter a tab while I need a way to have tabs selected automatically depending on the values in certain cells Georgee Alf Bryn wrote: Perhaps a UserForm could solve your problem? Sub ShowDialog() UserForm1.Show End Sub Code for the UserForm: Private Sub CommandButton1_Click() Dim arr() As String Dim N As Integer N = 0 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) = True Then N = N + 1 ReDim Preserve arr(1 To N) arr(N) = ListBox1.List(i) End If Next i If N = 0 Then MsgBox "You must choose at least one sheet - or click x" Exit Sub End If ThisWorkbook.Worksheets(arr).PrintOut Unload Me Sheets(1).Select End Sub Private Sub UserForm_Initialize() For Each ws In ActiveWorkbook.Sheets If ws.Visible = True Then Me.ListBox1.AddItem (ws.Name) End If Next End Sub "GorKo" wrote in message oups.com... I created a file with multiple Worksheets and defined an Array: Dim arrRtTab(1 To 12) As String arrRtTab(1) = "MS" arrRtTab(2) = "MW" arrRtTab(3) = "MM" arrRtTab(4) = "MU" arrRtTab(5) = "MDS3" arrRtTab(6) = "XS" arrRtTab(7) = "XN" arrRtTab(8) = "QN" arrRtTab(9) = "QE" arrRtTab(10) = "QS" arrRtTab(11) = "NS" arrRtTab(12) = "LI" Depending on results of my computations I need to be able select multiple tabs of the file and format or print them. How to build the variable to be able to select tabs for instance MS, MM, XN or QS, LI for printing them? Please Help Georgee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
muliple sheet range + sumif / sumproduct | Excel Worksheet Functions | |||
Obtaining Multiple Results using muliple Criteria from an Array | Excel Worksheet Functions | |||
Search, find, copy muliple cells from new sheet and paste | Excel Programming | |||
Copying an array variable to a sheet | Excel Programming |