There is a typo: activbeSheet → activeSheet
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.
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
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!
After you get the Cell what you wanted to select,
cellActiveSheetE7 = activeSheet.getCellRangeByName(“E7”)
you can reference its string content:
button.TargetURL = cellActiveSheetE7.String
You can check it by usage the XrayTool (after you loaded it into the memory):
xray cellActiveSheetE7I’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.
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.
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.
@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.
“I don’t know a single thing about basic code.”
Then, why you try to use it???
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.
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 SubIn 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 SubThank 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?
With cell selected, Ctrl+1, Background.
Questions if you’ll please answer them, Schiavinatto: Why does it use 4+ subs? The one I put (hacked) together is all in one sub. What’s the difference? Why the difference? Is there a reason it’s not all in one one sub?
I’d love to learn this stuff. I don’t expect to learn it in a day. Getting to understand it bit by bit here and there appreciated.
The GoToLocal Command is a SubMacro, and normal in a macro looking for information or navigating between sheets several times, this SubMacro makes it easy.
Note that in the first macro the GoToLocal command is used 2x.
The Macro could look like this:
Sub GoToPlan
Dim oSel as Object : Dim Var1$
dim Loc(0) as new com.sun.star.beans.PropertyValue
Loc(0).Name = "ToPoint" : Loc(0).Value = "E7"
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame _
, ".uno:GoToCell", "", 0, Loc())
oSel = ThisComponent.getCurrentSelection()
Var1 = oSel.getString()
'msgBox Var1
dim Loc2(0) as new com.sun.star.beans.PropertyValue
Loc2(0).Name = "ToPoint" : Loc2(0).Value = Var1
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame _
, ".uno:GoToCell", "", 0, Loc2())
End Sub
The GoToLocal macro can be called from any other macro and serves to go from a single cell (A2) to the area (A2:D5); for Named Area and for Sheet ( Sheet1 ).