| View previous topic :: View next topic |
| Author |
Message |
anil11
General User
Joined: 28 May 2012
Posts: 7
|
 Posted: Mon May 28, 2012 2:45 pm Post subject: [Solved] lock cell based on validation of another cell |
 |
|
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 
i've only been able to create a macro to lock one cell. i couldn't even relate it to changes made to B14  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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
anil11
General User
Joined: 28 May 2012
Posts: 7
|
| Posted: Tue May 29, 2012 5:49 am Post subject: |
|
|
omg that was awesome! u're a god  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 |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue May 29, 2012 5:57 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
anil11
General User
Joined: 28 May 2012
Posts: 7
|
| Posted: Tue May 29, 2012 6:00 am Post subject: |
|
|
 deym i didn't think u cud do that through conditional formatting. thanks thanks! |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue May 29, 2012 6:10 am Post subject: |
|
|
| 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 http://forum.openoffice.org |
|
| Back to top |
|
|
anil11
General User
Joined: 28 May 2012
Posts: 7
|
| Posted: Tue May 29, 2012 7:06 am Post subject: |
|
|
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 |
|
|
anil11
General User
Joined: 28 May 2012
Posts: 7
|
| Posted: Tue May 29, 2012 11:43 am Post subject: |
|
|
hi,
this is fixed now. i just tweaked the style formatting for "locked"
thanks so much! |
|
| Back to top |
|
|
|
