[Solved] Pass literal argument to macro from list box event

[Enesin]

I am calling a macro from a listbox control.

While calling the macro, I'd like to pass a parameter. I would like to pass this value literally from the call.

This means that instead of calling a macro by choosing "MyMacro" from a list, I would call it with “MyMacro(“Sheet2.F1”)”, passing “Sheet2.F1” as a string into the macro.

How is this done? Nowhere that I see in the listbox properties lets me write the name of the macro – I can only select a macro from a list.

I usually look around the internet, find something that asks the same question, then spend a day figuring out what people are typing.

This time I can't even find any examples to stare at. This means it's either incredibly simple or impossible. I hope it's simple.

[Zizi64]

While calling the macro, I'd like to pass a parameter. I would like to pass this value literally from the call.

This means that instead of calling a macro by choosing "MyMacro" from a list, I would call it with “MyMacro(“Sheet2.F1”)”, passing “Sheet2.F1” as a string into the macro.

The “Sheet2.F1” is the choosen textual value of the Listbox? What is the source of the passed parameter?
You can
- get the object of the event (Where the macro routine was called from: the Textbox) in the assigned macro routine. And then you can get the actually choosen text from the Textbox
- type-in the text in an inputbox (launched from the macro)
- get the texr from a specific cell
by some API functions.

How is this done? Nowhere that I see in the listbox properties lets me write the name of the macro – I can only select a macro from a list.

Is it a "ListBox" form control element really, or a cell with "Data validity" function?
The form control elements have three TAB for the settings: General, Data, Events.
Right click on the form control element, choose the Control item. Click on the Events TAB, Just choose an event and assign your macro to it. The macro code must be located in the MyMacros, or in the actual document.


Please upload an ODF type sample file - embedded your macro code, the listbox (with its items), and please assign the macro to the Listbox.

[Enesin]

ListBoxCascade.ods

Demo ODS showing attempt.

(19.57 KiB) Downloaded 398 times

I don't wish to pass a variable. I want to pass a literal value as if it's a variable during the call.

Unless there's a better way...

I want different listboxes to each use the same macro getting different results.

***Ultimately, the macro needs to know which listbox called it.***

My deal is that I'm tackling that old chestnut, “Have a listbox change subsequent listboxes”. So many people ask about this kind of thing that it should be a built in feature.

But of everything that I've looked up and read, I understand none of it. I've seen no code examples.

So I cooked up my own scheme. It works, but only for a single list box so far.

That's because the address of the table is hard-coded into the macro (“Sheet2.F1”).

If I can have any listbox (standard listbox control so far) pass a value of *its particular table to use* to the macro when called, I can have it work like I want.

I've cobbled a demo of what I'm doing. Best to say I'm using this more simply than in the demo, but I need it to be more... portable and reusable.

An explanation of the demo sheet:

• List box “lbKeyChoice” calls a macro called “ListBoxKey”. This currently seeks a table (orange on Sheet2) which uses a scheme to tell the macro which ranges to put into what other list boxes.

• It's flexible to a point (list boxes must not skip beginning entries in the lists). The list box “lists”(yellow on Sheet2) can be anywhere, but are organized here for clarity.

• The orange table lists ranges according to the (current) two choices of the key list box. This can be simply extended with more data downward to change more sub-list boxes.

• The number of key list box choices can be extended to the right more columns if wanted. Just increase the choice of the key listbox to add “Average” and make a new column of ranges.

• Then make new lists for the sub-listboxes off to the right of the yellow lists already there. Or just use a smaller part of the existing lists.

• The left column in the orange table lists the names of sub-listboxes to change.

• The choice columns are just ranges for their lists. If a range is preceded by a number, that number become the default choice of the sub listbox. If there is no number, the default is the first choice.

• Examples also show that sub-listboxes can use the same lists, just more or less of it according to the choice. “Timepiece”, “Social” and “Recreation” are examples of this.

• Ignore the list stuff in magenta. That's just to give the demo something to do. It's really all about having the macro know which listbox called it, so it knows which range table (orange) to use.

I don't see a way to save as ODF. Do you mean ODS? I hope so. That's what I'm sending.

And no, I'm not really making lists to compute social status based on wealth. That was done for entertainment value. Programmers need to laugh too.

Finally, thank you so much for answering me. I'm trying hard to learn this programming environment, and have taken a week just to get this far.

[RoryOF]

I am not following the detail of this thread, but in case it may be of assistance, I draw attention to the existence of a number of prewritten macros in OpenOffice, including some dealing with listboxes.

These are at \Tools \Macros \Organize Macros \ OpenOffice Basic : scroll down to Tools in the left pane that opens and click on the down arrow. You will see Listbox, click on this and the included macros will show in the pane to the right. Select a macro and choose the Edit button to see its content.

Tools is under Preferences on a Mac.

ODF is the shorthand for the native formats of OpenOffice - .,odt .ods etc.

[Enesin]

