Protected cells cannot be modified.

Creating a macro - Writing a Script - Using the API

Protected cells cannot be modified.

Postby ljbaney » Tue Mar 05, 2019 5:46 pm

File attached.
Cell protect is off.
Sheet protect is off.
Macro works when run as a macro.
Get this error when:
run macro as a function,
macro (replaces, deletes, clears) contents of cell.
Note:When macro DOES NOT (replace, delete, clear) contents of cell, it will run as a function.
Same error on seperate computers:
Windows XP, OOA 4.1.5
Windows 10, OOA portable 4.1.5


FORMULA
Code: Select all   Expand viewCollapse view
=IF($A$1=B3;(DELETE1());"")


MACRO
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
   Option Explicit
   
   sub Main
   end sub
   
   function delete1()
   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 = "$C$3"
   
   dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
   
   rem ----------------------------------------------------------------------
   dim args2(0) as new com.sun.star.beans.PropertyValue
   args2(0).Name = "StringName"
   args2(0).Value = "delete"
   
   dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args2())
   
   end function
Attachments
test.ods
(10.76 KiB) Downloaded 27 times
Last edited by robleyd on Wed Mar 06, 2019 12:55 am, edited 1 time in total.
Reason: Added Code tags
Linda Baney, Apache OpenOffice Portable 4.1.5, Windows 10
ljbaney
 
Posts: 5
Joined: Sat Feb 09, 2019 6:11 pm

Re: Protected cells cannot be modified.

Postby FJCC » Tue Mar 05, 2019 6:00 pm

A function cannot change the content of cells other than the cell that contains it. I do not know why you are getting that specific error but, in any case, you will not be able to use a function to clear the content of other cells.
If you explain more about your goal, someone may have a suggestion for accomplishing it with another method.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7261
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Protected cells cannot be modified.

Postby ljbaney » Tue Mar 05, 2019 7:00 pm

Thanks for your reply. The spreadsheet is used to calculate percentages of nutrients consumed on a daily basis. The user indicates number of servings of food in a cell range. The next day, I want to automatically clear entries in that cell range so the user can start over again.
Linda Baney, Apache OpenOffice Portable 4.1.5, Windows 10
ljbaney
 
Posts: 5
Joined: Sat Feb 09, 2019 6:11 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests