I read a post with the following reply:

As Dave said, you can't "really" make Excel so secure that at competent
person cannot get in, but you "can" make the trip in so confounding that
only a truely dedicated individual will go through all the necessaries to get
there.........thereby, "keeping the honest people out"....
You could incorporate things like: 1-workbook won't open unless macros are
enabled, 2- Pass word(s) are invoked, 3-Only users with name on a list can
access, 4- only user putting secret value into specific cell can gain access,
etc etc........

I will like to know more on the option No. 3 & 4. Can anyone help me on this.
Tnks / r

#3. You could have a macro that runs each time the workbook is opened that
checks a list of names on a hidden worksheet. If the user's name isn't there
(either their network id or their tools|Options|general id), then the macro
could close the workbook.

But macros don't have to be enabled. Or the auto open macros could be avoided.
So this isn't very secure.

#4. Same kind of thing, the user opens the workbook with macros enabled, but
with all the worksheets hidden. When they type something into that "Special"
cell, some macro runs and validates that entry.

This also suffers the same problem as the others. Macros have to be enabled and
worksheets can be unhidden pretty easily for the really curious user.

#5. (You didn't ask!). Password protect the workbook (file|SaveAs|Tools, and
give it a password to open). But then use another helper workbook that has all
the validation/macros in it. If the user passes the validity, then the code in
the helper workbook opens the real workbook (and supplies that password).

But the bad news here is that the helper workbook's code can be broken pretty
easily, too. And once the password is out there, then the users won't need that
helper workbook to open the real workbook.

If you really have sensitive data, don't put it into excel. If you have to put
it into excel, don't share it with anyone you don't trust.



vcff wrote:

I read a post with the following reply:

As Dave said, you can't "really" make Excel so secure that at competent
person cannot get in, but you "can" make the trip in so confounding that
only a truely dedicated individual will go through all the necessaries to get
there.........thereby, "keeping the honest people out"....
You could incorporate things like: 1-workbook won't open unless macros are
enabled, 2- Pass word(s) are invoked, 3-Only users with name on a list can
access, 4- only user putting secret value into specific cell can gain access,
etc etc........

I will like to know more on the option No. 3 & 4. Can anyone help me on this.
Tnks / r

--

Dave Peterson

Hi Dave
tnks for the help. Can you provide the macro for #4 as I am new to all this.
Once again Tnks / r

"Dave Peterson" wrote:

#3. You could have a macro that runs each time the workbook is opened that
checks a list of names on a hidden worksheet. If the user's name isn't there
(either their network id or their tools|Options|general id), then the macro
could close the workbook.

But macros don't have to be enabled. Or the auto open macros could be avoided.
So this isn't very secure.

#4. Same kind of thing, the user opens the workbook with macros enabled, but
with all the worksheets hidden. When they type something into that "Special"
cell, some macro runs and validates that entry.

This also suffers the same problem as the others. Macros have to be enabled and
worksheets can be unhidden pretty easily for the really curious user.

#5. (You didn't ask!). Password protect the workbook (file|SaveAs|Tools, and
give it a password to open). But then use another helper workbook that has all
the validation/macros in it. If the user passes the validity, then the code in
the helper workbook opens the real workbook (and supplies that password).

But the bad news here is that the helper workbook's code can be broken pretty
easily, too. And once the password is out there, then the users won't need that
helper workbook to open the real workbook.

If you really have sensitive data, don't put it into excel. If you have to put
it into excel, don't share it with anyone you don't trust.



vcff wrote:

I read a post with the following reply:

As Dave said, you can't "really" make Excel so secure that at competent
person cannot get in, but you "can" make the trip in so confounding that
only a truely dedicated individual will go through all the necessaries to get
there.........thereby, "keeping the honest people out"....
You could incorporate things like: 1-workbook won't open unless macros are
enabled, 2- Pass word(s) are invoked, 3-Only users with name on a list can
access, 4- only user putting secret value into specific cell can gain access,
etc etc........

I will like to know more on the option No. 3 & 4. Can anyone help me on this.
Tnks / r

--

Dave Peterson

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

If Target.Value = "hi TheRE" Then '<--it's case sensitive
For Each wks In ThisWorkbook.Worksheets
wks.Visible = xlSheetVisible
Next wks
End If
End Sub

This goes behind the worksheet that "owns" the cell--and I used A1.

If you want to learn about events...

