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

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

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

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/

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

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

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

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

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

Controls in protected sheet
 

anyone on this yet...

--
Message posted from http://www.ExcelForum.com

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

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/

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

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/

.

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

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

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

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

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