Hiding Spreadsheet Automatically

[ankrups]

Hi there,
one simple question...how can we automated process of hiding the selected worksheet when we close the workbook....

thanks


ankit..

 

[venkat1926]

put an event code under thisworkbook_before close somethinglike this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
Worksheets("sheet1").Visible = False
Application.Enableevents = True
End Sub

 

[ankrups]

thanks venkat for your help...

 

[ankrups]

just a thought pop up in mind...Can we create such formula that we write only once...in VBA...and in future we just call this formula by clikcing VBA Code of related worksheet....


thanks..

 

[venkat1926]

put it under FUNCTION and NOT as sub.

this is an example
see this functin

Public Function monthname(x As Long) As String
'use function as personal.xls!monthname(some no.)
If x = 1 Then monthname = "jan"
If x = 2 Then monthname = "feb"
If x = 3 Then monthname = "mar"
If x = 4 Then monthname = "apr"
If x = 5 Then monthname = "may"
If x = 6 Then monthname = "jun"
If x = 7 Then monthname = "july"
If x = 8 Then monthname = "aug"
If x = 9 Then monthname = "sep"
If x = 10 Then monthname = "oct"
If x = 11 Then monthname = "nov"
If x = 12 Then monthname = "dec"
End Function
This functin I have parked in personal.xls file vb editor so that it can be used in any workbook.
now in any cell in any sheet in any workbook type

=PERSONAL.XLS!monthname(3)
note exclamation mark.
the result will be
mar