Run .vbs script on client workbooks

[goplayoutside]

Hi -

I am not a programmer and have a question I hope I can explain and that you
can answer.

The agency I work for requests hundreds of excel workbooks from other state
agencies. We are auditors. (Please don't hold that against me. I'm not an
auditor. I just keep their computers running.)

Since these workbooks come from other agencies, there is no standard format
or style. Many times, the workbooks have hidden rows and columns and may
contain one sheet or several sheets.

I have been asked if it is possible to run a Macro on the workbook to unhide
the columns and rows when the workbook opens. I have the Macro: (I have
saved it as a .vbs file)

Sub ShowHiddenRowsAndColumns()
'
' showhiddenrows Macro
' exposes hidden rows and columns
'
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False

End Sub

This works for the workbook it was created in. We want to run it on a
workbook that does not have the macro in it when the file opens. Is there a
simple way to do this?

Thank you very much in advance for your time and effort in helping me.

Wendy

 

[Harald Staff]

Hi Wendy

Macros are VBA, not VBS, so you can't port code like that without altering
it a bit.

I'd rather create a "magic workbook" that does this.Open the magic workbook
first, and it will unhide all rows and columns in all sheets in all
workbooks that are opened after in the session, as long as the book is open
that is.

Here's how to make it:

Open the VB editor in a new workbook. Go to the ThisWorkbook module and
paste this in:

'************ top of module **********************

Option Explicit

Private WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim oSht As Worksheet
On Error Resume Next
For Each oSht In Wb.Worksheets
oSht.Cells.EntireColumn.Hidden = False
oSht.Cells.EntireRow.Hidden = False
Next
End Sub

'***************** end of code ******************

Save this as either MagicBook.xls or MagicBook.xlsm (2007 version only).
Close it, reopen it and Excel will handle the rest without further
interaction.

Exception is if the sheets are password protected. Code will not err, but
not unhide anything either. It's possible to include password breaking code
in the routine if so, but it complicates it a bit, so i'll leave that for
now.

HTH. Best wishes Harald