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,
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.
Regards,
Patrick
I wept for myself because I had no PivotTable.
Then I met a man who had no AutoFilter.
Microsoft MVP for Excel, 2007 & 2008
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
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
=INDEX(Sheet2!A:M,5,MATCH("value",Sheet2!A1:M1,0))
pulls from the 5th row in the matching column
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
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?
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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:
[VBA]
=INDIRECT("Sheet2!A1:A10")
[/VBA]
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
I succeeded! Thankyou very much Lucas! for your answer!
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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.....
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
What, dynamic ranges? Just use
=OFFSET($A$1,,,COUNTA($A:$A))
in the DV list box
I agree Bob, sometimes I use this sheetchange event to do the same thing......either method works just as well....true?
[VBA]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
[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
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
[vba]
n = Me.Range("A" & Me.Rows.Count).End(xlUp).Row
ThisWorkbook.Names.Add Name:="MyList", RefersTo:=Worksheets(Me.Name).Range("A1:A" & n)
[/vba]
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.
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Correct, but you missed the big advantage as I see it that it will still work if the user changes the sheet name.
You can even make it better/simpler
[vba]
n = Me.Range("H" & Me.Rows.Count).End(xlUp).Row
Me.Range("H1:H" & n).Name = "MyList"
[/vba]
Just thought of another advantage of your approach, it caters for blanks in the range, the OFFSET method really struggles with that.
Yes, another definate advantage.....I have yoinked the last code you posted also....works great.Originally Posted by xld
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Posting Permissions
Reply With Quote