Chip Pearson has notes about worksheet events:
http://www.cpearson.com/excel/events.htm

David McRitchie's notes:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




vcff wrote:

Hi Dave
tnks for the help. Can you provide the macro for #4 as I am new to all this.
Once again Tnks / r

"Dave Peterson" wrote:

#3. You could have a macro that runs each time the workbook is opened that
checks a list of names on a hidden worksheet. If the user's name isn't there
(either their network id or their tools|Options|general id), then the macro
could close the workbook.

But macros don't have to be enabled. Or the auto open macros could be avoided.
So this isn't very secure.

#4. Same kind of thing, the user opens the workbook with macros enabled, but
with all the worksheets hidden. When they type something into that "Special"
cell, some macro runs and validates that entry.

This also suffers the same problem as the others. Macros have to be enabled and
worksheets can be unhidden pretty easily for the really curious user.

#5. (You didn't ask!). Password protect the workbook (file|SaveAs|Tools, and
give it a password to open). But then use another helper workbook that has all
the validation/macros in it. If the user passes the validity, then the code in
the helper workbook opens the real workbook (and supplies that password).

But the bad news here is that the helper workbook's code can be broken pretty
easily, too. And once the password is out there, then the users won't need that
helper workbook to open the real workbook.

If you really have sensitive data, don't put it into excel. If you have to put
it into excel, don't share it with anyone you don't trust.



vcff wrote:

I read a post with the following reply:

As Dave said, you can't "really" make Excel so secure that at competent
person cannot get in, but you "can" make the trip in so confounding that
only a truely dedicated individual will go through all the necessaries to get
there.........thereby, "keeping the honest people out"....
You could incorporate things like: 1-workbook won't open unless macros are
enabled, 2- Pass word(s) are invoked, 3-Only users with name on a list can
access, 4- only user putting secret value into specific cell can gain access,
etc etc........

I will like to know more on the option No. 3 & 4. Can anyone help me on this.
Tnks / r

--

Dave Peterson


--

Dave Peterson

Dave, tnks for your help. Have a nice day.

"Dave Peterson" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim wks As Worksheet

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

If Target.Value = "hi TheRE" Then '<--it's case sensitive
For Each wks In ThisWorkbook.Worksheets
wks.Visible = xlSheetVisible
Next wks
End If
End Sub

This goes behind the worksheet that "owns" the cell--and I used A1.

If you want to learn about events...

Chip Pearson has notes about worksheet events:
http://www.cpearson.com/excel/events.htm

David McRitchie's notes:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm




vcff wrote:

Hi Dave
tnks for the help. Can you provide the macro for #4 as I am new to all this.
Once again Tnks / r

"Dave Peterson" wrote:

#3. You could have a macro that runs each time the workbook is opened that
checks a list of names on a hidden worksheet. If the user's name isn't there
(either their network id or their tools|Options|general id), then the macro
could close the workbook.

But macros don't have to be enabled. Or the auto open macros could be avoided.
So this isn't very secure.

#4. Same kind of thing, the user opens the workbook with macros enabled, but
with all the worksheets hidden. When they type something into that "Special"
cell, some macro runs and validates that entry.

This also suffers the same problem as the others. Macros have to be enabled and
worksheets can be unhidden pretty easily for the really curious user.

#5. (You didn't ask!). Password protect the workbook (file|SaveAs|Tools, and
give it a password to open). But then use another helper workbook that has all
the validation/macros in it. If the user passes the validity, then the code in
the helper workbook opens the real workbook (and supplies that password).

But the bad news here is that the helper workbook's code can be broken pretty
easily, too. And once the password is out there, then the users won't need that
helper workbook to open the real workbook.

If you really have sensitive data, don't put it into excel. If you have to put
it into excel, don't share it with anyone you don't trust.



vcff wrote:

I read a post with the following reply:

As Dave said, you can't "really" make Excel so secure that at competent
person cannot get in, but you "can" make the trip in so confounding that
only a truely dedicated individual will go through all the necessaries to get
there.........thereby, "keeping the honest people out"....
You could incorporate things like: 1-workbook won't open unless macros are
enabled, 2- Pass word(s) are invoked, 3-Only users with name on a list can
access, 4- only user putting secret value into specific cell can gain access,
etc etc........

I will like to know more on the option No. 3 & 4. Can anyone help me on this.
Tnks / r

--

Dave Peterson


--

Dave Peterson