Security of Excel Spreadsheet 1

[Trident1]

I have been using macros in excel for quite a while to automate executions whilst working within the spreadsheet environment. To date, I have not mastered the art of a macro activating when the workbook opens!

I have read a few articles on the net about this and it seems that the manner in which the code is written differs from the ones I am accustomed to....!?

I now NEED this facility.

Basically I need to initially hide all the sheets within a workbook from the onset. I then need a system in place where an individual user can enter a password to make their own worksheet visible and usable. Ultimately the "Administrator" should be able to enter a password to make all of them visible.

Any help will be much appreciated...

 

[fredericofonseca]

that question has been answered before on tek-tips.

Search the forums/faqs (not just this one) for password and worksheet and you will find some examples.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Trident1]

Thank you Frederico for your reply. I started to pursue your suggestion and there are literally hundreds of hits on both words; password and worksheet. I really need someone to point me in the direction of these auto open, or whatever they are called, macros. Even if someone can just show me a code to hide a named worksheet when the workbook is opened, how and where to enter it would be a huge help to me.

Regards

 

[shetlandbob]

have you searched?

I dont mean to be rude, but I just did a search on "worksheet hide" and there is a thread in the first 10 that contains example code of hiding worksheets/passwords etc....

And do a second search for auto_open to find the macro that is executed on opening a sheet.

 

[xlbo]

keywords for help file:

Workbookopen event
environ
visible
protect method

the following will give you more info about the "environ" function:

Sub env()
Dim EnvString As String
Indx = 1
Do
    EnvString = Environ(Indx)
    Cells(Indx, 1) = EnvString
    Indx = Indx + 1
Loop Until EnvString = ""
End Sub

Also have a look at he object model (F2 whilst in the VBE) which will enable you to see the properties of worksheets (this is where you can find info on the visible property and the protect property)

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[macropod]

Hi Trident1,

Excel can be set up to hide or show a worksheet. There are three levels of visibility- Visible (xlVisible), Hidden (xlHidden) and Very Hidden (xlVeryHidden).

The distinction between xlHidden and xlVeryHidden is that, with Visible = Hide(or False) - Sheet/Unhide command is available, but with Visible = xlVeryHidden, the Sheet/Unhide command is not available. To unhide such a worksheet, you must set the Visible property to True.

To hide a specific worksheet
Sub Hide_WS1()
Worksheets(2).Visible = False ' you can use Hide or False
End Sub

To make a specific worksheet very hidden
Sub Hide_WS2()
Worksheets(2).Visible = xlVeryHidden
End Sub

To unhide a specific worksheet
Sub UnHide_WS()
Worksheets(2).Visible = True
End Sub

To toggle between hidden and visible
Sub Toggle_Hidden_Visible()
Worksheets(2).Visible = Not Worksheets(2).Visible
End Sub

To set the visible property to True on ALL sheets in workbook
Sub Un_Hide_All()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = True
Next
End Sub

To set the visible property to xlVeryHidden on ALL sheets in workbook.
Sub xlVeryHidden_All_Sheets()
On Error Resume Next
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = xlVeryHidden
Next
End Sub
Note: One thing you'll find when you try to hide all worksheets as per the above is that the last "hide" will fail. That's because at least one worksheet must remain visible, but you can hide all of that worksheet's columns and rows.

That should keep you busy for a while, and automating a password hide/unhide process will keep you busy even longer. Then, for some added security, you'll need to think about protecting the vba project that all this code sits in, plus hiding the code itself from view.

Cheers

 

[Trident1]

Thank you so much for yor trouble. I will endeavour to play with all this information.

Mike