calc: macro help please

English
,
#1

How do I get this to update the button with the URL from cell E7?

Sub update_target_url()
 doc = ThisComponent
    selection =  doc.CurrentController.selection 
    form = doc.CurrentController.ActiveSheet.DrawPage.Forms(0)
    button = form.getByName("cmd_link")
    button.Label = "Go"
    button.TargetURL = selection.String
End Sub

I don’t want it using random data from whatever cell I have selected I want it always using the data from a specific cell, the cell I have the VLOOKUP function in. In the example I’m trying to get this working in VLOOKUP is in E7.
How do I tell the script to get the URL from E7?

test2.ods

#2

Code:

activeSheet = doc.CurrentController.ActiveSheet
cellActiveSheetE7 = activeSheet.getCellRangeByName("E7")
nextTargetURL = cellActiveSheetE7.String

Regarding the “I don’t want …data from whatever cell I have selected…” from the question, I would assume you also wouldn’t want to take the data from the currently selected sheet, but from a specific sheet identified by its name, say targetURLsSheet.

If so use (in place of activeSheet)

sheet = doc.Sheets.getByName("targetURLsSheet")

[Edit] Typo as mentioned in the comment by @Zizi64 was rectified.[/Edit]

#3

Thanks! Do I replace something in the current code with those lines? If they’re to be added to it does it matter where I add them? activeSheet is fine.

#4

Getting an error with this line whether I attempt adding this code, replacing some of the other code, or using it as it’s own macro.
cellActiveSheetE7 = activbeSheet.getCellRangeByName(“E7”)

Basic Runtime Error
Object Variable not set.

I’m 100% clueless when it comes to macro coding. It baffles me. I can’t make any sense of it.

#5

There is a typo: activbeSheet → activeSheet

#6

LOL, simple things. So I made the code:

    Sub update_target_url()
 doc = ThisComponent
 	activeSheet = doc.CurrentController.ActiveSheet
	cellActiveSheetE7 = activeSheet.getCellRangeByName("E7")
	nextTargetURL = cellActiveSheetE7.String
    selection =  doc.CurrentController.selection 
    form = doc.CurrentController.ActiveSheet.DrawPage.Forms(0)
    button = form.getByName("cmd_link")
    button.Label = "Go"
    button.TargetURL = selection.String
End Sub

It no longer errors but it still doesn’t update the button’s URL unless I have cell E7 selected. I want this to update the button URL regardless of what cell I currently have selected. I have no clue at all what I’m doing with macro coding.

#7

You must put the string of the selected Cell object into the property “button.TargetURL”, but not the string of the Selection object.

Download and install one of third party object inspection tools: XrayTool or MRI. Then you will able to list and examine the existing properties of the programming objects:

https://berma.pagesperso-orange.fr/index2.html

https://extensions.libreoffice.org/en/extensions/show/mri-uno-object-inspection-tool

#8

Thanks. I did get this working as I want it to. I recorded a macro of selecting the cell I wanted it to select, copied the code from that macro and added it to the existing macro. I downloaded the extensions and will check them out. Maybe they’ll help me finally make sense of Basic scripting. Thanks, Zizi64!

#9

After you get the Cell what you wanted to select,

cellActiveSheetE7 = activeSheet.getCellRangeByName(“E7”)

you can reference its string content:

button.TargetURL = cellActiveSheetE7.String

#10

You can check it by usage the XrayTool (after you loaded it into the memory):

xray cellActiveSheetE7
#11

I’m sure Lupp’s script works. I couldn’t get it to work so I wasn’t able to use it. I recorded a macro that selected the cell I wanted it to select and that worked.

#12

You not need to select a Cell graphically, when you want to get its value ot its string in a StarBasic+API macro.

It is enough to get it as a programming object.

The graphical actions always will be slower.

#13

