asked on

Excel Problem with selecting multiple sheets with array

Dear all,

I have written a simple program used to selecting multiple worksheets within a hundred sheets workbook. The program involves two functions, (1) list all worksheets in the listbox and then (2) select the pages by perform multiselect in the listbox.

The first parts works ok but an error " Subscript out of range" shown when executing the last line of the code. Can any expert help me to fix it?

As I am just a layman of programming, please also advise me if you spot any part of the code needs improvement.

Function UpdateListBox()

    Dim i As Integer
        Dim SHTCount As Integer
        Dim SHT As Variant
    
    
        SHTCount = Worksheets.Count
        ReDim SHT(SHTCount)
    
        
        For i = 1 To SHTCount
        
            SHT(i) = Worksheets(i).Name
        
        
            Debug.Print SHT(i)
        
        Next
            
        ListBox1.List() = SHT

End Function


Function CheckSelected()

Dim s As Integer
Dim SelePages As String
Dim digi As Integer
'Dim ws As Variant


    For s = 0 To ListBox1.ListCount - 1
        
        If ListBox1.Selected(s) = True Then
                        
            SelePages = SelePages & Chr(34) & Worksheets(s + 1).Name & Chr(34) & ", "
                       
        End If
                
    Next s
        
                        
        digi = Len(SelePages)
        SelePages = Mid(SelePages, 1, digi - 2)
        
        
        SelePages = "Array(" & SelePages & ")"
        'Debug.Print SelePages
        

    Sheets(SelePages).Select <--- Error found here
        
    End Function

Open in new window

out-of-range.jpg

Chris Bottomley

Sheets(SelePages).Select <--- Error found here

When you select the page it is taking it's name from the variable so assuming Selepages = "Sheet1" then the worksheet you are adressing is:

Array(Sheet1)
via
SelePages = "Array(" & SelePages & ")"

Do you really have a sheet so called?

Chris

Dave

You can't pass a string variable for the Array selection

You can do it if you build an actual array of sheets to be seleted

ie to select all odd sheets in the worbook by code you can use an array (shArray) like below

Cheers

Dave

Sub ShtSelect()
' credit to http://www.tushar-mehta.com/  
Dim shArray()
    Dim lngsht As Long
    ReDim shArray(0)
    For lngsht = 1 To Sheets.Count Step 2
        shArray(UBound(shArray)) = Sheets(lngsht).Name
        ReDim Preserve shArray(UBound(shArray) + 1)
    Next
    ReDim Preserve shArray(UBound(shArray) - 1)
    Sheets(shArray).Select
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawlords

ASKER

Thanks Dave and Chris,

Your idea is brilliant but the sheets we need to selecte would be more irregular in pattern as shown in the picture rather than odd or even pages issues. I think that has added much complexity to our case.

By the way, the SelePages appeared in my original code is the string to collect the name of the selected sheets and then convert to a sting somthing like Array("GFA" , "GPRS, "Main Roof") before sending to Sheet select procedure. It seems that is the stem of the problem.

frmSelectPages.frm

Dave

I think my last
CheckSelected()
should work for your problem (of irregular sheets). It uses your Listbox

My first post was more a worked example of a method that would work as I didnt have your file to test

Cheers

Dave

Lawlords

ASKER

Thanks. That really my problem.