Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Is it not possible to use controls in a protected sheet. My comboboxe
and checkboxes are not working in a protected sheet. How do I make the work. - Manges -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Do you have linked cells for each of your controls on that same worksheet?
If yes, are those link cells locked or unlocked? (Either unlock them or move them to a new sheet (hidden, perhaps???).) "mangesh_yadav <" wrote: Is it not possible to use controls in a protected sheet. My comboboxes and checkboxes are not working in a protected sheet. How do I make them work. - Mangesh --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
"mangesh_yadav " wrote in
message ... Is it not possible to use controls in a protected sheet. My comboboxes and checkboxes are not working in a protected sheet. How do I make them work. Try adding this to the Workbook.Open event: With Sheets("Sheet1") 'or whatever your sheet is called .EnableAutoFilter = True 'remove this line if you are not using an autofilter .Protect DrawingObjects:=True, _ contents:=True, Scenarios:=True, UserInterfaceOnly:=True End With Please post back if this works. Iain King |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
I have unlocked the cells linked to these controls. And they are on the
same sheet. Mangesh --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Infact the moment I click on one of these controls, I get a messag
saying that the worksheet is protected (even before the macro linked t the control is executed). Manges -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Hi Iain,
Thanks for the reply. I tried your solution, but even that does not work. - Manges -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
"mangesh_yadav " wrote in message ... I have unlocked the cells linked to these controls. And they are on the same sheet. You're not in Select Objects mode, are you? Afraid that's all I can think of.... Iain King |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
What "select objects" mode...?
The problem is: currently the application even does it is supposed t do when I click a checkbox (for instance). But when I click on th checkbox, I get the message saying that the sheet is protected. And i fails to change the status of the linked cell even though the cell i added to the range of AllowEdit Ranges. - Manges -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
What "select objects" mode...? It'sa mode used for editting components on a sheet. It's not what's wrong here, as you've said. The problem is: currently the application even does it is supposed to do when I click a checkbox (for instance). But when I click on the checkbox, I get the message saying that the sheet is protected. And it fails to change the status of the linked cell even though the cell is added to the range of AllowEdit Ranges. Does the checkbox change state (i.e., gain or lose the tick)? It sounds like the checkbox is locked with the sheet protected. So, just to be clear - is the sheet protected? If so, what specific protections are applied? Generally, you can (and should) Lock controls so that the user cannot edit their properties/ text. users can still use locked controls (usually). Is there an event triggering on the checkbox being clicked? Such an event might be trying to write to a protected cell... Can you post any code which is attached to the checkbox? Iain King |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Yes, I am not in any select mode.
Ok as for an example: I have this checkbox from the Forms menu. Th linked cell is also on the same sheet. I have unlocked the checkbox i its properties (format control protection). I have locked the sheet with the following options. inpSht.Protect DrawingObjects:=True, contents:=True, _ Scenarios:=True AllowFormattingCells:=True (i have also tried the above with UserInterfaceOnly:=True) The state of the checkbox does not change after I click. The moment click, I get the protected msg. And thats all. If I have a macr attached then the macro gets executed. Manges -- Message posted from http://www.ExcelForum.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
The message indicates the cell linked to the control is locked and the sheet
protected You might check this again. in you own words: click on format cells protection [un]check the box 'Locked' click on Tools protection protect sheet -- Regards, Tom Ogilvy "mangesh_yadav " wrote in message ... anyone on this yet...? --- Message posted from http://www.ExcelForum.com/ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Ok as for an example: I have this checkbox from the Forms menu. The linked cell is also on the same sheet. I have unlocked the checkbox in its properties (format control protection). A checkbox can be locked and still work - the checkbox will toggle, but the user will be unable to view it's properties/ edit it in any way. I have locked the sheet with the following options. inpSht.Protect DrawingObjects:=True, contents:=True, _ Scenarios:=True, AllowFormattingCells:=True (i have also tried the above with UserInterfaceOnly:=True) userinterfaceonly at true means that while the user is prohibited from doing certain things, any macro you write will be unrestricted. The state of the checkbox does not change after I click. The moment I click, I get the protected msg. And thats all. If I have a macro attached then the macro gets executed. can you post the code from the attached macro? Have you tried unlinking it from the cell - does it work without an error then? Link it somewhere else and test it. Relink it where it should be and test it. Iain King |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Hi, Before you add sheet protection, format the cells using the 'Protection' tab and unlock the cells. Regards, GS -----Original Message----- Is it not possible to use controls in a protected sheet. My comboboxes and checkboxes are not working in a protected sheet. How do I make them work. - Mangesh --- Message posted from http://www.ExcelForum.com/ . |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Take a look at the linked cell--not the control itself.
Select one of the linked cells. Format|cells|protection tab Uncheck the Locked option. "mangesh_yadav <" wrote: Hi Iain, Thanks for the reply. I tried your solution, but even that does not work. - Mangesh --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Solution (or you can say avoiding the problem):
I might have learned the hard way but maybe thats how it works. unlinked the cells from the controls and re-linked them through th code for the control (i.e. on_Click or on_Change). This way it does no give any error. Only when the control has a cell linked directly throug its properties, it gives the problem. But without changing anything a all, I just unlinked it and wrote it down in its code and it work perfectly fine. Any comments on this. Anyway thanks for all the help. Manges -- Message posted from http://www.ExcelForum.com |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
Just realised one more mistake I was doing as you all pointed out to me
Till now I was working through AllowEditRanges. These work fine even i the cell is locked. But the controls with linked cells do not work thi way. They require that the cells are unlocked when you are usin protection. Thanks to all of you. Manges -- Message posted from http://www.ExcelForum.com |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
I have unlocked the cells linked to these controls. And they are on the
same sheet. Mangesh but I agree, he should take another look. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Take a look at the linked cell--not the control itself. Select one of the linked cells. Format|cells|protection tab Uncheck the Locked option. "mangesh_yadav <" wrote: Hi Iain, Thanks for the reply. I tried your solution, but even that does not work. - Mangesh --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controls in protected sheet
His double/triple? checking paid off.
Tom Ogilvy wrote: I have unlocked the cells linked to these controls. And they are on the same sheet. Mangesh but I agree, he should take another look. -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... Take a look at the linked cell--not the control itself. Select one of the linked cells. Format|cells|protection tab Uncheck the Locked option. "mangesh_yadav <" wrote: Hi Iain, Thanks for the reply. I tried your solution, but even that does not work. - Mangesh --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to activate controls in protected sheet | Excel Worksheet Functions | |||
Need more than 1208 controls per sheet | Excel Discussion (Misc queries) | |||
Unable to use controls when sheet is protected | Excel Programming | |||
Unable to use controls when sheet is protected | Excel Programming | |||
Counting Controls in a sheet | Excel Programming |