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