Recording macros and doing things graphically is an option for people like me who aren’t able to translate macro code so it makes sense to them. I’m completely lost when it comes to that stuff. I couldn’t get Lupp’s code to do anything because I have no clue what I’m doing with basic code. I got lucky getting this to work the way I did.

#14

@Zizi64 your comment

After you get the Cell what you wanted to select,
cellActiveSheetE7 = activeSheet.getCellRangeByName("E7")
you can reference its string content:
button.TargetURL = cellActiveSheetE7.String

Where would I put that? Does it go in place of something in the original code? What does it replace? Do I just add it? Does it matter where I add it? I’m aware it has to go between sub and end sub. Does it have to be placed in the code somewhere specific? I don’t know a single thing about basic code.

#15

“I don’t know a single thing about basic code.”

Then, why you try to use it???

#16

Because I can.

"Recording macros and doing things graphically is an option for people like me who aren’t able to translate macro code so it makes sense to them. "

Can’t do everything I want my sheets doing with formula’s. We have to use macros for some things.

#17

In the event someone else is looking for how to do this a fellow ask.Libre member was kind enough to write the following macro for me in reply to this question:

Sub update_target_url()
    doc = ThisComponent
    form = doc.CurrentController.ActiveSheet.DrawPage.Forms(0)
    button = form.getByName("cmd_link")
    button.Label = "LibreOffice Ask"
    button.TargetURL = "https://ask.libreoffice.org"
End Sub

I edited it to this:

Sub update_target_url()
 doc = ThisComponent
    selection =  doc.CurrentController.selection 
    form = doc.CurrentController.ActiveSheet.DrawPage.Forms(0)
    button = form.getByName("cmd_link")
    button.Label = "Go"
    button.TargetURL = selection.String
End Sub

Then I recorded a macro of selecting the cell I wanted it to select which in this case was E7 and got this code from the recorded macro I saved:

    REM  *****  BASIC  *****
    
    Sub Main
    
    End Sub
    
    
    sub selectE7
    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 = "$E$7"
    
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
    
    
    end sub

I then took the snippet of code that selected the cell from the above generated macro I recorded:

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 Select vLookUp Cell
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$E$7"

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

And added it to the update_target_url macro.

Here is the end result and macro code:

Sub update_target_url()
    rem define variables
    dim document   as object
    dim dispatcher as object
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    dim args1(0) as new com.sun.star.beans.PropertyValue
    args1(0).Name = "ToPoint"
    args1(0).Value = "$E$7"
    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
     doc = ThisComponent
        activeSheet = doc.CurrentController.ActiveSheet
        cellActiveSheetE7 = activeSheet.getCellRangeByName("E7")
        nextTargetURL = cellActiveSheetE7.String
        selection =  doc.CurrentController.selection 
        form = doc.CurrentController.ActiveSheet.DrawPage.Forms(0)
        button = form.getByName("cmd_link")
        button.Label = "Go"
        button.TargetURL = selection.String
    End Sub
#18

In E7 use Validation, and the macros below: test file

Sub GoToPlan
Dim oSel as Object : Dim Var1$
	GoToLocal "E7"
oSel = ThisComponent.getCurrentSelection()
Var1 = oSel.getString()
'msgBox Var1
	GoToLocal Var1
End Sub

Sub GoToLocal ( xLocal$ )
dim Loc(0) as new com.sun.star.beans.PropertyValue
Loc(0).Name = "ToPoint" : Loc(0).Value = xLocal
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame _
, ".uno:GoToCell", "", 0, Loc())
End Sub
#19

Thank you! I got it working and doing what I wanted it to. I will give this a try though. Thank you very much!
Edit: I checked it out. I might use this for another instance on the same sheet so I’m not using the same macro twice. I’d prefer not having the bright yellow cell background. A person that’s going to be using what this is for has sensitive eyes that bright yellow color will irritate. It still makes the drop-down list bright yellow if I change the data cells. How do I get rid of the background color?

#20

With cell selected, Ctrl+1, Background.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.