2009-11-17

Excel VBA Stub: Rearranging Sheets

Codes for re-ordering worksheet
subSplitAndHideSheets: split and hide sheets that not appeared on list: strSheetsAll

------------------


Sub subLaunch
dim strSheetsAll as string
strSheetsAll = "Sheet1;Sheet3;Sheet2"
call subSplitAndHideSheets(strSheetsAll)
end sub


Sub subSplitAndHideSheets(wbCurr As Workbook, strSheetsAll as string)

Dim strSheets() As String
Dim strSheetsSD
Dim intSheetsCounter As Integer
Dim intSheetsCount As Integer
Dim intCurrSheet As Integer


If strSheetsAll = "" Then
Exit Sub 'Exit sub if not given
End If

Set strSheetSD = CreateObject("Scripting.Dictionary")

strSheets = Split(strSheetsAll, ";")
intCurrSheet = 1
intSheetsCount = UBound(strSheets)
For intSheetsCounter = 0 To UBound(strSheets)
If funcChkSheetExist(wbCurr, strSheets(intSheetsCounter)) Then
strSheetSD.Add (UCase(strSheets(intSheetsCounter))), Nothing
wbCurr.Sheets(strSheets(intSheetsCounter)).Move before:=Sheets(intCurrSheet)
intCurrSheet = intCurrSheet + 1
End If
Next

intSheetsCount = wbCurr.Worksheets.Count
If intSheetsCount <= 1 Then
Exit Sub
End If
For intSheetsCounter = 1 To wbCurr.Worksheets.Count
If wbCurr.Sheets(intSheetsCounter).Visible = xlSheetVisible Then
'set as hidden: if Sheet not exist in given string array and name is not parameters
If Not strSheetSD.exists(UCase(wbCurr.Sheets(intSheetsCounter).Name)) And _
UCase(wbCurr.Sheets(intSheetsCounter).Name) <> STRSHEETNAME_PARAMETERS Then
wbCurr.Sheets(intSheetsCounter).Visible = xlSheetHidden
End If
End If
Next
End Sub

Function funcChkSheetExist(wbCurr As Workbook, strSheet As String)
On Error GoTo errHandling
Dim tmpVal
tmpVal = wbCurr.Sheets(strSheet).Range("A1")
funcChkSheetExist = True
Exit Function
errHandling:
funcChkSheetExist = False
End Function

No comments:

Post a Comment