Controls in protected sheet

[mangesh_yadav]

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

 

[Dave Peterson]

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???).)

 

[Iain King]

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

 

[mangesh_yadav]

I have unlocked the cells linked to these controls. And they are on the
same sheet.

Mangesh

 

[mangesh_yadav]

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

 

[mangesh_yadav]

Hi Iain,

Thanks for the reply.
I tried your solution, but even that does not work.

- Manges

 

[Iain King]

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

 

[mangesh_yadav]

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

 

[Iain King]

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

 

[mangesh_yadav]

anyone on this yet...

 

[mangesh_yadav]

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

 

[Tom Ogilvy]

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

 

[Iain King]

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

 

[Garry]

Hi,
Before you add sheet protection, format the cells using
the 'Protection' tab and unlock the cells.
Regards, GS

 

[Dave Peterson]

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]

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

 

[mangesh_yadav]

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

 

[Tom Ogilvy]

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.

 

[Dave Peterson]

His double/triple? checking paid off.

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.