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

[Solved] lock cell based on validation of another cell

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


Joined: 28 May 2012
Posts: 7

PostPosted: Mon May 28, 2012 2:45 pm    Post subject: [Solved] lock cell based on validation of another cell Reply with quote

hi,

im very new to openoffice and i would appreciate ur help on this.
case is, when a user chooses a value for cell B14 (which has data validation), I want cells D20, D23 and D26 locked if the value chosen is anything other than "Quality".
that is, if user chooses "Audio Gap", "Disconnect", or "Other", cells D20, D23 and D26 will be locked.

this is for every instance B14 is changed. i don't want to forever lock D20, 23 and 26. for instance, if user selects "Disconnect" but then changes his mind and selects "Quality", end result is D20, 23 and 26 are unlocked. i hope that was clear Smile

i've only been able to create a macro to lock one cell. i couldn't even relate it to changes made to B14 Crying or Very sad please help! thanks so much!

here's what i was able to create so far:
Code:

sub testlock
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$D$20"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(3) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Protection.Locked"
args2(0).Value = true
args2(1).Name = "Protection.FormulasHidden"
args2(1).Value = false
args2(2).Name = "Protection.Hidden"
args2(2).Value = false
args2(3).Name = "Protection.HiddenInPrintout"
args2(3).Value = false

dispatcher.executeDispatch(document, ".uno:Protection", "", 0, args2())


end sub


Last edited by anil11 on Tue May 29, 2012 1:40 pm; edited 2 times in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon May 28, 2012 11:02 pm    Post subject: Reply with quote

http://www.mediafire.com/file/81tx0t8a3ylzk0a/conditional_locking.ods
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
anil11
General User
General User


Joined: 28 May 2012
Posts: 7

PostPosted: Tue May 29, 2012 5:49 am    Post subject: Reply with quote

omg that was awesome! u're a god Very Happy ill try incorporating it with my template. thanks so much!

btw, i can't view the code u used. i was hoping i could see and study it Smile
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 29, 2012 5:57 am    Post subject: Reply with quote

There is no code at all. Look at the conditional formatting of the cells I erroneously labeled as "Validation".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
anil11
General User
General User


Joined: 28 May 2012
Posts: 7

PostPosted: Tue May 29, 2012 6:00 am    Post subject: Reply with quote

Shocked deym i didn't think u cud do that through conditional formatting. thanks thanks! Very Happy
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 29, 2012 6:10 am    Post subject: Reply with quote

anil11 wrote:
:shock: deym i didn't think u cud do that through conditional formatting. thanks thanks! :D

Actually, it has to be a combination of conditional formatting with function STYLE.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
anil11
General User
General User


Joined: 28 May 2012
Posts: 7

PostPosted: Tue May 29, 2012 7:06 am    Post subject: Reply with quote

i encountered an issue. i placed data validation on cells D20, 23 and 26 (1,2,3).
if i choose "Quality", cells D20, 23 and 26 get unlocked right, so i get to choose from another data validation in D20, say "2". so D20 contains "2"
but then lets say i changed my mind. i go back to cell B14 and choose something that will lock my cells, say option "A" in your file. but D20 contains "2". i could not delete the data anymore in that cell since that is already locked.
is it possible to delete entries in or to empty D20, 23 and 26 when a user changes B14 to something other than "Quality"?
Back to top
View user's profile Send private message
anil11
General User
General User


Joined: 28 May 2012
Posts: 7

PostPosted: Tue May 29, 2012 11:43 am    Post subject: Reply with quote

hi,
this is fixed now. i just tweaked the style formatting for "locked"
thanks so much! Very Happy
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