JawD 31 Posted June 21, 2007 Share Posted June 21, 2007 So, here are the things I want to be able to do Say I have sheet 1 sheet 2 sheet 3 Sheet 1 and 2 will be main contents Sheet 3 will be a reference sheet that the other two sheets refer to Sheets 1 and 2 will contain formula to pull information from sheet 3 I need to be able to: i. Lock sheet 1 and 2 from editting or viewing forumula ii. Hide sheet 3 completely so it cant be viewed iii. retain the use of a hyperlinik between sheets 1 and 2 What I have sussed so far: i. By adding sheet protection, I can stop lock cells from being selected and hide their forumla (dont this via cell properties, protection, locked and hidden) Ive added a password to the sheet protection. ii. Havent worked out how to hide a sheet yet iii. By hiding/locking the cells it seems to do the same to the hyperlink? How do I keep the sheet protection but also keep the use of a hyperlink? I dont seem to be able to protect a range of cells, only the whole sheet? Cheers Link to comment Share on other sites More sharing options...
Craig 6287 Posted June 21, 2007 Share Posted June 21, 2007 Hiding a sheet is simply a case of being on the sheet in question, going to the Format menu, selecting Sheet and then Hide For the last bit, highlight the cells you don't want protected, go to the b]Format[/b] menu, select Cells and then the Protection tab. Clear the tick in the 'Locked' check-box. Hope this helps. Link to comment Share on other sites More sharing options...
JawD 31 Posted June 21, 2007 Author Share Posted June 21, 2007 Aye, with a bit off messing around, thats seemed to have done the trick. Cheers Link to comment Share on other sites More sharing options...
JawD 31 Posted July 27, 2007 Author Share Posted July 27, 2007 Ok, more on this. I now have my workbook which consists of around 40+ sheets. I want to distribute the workbook but do not want the user to be able to modify any cell. But they should be able to follow hyperlinks within the workbook. The way I have found I have do this is to protect sheet > Tick Select Locked Cells, Tick Select Unlocked Cells, Untick Edit Objects, Untick Edit Scenrios > Add password > confirm Password Now, this does exactly what I want it to do, except I cant be arsed to go through every sheet and repeat this. So I need a way to do them all at once. You cant select multiple sheets and do this for some reason. I suspect there is some code you can enter (which you right click and chose view code) is visual basic but I aint got a clue about that. Anyone any ideas? Cheers Link to comment Share on other sites More sharing options...
Gemmill 34166 Posted July 27, 2007 Share Posted July 27, 2007 Can you not do it using "protect workbook" instead of "protect sheet"? Link to comment Share on other sites More sharing options...
peasepud 59 Posted July 27, 2007 Share Posted July 27, 2007 Dim Sheetnumber Sheetnumber = 1 Do ActiveWorkbook.Worksheets(Sheetnumber).Activate ActiveWorkbook.Worksheets(Sheetnumber).Protect "enter password here" Sheetnumber = Sheetnumber + 1 Loop Until Sheetnumber = ActiveWorkbook.Worksheets.Count ActiveWorkbook.Protect "enter password here" Link to comment Share on other sites More sharing options...
JawD 31 Posted July 27, 2007 Author Share Posted July 27, 2007 @ Gem - Dont think you can for some odd reason @ PP - Cheers! now where do I dump that? I assume in the code somewhere? Im running office 07. ta Link to comment Share on other sites More sharing options...
rikko 20 Posted July 27, 2007 Share Posted July 27, 2007 Just go to record macro, do all that stuff, save the macro. Then re-run the macro on each page. Will only be pressing one button combo to it then. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now