In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not.
The following excel vba function assigned to check whether a workbook we need is open or not.
Using Workbook name as an input parameter, the function will do a looping in Workbooks collection to check all opened Workbook name, if there is a Workbook with the same name with the Workbook that we looking for, then the function will return true, and false if otherwise.
Public Function CheckSourceAvailability(sWorkBook As String) _
As Boolean
Dim wb As Workbook, bResult As Boolean
bResult = False
For Each wb In Application.Workbooks
If InStr(LCase(wb.Name), LCase(sWorkBook)) > 0 Then
bResult = True
Exit For
End If
Next wb
CheckSourceAvailability = bResult
End Function
All information about opened Workbook was saved by Excel in a collection object called Workbooks, just like Worksheets collection used by Excel to save information about all the Worksheets available.
FIN.
Some time we want to hide a certain Excel Worksheet from view, and it’s a common practice to use select the Worksheet, go to menu Format > Sheet > Hide.
Using the method explained above is right, but unfortunately, others people can easily unhide the Worksheet using the same method, only this time, instead of selecting Hide, they simply need to choose UnHide, and all the Worksheets in hiding will be revealed.
The above method only working in Microsoft Excel before 2007, in Excel 2007, the menu to hide and unhide columns/cells/worksheets are hidden by default, but we can add this menu into Excel Quick Access Toolbar (tiny menu at top left corner of the window), by accessing menu Customize Quick Access Toolbar (tiny down arrow on the right), select More Commands… » Choose commands from Home Tab » and select menu Hide & Unhide.
The other method on how to hide our Excel Worksheet, more secured, and not really well known by people is using Worksheet xlSheetVeryHidden properties.
To perform this method, first we need to go to Microsoft Visual Basic Editor (ALT+F11), in the project explorer (if the explorer is not showing, click CTRL+R), select Worksheet that we want to hide, then go to Properties Windows (F4), and in the Visible properties, select 2 – xlSheetVeryHidden like in this picture:
If we follow all the guide above, the Worksheet will be disappear/hidden from view, even when we use menu Format > Sheet > UnHide, the Worksheet will not be displayed in the list of Worksheets in hiding.
We can also get the same result using Excel VBA macro, like this:
Private Sub Workbook_Open()
Worksheets("Sheet1").Visible = xlSheetVeryHidden
End Sub
With a simple one line of code, Sheet1 will automatically set to VeryHidden each time the Excel Workbook was opened. Change “Sheet1″ with your Worksheet name.
FIN
