PDA

View Full Version : Solved: Sheet Security



SureshSuresh
01-17-2007, 11:11 PM
Hi!
I came back with another doubt, i want to put security to my sheets. In detail, I want to give authentication to each sheet to a particular user and one administrator can access total sheets. How can I do this? Help me to do this!
Thanks in advance,

matthewspatrick
01-18-2007, 06:45 AM
You can do this by implementing worksbook and/or worksheet level passwords (depends on what you need to do, exactly).

However, I urge you not to do it. Passwords for Excel's protection features can be cracked in seconds using widely available, cheap tools. Ditto passwords for VBA projects. Excel and VBA offer no security worthy of the name.

If you really need to have that level of security, you should be looking at other applications.

Charlize
01-18-2007, 06:54 AM
I will try to find something in my files. I know I have something that uses a thing called blowfish algorithm. When a special phrase is used the data is encrypted on the sheet into some rubbish. Don't know if it came from here or somewhere else.

Charlize

lucas
01-18-2007, 08:52 AM
As Patrick points out this method is not secure but if you just wish to keep people in the office working on their own sheet and security is not a huge issue this workbook does what you asked.

You will have to set up a new front sheet as one sheet will always have to show and you will have to hide the passwords sheet...also I would have the form open on workbook open...good luck with it.

SureshSuresh
01-30-2007, 05:18 AM
Hi!
can any body help me to pick total data of one label in another sheet? I mean, i have one table in one sheet i want to retrieve data of a column using its lable(column heading) in another sheet, please help to do this. Thanks in advance!
SureshP

Bob Phillips
01-30-2007, 05:48 AM
=INDEX(Sheet2!A:M,5,MATCH("value",Sheet2!A1:M1,0))

pulls from the 5th row in the matching column

SureshSuresh
01-30-2007, 02:09 PM
Thankyou Lucas! for your suggestion. I got your answer, but i didit and succeeded. Actually I want to give the list from another sheet, I am unable to do this. Help me!
SureshP

lucas
01-30-2007, 02:13 PM
Hi SureshP,
The best way to pull it from another sheet is to use a named range.....I will put a small example together with explainations for you in a few minutes......I assume its the same as the detail I gave you except you wish to pull your list from a different sheet...correct?

lucas
01-30-2007, 02:34 PM
Another alternative to using a named range is to use indirect...in the data validation box after you select list in the dropdown....paste this in the source box:

=INDIRECT("Sheet2!A1:A10")


sheet 2 is the sheet the list is on and A1:A10 is the range of the list.


The first method I suggested is on the sheet with the list select
insert-name-define....click the icon at the right on the window in the bottom of the dialog to get the range into the box.

name your range. If it's named myrange then in the data validation box you would use =myrange as the source.

These last few posts are in response to a pm by SureshP with a question about setting up data validation dropdowns in cells.

SureshSuresh
01-30-2007, 02:40 PM
I succeeded! Thankyou very much Lucas! for your answer!

lucas
01-30-2007, 02:49 PM
Your welcome SureshP,
If you have your answers for this thread be sure to mark it solved using the thread tools at the top of the page. You can always post followup questions here if you need to.

The list ranges can also be made dynamic..meaning you can add items to the list and they will be included in the dropdown automatically....let us know if you need more help.

lucas
01-30-2007, 03:30 PM
Hi Suresh,
I reset the title of the thread so that folks can search for it.

When you use the thread tools to mark your thread solved look for the line "Mark thread solved" That's the easiest way.....:hi:

Wolfgang
01-31-2007, 08:03 AM
Hi Charlize...

would you please go on and look through your files for this particular method for me?!

Sounds highly interesting...

Thank you very much...

Best,
Wolfgang

Bob Phillips
01-31-2007, 08:17 AM
What, dynamic ranges? Just use

=OFFSET($A$1,,,COUNTA($A:$A))

in the DV list box

lucas
01-31-2007, 08:25 AM
I agree Bob, sometimes I use this sheetchange event to do the same thing......either method works just as well....true?
Private Sub Worksheet_Change(ByVal Target As Range)

Dim n As Long

n = Sheets("Lists").Range("A65536").End(xlUp).Row
ThisWorkbook.Names.Add Name:="MyList", RefersTo:=Sheets("Lists").Range("A1:A" & n)

End Sub

Bob Phillips
01-31-2007, 08:38 AM
Sheetchange of course has the advantage of only kicking in when there is a change, not on every sheet calculation.

If you are using worksheet event code though, you should take advantage of built-in information to make it more flexible



n = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
ThisWorkbook.Names.Add Name:="MyList", RefersTo:=Worksheets(Me.Name).Range("A1:A" & n)

lucas
01-31-2007, 08:48 AM
Ok, that would have the advantage of not having to change the sheet name in the code each time you use it on a different sheet and would take advantage of later versions of excel that have more rows....right so far? I can see the advantages and will adapt my code.

Bob Phillips
01-31-2007, 09:03 AM
Correct, but you missed the big advantage as I see it that it will still work if the user changes the sheet name.

Bob Phillips
01-31-2007, 09:07 AM
You can even make it better/simpler



n = Me.Range("H" & Me.Rows.Count).End(xlUp).Row
Me.Range("H1:H" & n).Name = "MyList"


Just thought of another advantage of your approach, it caters for blanks in the range, the OFFSET method really struggles with that.

lucas
01-31-2007, 09:21 AM
Correct, but you missed the big advantage as I see it that it will still work if the user changes the sheet name.

Yes, another definate advantage.....I have yoinked the last code you posted also....works great.