| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 57496 | 2005-05-04 05:47:00 | Excel - clashes with locking cells and tool buttons, combo boxes etc. | Mike S (1766) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 352006 | 2005-05-04 05:47:00 | After struggling with combo boxes and how to get them working I’ve now arrived at a spreadsheet I’m happy with. It’s intended for others to use by entering data and thus avoiding difficult and time-consuming number crunching for them. When doing spreadsheets for others to use, experience has taught me to lock ‘em up solid so people only are able to tinker with the cells I want them to. It saves calls of the “I’ve just done so-and-so and it’s gone all funny” type. On this occasion I have included functions from the ‘Form’ toolbar and everything works beautifully. These functions include Option buttons, a Combo box and a Scroll bar. Here, though, I appear to have made a rod for my own back, since when I lock the worksheet the tool buttons and so forth won’t function. I tried unlocking each function device but that doesn’t allow them to be of use but it does allow tinkering or worse, deletion. Is there a way of making everything tamper proof but still being able to operate buttons, boxes and bars? |
Mike S (1766) | ||
| 352007 | 2005-05-04 07:27:00 | The output cell (the cell specified in the cell link field found under format control) must be unlocked so that a combo box etc can change its value. To do this select the cell that will be changed and select 'format' then 'cells' then select the 'protection' tab on the dialog box that opens and unselect the 'locked' check box | Steven (7085) | ||
| 352008 | 2005-05-04 09:18:00 | Hi Mike, Steven has given you the answer but you have found one of the issues with using Forms controls . While they are very easy to create and use they rely upon using cells to store the list of values and to return the result . However, you can use another sheet to store your list values and the returned result or be sneaky and place the returned value underneath the control so it cant be seen . If you store in a separate sheet you have the same issues with protection however it can be hidden to avoid casual mistakes by Users . You can make the sheet "very hidden" if you want which prevents the sheet being unhidden via the noraml fashion - menu option Format|Sheet|Unhide . To do this open the Visual Basic Editor (Alt+F11 or Tools|Macro|Visual Basic Editor) then in the left hand window pane titled Project - VBA Project you should see a list of your sheets . NB: you may need to expand the Microsoft Excel Objects folder to see them . Left click the sheet once to select it then F4 to view the properties window for the sheet . In the property named Visible select the very hidden option from the drop down then close the VBE by selecting Alt-Q or File|Close . Save the workbook . hth |
Parry (5696) | ||
| 352009 | 2005-05-05 03:42:00 | Thanks for the help guys. It now works perfectly: I’m delighted. I hide formulae of course and I like the idea of hiding those value cells behind one of the tools. In the past I’ve hidden stuff hiding the relevant rows or columns and making sure I put such data where I can easily do that. I have also put “secret” cell entries way over in AA500 cell or whatever and a further ruse is to use white for the type colour. Even further over on the spreadsheet I put a space character, well away from the white type, so if they go Ctrl/End to the bottom right corner of the worksheet there’s nothing to see. I’ve yet to find a way of preventing white type being read when doing Ctrl A, except using black type in a black-filled cell - but that draws attention to that cell anyway! The “Very hidden” option looks very good but I haven’t tried it yet. |
Mike S (1766) | ||
| 352010 | 2005-05-05 06:54:00 | Dear ‘Parry’, I have now tried the ‘Very Hidden’ option. I followed instructions, and didn’t immediately get to where you said, but after a little playing (remembering Visual Basic is like a foreign land to me), I did get to where you wanted me to be – and I see the Very Hidden option. Now I know how to get there. However, even without the 'Very Hidden' option, I find that if I protect the workbook I cannot find a way of un-hiding the hidden worksheet anyway (Format/Sheet gives a panel with Unhide greyed out and inoperative). I’ve missed something: what is it? |
Mike S (1766) | ||
| 352011 | 2005-05-05 07:32:00 | Dear ‘Parry’, I have now tried the ‘Very Hidden’ option . I followed instructions, and didn’t immediately get to where you said, but after a little playing (remembering Visual Basic is like a foreign land to me), I did get to where you wanted me to be – and I see the Very Hidden option . Now I know how to get there . However, even without the 'Very Hidden' option, I find that if I protect the workbook I cannot find a way of un-hiding the hidden worksheet anyway (Format/Sheet gives a panel with Unhide greyed out and inoperative) . I’ve missed something: what is it? Hi Mike, to make the sheet visible again you simply follow the same procedure in the VBEditor but choose the option visible instead of very hidden . The very hidden option makes Unhide greyed out and inoperative . :-) It can also be done with a macro but the macro should be hidden requiring you to have some method to run it - either direct from the VBE or the macro asking for a password before proceeding . With Forms controls you must have the cell that is linked (the one that returns the result) unprotected or else the value in the cell cant change . Therefore you can actually protect the sheet that you hide but ensure that cell is unlocked . Just simply hiding it may be enough for you rather than making it very hidden . The security in Excel is like a wet paper bag for people who know a reasonable amount about Excel and its faults but is sufficient to stop the casual person wanting to nosey around . If you were using ActiveX controls (the ones requiring code) you dont actually need a list in the sheet or somewhere to store the returned value if you dont want to and you can unprotect, do something, then reprotect on the fly as required . regards, Graham |
Parry (5696) | ||
| 352012 | 2005-05-05 22:19:00 | Dear Graham, I think we’re at slight cross-purposes on this last wee point. I couldn’t see the difference between: a) Greying out unhide by protecting the sheet b) Greying out unhide by using ‘Very hidden’ Clearly, using both will be even better though! Anyway, you’ve certainly cleared away all my problems. Great stuff - many thanks. Mike |
Mike S (1766) | ||
| 352013 | 2005-05-05 23:09:00 | There are different levels of protection . If you protect the workbook and select the option to protect the structure, then yes previously hidden sheets cannot be unhidden . If however, you protect the sheet then hide it the option to unhide the sheet is still available as the protection was for the sheet itself and not the structure of the workbook . Sounds like you have something that works for you so thats cool . I was just offering some other alternatives . :-) |
Parry (5696) | ||
| 352014 | 2005-05-05 23:46:00 | Hi Graham, Ahaaaa! (Light bulb has just gone on!!!) Mike |
Mike S (1766) | ||
| 1 | |||||