Thank you Rory. I didn't know the tools were here, but have seen them referenced on the net. Funny that they've been under my nose the whole time. I would have learned things more quickly from them.

I think I can simplify my question:

I have a macro that when run from a listbox, can change the values of many other listboxes. It works. It's extensible and flexible. It may also be a weird solution, but at least I understand it. I don't understand other schemes I've seen.

Now I want to reuse it. So that if I have the listbox "lbTimepiece" call it, it knows to look for a new table at say, "Sheet2.F15" or so.

How to get this range information passed to the macro according to what listbox called the macro?

[JeJe]

you do something like this. oEvt is a variable of your choice of name for the event struct that the sub receives.

Code: Select all

Sub ListBoxKey(oEvt) 

select case oEvt.source.model.name

case "lbKeyChoice"
'do something

case "lbClothing"
'do something different

end select

[Zizi64]

I don't see a way to save as ODF. Do you mean ODS? I hope so. That's what I'm sending.

ODF = the abbreviation of the name "Open Document Format" (ISO/IEC 26300)
https://en.wikipedia.org/wiki/OpenDocument

File extensions for the ODF fileformats:

.ods, .ots = file extensions of the Calc spreadsheet documents and templates
.odt, .ott = file extensions of the Writer text documents and templates
.odg, .otg = file extensions of the Draw graphical documents and templates
.odp, .otp = file extensions of the Impress presentation documents and templates
.odf = file extensions of the Formula editor formula documents

[Enesin]

JeJe, thank you. This is a great method for me to know about now.

It works, and I've updated the demo ODS to use it, but I do still run into a problem:

Each time I use the macro to cascade list boxes, I have to go in and hard code the addresses for the new range table.

As in:

Code: Select all

ListBoxKey(oEvt)
select case oEvt.source.model.name
case "lbKeyChoice1"
    CallParam = "Sheet2.F1"
case "lbKeyChoice2"
    CallParam = "Sheet2.F15"
End select

So I'm still curious if it's possible to pass those kinds of addresses to a macro when a listbox calls it?

[mikele]

Hi,
for those kind of listboxes there is no need of macros. You can solve it with data validity.
In sheet3 there is an example.
I named some ranges: the red (A2:A3) is named "wealth", the grey (C1:N1) "stuff", the yellow (C2:N8) "Rich", the green (C10:N16) "Poor". The last two have to be exactly the value of A2 and A3!
With this preparation the following formulas are easier. Take a look at the data validation in B19 and B21:B29.
For example B21:
Allow: Cell range
Source:

Code: Select all

IF(stuff=A21;INDIRECT($B$19))

[JeJe]

"So I'm still curious if it's possible to pass those kinds of addresses to a macro when a listbox calls it?"

The only valid parameter is the event struct. You can do things like use the ListBox Properties additional information/Tag. Or you can name the listbox to include some information eg ListboxF15 and extract that in the sub.

[Villeroy]

List boxes belong to database forms. Calc is not a database. In Calc you can do a lot more with validation by cell range or list.
Anyway, an MRI session reveals something about a list box bound to a cell target and range source:

Code: Select all

Sub Snippet(Optional oInitialTarget As Object)
  Dim oSource As Variant
  Dim oModel As Variant
  Dim oListEntrySource As Variant
  Dim oStringItemList As Variant
  Dim nListSourceType As Long
  Dim oTypedItemList As Variant
  Dim sItem As String
  Dim sSelectedItem As String
  Dim nSelectedItemPos As Integer
  Dim oItemData As Variant
  Dim sItemText As String
  Dim oValueBinding As Variant
  Dim aBoundCell As New com.sun.star.table.CellAddress
  Dim nSheet As Integer
  Dim nRow As Long
  Dim nColumn As Long

  oSource = oInitialTarget.Source
  oModel = oSource.getModel()
  oListEntrySource = oModel.getListEntrySource()
  
  oStringItemList = oModel.StringItemList
  nListSourceType = oModel.ListSourceType
  oTypedItemList = oModel.TypedItemList
  
  sItem = oSource.getItem(0)
  sSelectedItem = oSource.getSelectedItem()
  nSelectedItemPos = oSource.getSelectedItemPos()
  
  oItemData = oModel.getItemData(0)
  sItemText = oModel.getItemText(0)
  oValueBinding = oModel.getValueBinding()
  
  aBoundCell = oValueBinding.BoundCell
  nSheet = aBoundCell.Sheet
  nRow = aBoundCell.Row
  
  nColumn = aBoundCell.Column
End Sub

[Enesin]

Thank you JeJe! Holy cow, that's so brilliant it's evil! I have done this and tested and it's slick.

I simply named the listboxes "lbSheet2.F1" and so on (the "lb" is for a minor attempt at order in the chaos) and then extract the name into a working string.

Code: Select all

ListBoxKey(oEvt)

CallParam = oEvt.source.model.name    'Rip it
CallParam = Right(CallParam, Len(CallParam) - 2)    'Strip it
...

And it works. It does what I want, looks how I want, and makes me feel all warm and fuzzy inside.

