Hi
I am trying to create a spreadsheet that is a list of various items, but would like the file to autoopen with the formula find dialog box so that I can search for the item I am looking for. I have done some experience with via/macros in the distant past and forget how to do this. I want this to be a libre document. Here’s a pic on an excel spreadsheet that opens automatically when the spreadsheet is opened. I appreciate your patience. Detailed instructions would be awesome. I must admit, the tags got me confused as well :((
Kindly, please clarify which type of search you are talking about. The fact is that in Calc you can use two types of search: by Ctrl+F the panel below sheet will open, and by Ctrl+H a form with a rich selection of additional settings will open, a bit like what you showed on your screenshot.
Thanks for the RSVP. If I click on calc spreadsheet, it automatically comes up with a find box in the bottom left automatically and control H brings up a Delete Comments dialog box. I apologize , but didn’t say I was on Mac using big sur. The bottom left will suffice, but can I get Libre to highlight each selection it finds with a fill-in color or a different outline other than black. Thanks so much
As an aside, is there a place on the email I receive to directly respond and sign in?
I’m not sure if my instructions will be detailed enough, but I will try to explain the sequence of actions.
First, choose Tools - Options - LibreOffice - Advanced and make sure the Enable macro recording option is enabled.
Make sure your current spreadsheet is saved. For one of the subsequent actions, it is required that the spreadsheet has a name, and it will appear exactly when the document is saved to disk.
Now choose Tools - Macros - Record Macro and do some simple action. Any, whatever. For example, click any cell that is currently inactive, or press the right arrow key or something else. And immediately stop recording the macro (just click the button on this panel)
In the window that will open after that, select a location to save your simple macro (for example, create a new template in your current workbook) and specify with what name to write the macro code (for example, AutoFind - why not?)
Now go to editing this macro - press Alt+ F11, find the AutoFind macro and click the Edit button.
You will see a code like to this:
sub AutoFind
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 = "$G$20"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
end sub
In fact, this is not a real macro, this is just a script for the Dispatcher, just a record of a sequence of some keystrokes. A real macro looks a little different and is a little harder to write. But this is quite enough for your task, so I deliberately simplify the solution.
Feel free to remove all unnecessary, you only need lines describing the first two variables Dim, lines with the creation of objects for these two variables and a line with a call to the dispatcher.executeDispatch method. The two lines with the word Sub leave alone too.
Now let’s figure out which command to instruct the dispatcher to execute. Let’s say you want to open the Find And Replace form (Ctrl+H). To find out how the corresponding UNO command is written, choose Tools - Customize - Keyboard tab, find this command and move the cursor over it - the tooltip will show the required string.
This means that the command to the Dispatcher will look like this:
dispatcher.executeDispatch(document, ".uno:SearchDialog", "", 0, array())
Let’s go back to the Customize window and on the Events tab indicate that for the Open Document event, you need to execute the AutoFind macro.
Save and close your spreadsheet, open it again. If everything was done correctly, then the search form is already open.
If not, make sure to save the workbook in ODF format (.ods) and not in Excel, and that the macro security level allows the macro to run.
To find out what command Calc executes when pressing the CTRL+F keys, I had to find the file \share\config\soffice.cfg\modules\BasicIDE\menubar\menubar.xml in the office installation directory and find the line
<menu:menuitem menu:id=“vnd.sun.star.findbar:FocusToFindbar”/>
Until today, I, like you, had no idea about the existence of this team. But now I was able to record the macro:
sub AutoFind
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
Rem Open Edit - Find & Replace form (Ctrl+H)
Rem https://help.libreoffice.org/7.1/en-US/text/shared/01/02100000.html
dispatcher.executeDispatch(document, ".uno:SearchDialog", "", 0, array())
Rem Activate Find Panel (Ctrl+F)
dispatcher.executeDispatch(document, "vnd.sun.star.findbar:FocusToFindbar", "", 0, array())
end sub
This macro will open both search forms when opening a document.
In fact, it is programmed in a slightly different way, but such a simplified solution is enough to solve your problem.
PS. Now I know that you are using a Mac and some of the commands that I described may be in other places on the menu and the images may differ. I hope this will not be a big obstacle for you.
Sorry,but beyond my pay grade :((((…sorry you did so much work
Is there a way to just use the Find in the bottom left but have it highlight the cell/s it finds with filling the cell with a color or bracketing the cell with another color other than black…in preferences?
Now I see that you were not asking about automatically opening the search box, but about marking the results with color. It’s a shame that you could not immediately state your request. Find will not solve this problem, but Find and Replace will allow you to do it in just a few clicks. What does the last icon look like in the panel at the bottom left? Is it a magnifying glass with a Find and Replace pop-up?