OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Functionality of forms when a sheet is protected.

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Talyvar
General User
General User


Joined: 14 Sep 2006
Posts: 8
Location: Australia

PostPosted: Thu Sep 21, 2006 1:24 am    Post subject: Functionality of forms when a sheet is protected. Reply with quote

Hi everyone!

I am looking for a way to protect a sheet, but still have forms and the associated calculations function. I am using Open Office 2.0.3 for windows xp sp2.
I use dropdown lists, scroll bars and tick boxes.
I have visible numbers in a large number of cells that are calculated with various formulas that depend on the choices made with the forms.
When I protect the sheet, the forms are selectable and still alter, e.g. a dropdown lists choice will change, but none of the calculations update.
The only objects on the sheet that a user needs to interact with are the forms and I need to have everything else protected.
Choosing not to protect the cells where the forms are located doesn't change the result and I could not find a similar option specifically for the forms.

Any ideas on what I can try to resolve this will be greatly appreciated, thanks Smile
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Sep 21, 2006 3:10 am    Post subject: Reply with quote

Make sure automatic calulation is on?

(Tools, Cell Contents...)
Back to top
View user's profile Send private message
Talyvar
General User
General User


Joined: 14 Sep 2006
Posts: 8
Location: Australia

PostPosted: Thu Sep 21, 2006 3:25 am    Post subject: Reply with quote

I just checked that and yes it is on and dosen't seem to effect the result.
I also tried to recalculate, using F9, but that also doesn't wok.
Thanks for the idea though Smile
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Thu Sep 21, 2006 4:28 am    Post subject: Reply with quote

I think Recalc in Calc is Ctrl-Shift-F9, or some such combination.

You could post the file somewhere (mytempdir.com?) for people to download if it isn't confidential?
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Thu Sep 21, 2006 5:13 am    Post subject: Reply with quote

That's the correct key combination to force a recalculation.

I don't use forms. On principle, it seems appropriate to me that you cannot use them to alter protected cells.

Is it feasible to have the forms and data entry cells on a separate sheet?
_________________
search forum by month
Back to top
View user's profile Send private message
Talyvar
General User
General User


Joined: 14 Sep 2006
Posts: 8
Location: Australia

PostPosted: Thu Sep 21, 2006 4:16 pm    Post subject: Reply with quote

Sorry, the spreadsheet is confidential or I would be happy to post it for analysis.
Simply put, in a row there is a name cell, then the scroll bar to select a value followed by its linked cell and 4 different calculations that are dependent on the scroll bar's linked cell value. At the end of the row there is a tick box to select these values to be used in a totals box. I need te user to all teh cells in a row at the same time. Hence I need all the items on the one sheet.
I guess the issue is really, how can you keep everything on a sheet protected except for the cells which are designed for the user to select, and still have the protected cells do their calculations? I want to protect the other cells so no one accidentally deletes the values or changes any of the formulas in place.
Is it assumed that the only cells that a user would need active on a protected sheet are those selected as not protected? (At least if the sheet is not designed to be read only.)
It would seem to me that it is most likely that a protected cell would be protected to avoid its contents being changed, not to stop them functioning. Of course this is 'most likely' to me because that is how I use spreadsheets, so I recognise I may be misunderstanding the intended function of protection.
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Thu Sep 21, 2006 5:12 pm    Post subject: Reply with quote

Unprotect the spreadsheet.
Select the cell(s) that you do not want protected (I haven't used forms, but I think these will be all the cells to which the form controls link)
Format > Cells
Select the Cell Protection tab
Uncheck Protected
Protect the spreadsheet.

Formulae in protected cells should update when the cells to which they refer are edited.
I'm guessing that the cell protection is preventing the contents of linked cells from updating when the user edits a form control (protection -> prevent user changes).
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
Talyvar
General User
General User


Joined: 14 Sep 2006
Posts: 8
Location: Australia

PostPosted: Thu Sep 21, 2006 6:18 pm    Post subject: Reply with quote

AHA!
Yes, as long as the linked cell is not protected it all seems to work fine.
So I will insert another column which copies the linked cells value, allowing the user to see the value but allowing me to have the viewed cell protected. I will then hide the column the linked cells are in.
This should work nicely!
Thank you for the solution!! Very Happy
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Thu Sep 21, 2006 7:37 pm    Post subject: Reply with quote

It's nice to be appreciated, but thank Noranthon: he got there first. Wink
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
Talyvar
General User
General User


Joined: 14 Sep 2006
Posts: 8
Location: Australia

PostPosted: Fri Sep 22, 2006 2:22 am    Post subject: Reply with quote

Well, I would then like to thank each of you who helped me with this issue!
I have never been part of a forum before and I am very impressed with how swiftly a resolution was reached. I wasn't sure how long it would take to figure this out and I appreciate it heaps.
You guys are great!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group