Mikele: Thank you for your method. I love it, but the long gaps of blank in the lists makes it possible for people to not notice all choices. Also, when i enable the mighty "Design mode (on/off)" button, it still won't let me touch those controls to see what's under the hood. It's some kind of voodoo, and makes me feel like I'm a chimp banging rocks together. It's beyond me. I DO like the use of "indirect". I am thankful to learn that.

Villeroy: Thank you for the information. I apologize that I'm not using Calc as most people should. Now I'm glad I didn't tell anyone what I'm really using it for, or people would be chasing me with torches and pitchforks. Thank you also for the MRI information. I'll look at that tomorrow... I mean later today, when I wake up, and hopefully learn from it. I have XRay, and don't understand how to use it, but it's cool to know it's there in case I ever do.

This is solved. I'm including the demo ODF one last time for anyone who feels this weird method of cascading listboxes will be of any use. For the way I'm actually using them, they're working just great now.

[Villeroy]

[Tutorial] Introduction into object inspection with MRI

[Zizi64]

Code: Select all

ListBoxKey(oEvt)

CallParam = oEvt.source.model.name    'Rip it
CallParam = Right(CallParam, Len(CallParam) - 2)    'Strip it
...

The ListBoxes have a lot of various writable/readable properties. You can examine them by an Object inspection tool like the MRI (as Villeroy suggested it) or by the XrayTool.
And you can use these properties for passing textual parameters - without stripping it from the name property.

Here are some of them...

Help text:

Code: Select all

CallParam = oEvt.source.model.HelpText

Help URL:

Code: Select all

CallParam = oEvt.source.model.HelpURL

"Additional information":

Code: Select all

CallParam = oEvt.source.model.Tag

[Villeroy]

Code: Select all

addr = e.Source.Model.ValueBinding.BoundCell
cell = getCellByAddress(ThisComponent, addr)

Code: Select all

'pass a spreadsheet-document, sheet or range together with a c.s.s.table.CellAddress
'return empty if oAddr out of bounds or wrong obj
Function getCellByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getCellByAddress = oSheet.getCellByPosition(oAddr.Column,oAddr.Row)
exit function
nullErr:
	getCellByAddress = Null
End Function

[luciaserra]

Perfect, good info, I need something like this to start a great macro, thanks

[Enesin]

Zizi64 (or may I call you Tibor? )

Thank you so much for this, and all other information. This is the kind of "using things for other than intended" trick I was looking for, and best of all: answers my thread question exactly.

Between the info from JeJe and yourself, I now have a method I can use (other than intended, of course) for any standard control.

It helps simplify my method, and cuts down on potential confusion. Now my key listboxes are named "lbListKey1" and "lbListKey2". I have put the range address values into the "Help Text" field. This now makes the use of my macro so easy to twiddle on the fly; I can change a range table location without changing listbox names, which makes extensibility (having sub-listboxes in a tree effect their own sub-listboxes) easier to keep track of. This thread now gets a personal rating of "Solved+".

Villeroy: Thank you again. I must admit that I don't yet understand how your code example is to be used, but I'm going to knock it up in a macro so I can poke and prod it to figure it out. Your example jumps ahead of me, but I will get there. I will also use my Sunday to learn about MRI inspectors. I appreciate it much.

[JeJe]

Just making sure you know the Help Text is the text that appears in the popup when you pause the mouse over the control.
Its the Additional Information (in the control Properties) which is the same as the .model.tag (in code) that's really meant for storing extra information for the control. The name trick is useful if you want to keep the tag for something else.

What I often do is use this EndVal function to turn the control name into an index number.

Select case EndVal(OEvt.source.model.name) '"ListBox1", "ListBox2" etc
case 1 'ListBox1

case 2 'ListBox2

end select

Code: Select all

Function EndVal(st As String) As Long
	Dim i As Long, a As Integer, s As String, res As String
	For i = Len(st) To 1 Step -1
		s = Mid(st, i, 1)
		a = Asc(s)
		If a >= 48 AND a <= 57 Then
			res = s & res
		Else
			Exit For
		End If
	Next
	EndVal = Val(res)
End Function

[Enesin]

Villeroy: *edit* I thought I understood your example, but I was wrong.

But that's okay. I'll figure out your code example in time. I appreciate your help. */edit*

I want to use your listed method, especially for all the error checking you included. It also looks like I'd be able to tidy up my code this way. This looks like a solid method to learn. Thanks again for the example.

[Enesin]

JeJe: You're right, I don't want floating tooltips over my listboxes that say "Sheet2.F15". I just checked and feel silly. Okay, I'll use another field. I should have used "Tag" the first time, but didn't realize that meant the "Additional Information" field.

I still don't want to use a case list, because I want to be able to add more cascades to the scheme and call them from anywhere without adding to a list in the code.

Your function is cool. I'm going to start using that.

But again, I'm putting the destination cell address in the Tag field for now until I can get Villeroy's method working for me.