[Solved] Find Sheet name containing control

Creating a macro - Writing a Script - Using the API

[Solved] Find Sheet name containing control

Postby Enesin » Wed Jan 23, 2019 7:11 am

Is it possible to find the sheet name a basic control is on, given only the *name* of the control in a string variable?

A way to work backwards, so to speak?
Last edited by Enesin on Thu Jan 24, 2019 5:23 am, edited 1 time in total.
Apache Open Office 4.1.3 on Windows 7 (64bit)
There are no stupid questions. I mean, except for mine...
(&%#@ code! Don't do what I TELL you to do, do what I WANT!)
Enesin
 
Posts: 12
Joined: Tue Jan 15, 2019 10:02 pm

Re: Find Sheet name containing control

Postby FJCC » Wed Jan 23, 2019 7:25 am

There may be a better way than this. You can iterate over all of the ElementNames arrays on all the Forms on all the DrawPages. I put a button called Push Button 1 on Sheet1 and found that the array oElementNames in the code below contains "Push Button 1"

Code: Select all   Expand viewCollapse view
  oDrawPages = ThisComponent.getDrawPages()
  oObj1 = oDrawPages.getByIndex(0)
  oForms = oObj1.getForms()
 
  oObj2 = oForms.getByIndex(0)
  oElementNames = oObj2.getElementNames()
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: 7058
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find Sheet name containing control

Postby Enesin » Wed Jan 23, 2019 7:51 pm

FJCC: Thank you for your answer.

For the method I know to change values in list boxes, I need to know the sheet that they are on. I already know the name of the control.

I spent a day looking at your answer. It wasn't explained in a way I understand completely, but I'm guessing I iterate through all possible names with "for i = o to ubound(array)" or something? Looking backward, this appears to go up as far as draw pages. Is this the same as the sheet name?

I'm including my project as an attachment here. It's that cascading list box thing. *It works*, but I don't like the klunky method I used to do it (optionally expanding my data table).

I wondered if it was possible to find the sheet name so that, already knowing the name of the list box, I can reference it so that I can change its values. This is described in the remarks of the code, when i search for a control that's not on the calling (active) page.

I'd like to use your method, but I'd need it explained to me in more detail before I'd be able to.

Again, thanks.
Attachments
ListBoxCascade5.ods
(24.73 KiB) Downloaded 13 times
Apache Open Office 4.1.3 on Windows 7 (64bit)
There are no stupid questions. I mean, except for mine...
(&%#@ code! Don't do what I TELL you to do, do what I WANT!)
Enesin
 
Posts: 12
Joined: Tue Jan 15, 2019 10:02 pm

Re: Find Sheet name containing control

Postby FJCC » Wed Jan 23, 2019 9:55 pm

I didn't think through this in detail but start with this
Code: Select all   Expand viewCollapse view
Sub testMyFunc
SheetName = GetSheet("lbBeliefs")
print SheetName
end sub


Function GetSheet(Name)
oSheets = ThisComponent.Sheets
Cnt = oSheets.Count
for i = 0 to Cnt -1
   oSheet = oSheets.getByIndex(i)
   oDP = oSheet.getDrawPage()
   oForms = oDP.getForms()
   if oForms.Count > 0 Then
   for j = 0 to oForms.Count - 1
      oForm = oForms.getByIndex(j)
      ElemNames = oForm.ElementNames
      If Not IsEmpty(ElemNames) then
         for k = 0 to UBOUND(ElemNames)
            if ElemNames(k) = Name Then
               GetSheet = oSheet.Name
               exit Function
            End If
         next k
      end If
   next j
   end if
next i
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: 7058
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find Sheet name containing control

Postby Lupp » Wed Jan 23, 2019 10:33 pm

I didn't understand the code by 'Enesin' immediately - and I didn't want to spend the time to study it in detail.

The original question, however, was simple and clear:
Enesin wrote:Is it possible to find the sheet name a basic control is on, given only the *name* of the control in a string variable?
I asume the OriginalQuestioner is aware of the fact that the name given a control in the first line of the control editor in't the actual identifier of the object. In fact you can use the same name for many form controls even in the same sheet.

Since I had made an example anyway befor I noticed the post by FJCC, I also post the code now and attach the example. Anyone may study it - or not.
Code: Select all   Expand viewCollapse view
Global gControlsFound

Sub put
doc0 = ThisComponent
outSheet = doc0.Sheets(0)
outRange = outSheet.GetCellRangeByPosition(6, 20, 8, 20 + Ubound(gControlsFound) -1)
outRange.setDataArray(gControlsFound)
End Sub

Sub executeAction(pEvent)
doc0 = ThisComponent
parSheet = doc0.Sheets(0)
parCell = parSheet.GetCellByPosition(2,0)
nameToFind = parCell.String
Dim found(0 To 0, 1 To 3)
u = Ubound(found)
i = 0
For Each sheet In ThisComponent.Sheets
i = i + 1
dp = sheet.DrawPage
  For Each form In dp.Forms
   For Each control In form
    If control.Name=nameToFind Then
     u = u+1
     Redim Preserve found(1 To u, 1 To 3)
     found(u, 1) = sheet.Name : found(u, 2) = i : found(u, 3) = control.ServiceName
    End If
   Next control
  Next form
Next sheet
gControlsFound = found
REM Only for the demo:
put
End Sub
Attachments
aoo96719FindNamedFormControlsInSheets_1.ods
(14.78 KiB) Downloaded 11 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2342
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Find Sheet name containing control

Postby Villeroy » Thu Jan 24, 2019 12:36 am

Code: Select all   Expand viewCollapse view
Sub SomeFormEvent(ev)
obj = ev.Source.getModel()
do until obj.supportsService("com.sun.star.sheet.SpreadsheetDocument")
  obj = obj.getParent()
loop
sh = obj.CurrentController.getActiveSheet()
print sh.getName()
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Find Sheet name containing control

Postby Enesin » Thu Jan 24, 2019 5:19 am

This is solved. :D

Thank you Villeroy. I started with your example because it was so short(always a win). It always returns the sheet that the triggered control is on. Then I realized that my question was poorly worded, and for that I apologize. I need to be more specific in the way I ask questions.

So I went back up and looked at FJCC's response, and saw the word "lbBeliefs". I immediately realized that we were on the same page. Or... Sheet, I guess. I coded that up and it works like a champ. Thank you for your help.

So I'm upping the latest cascading listbox demo. It's better because it gets the macro to do more of the heavy lifting on the back end, instead of having the user F with more table columns. It also has a test button for FJCC's function, and has everything dim'd, and even has some minor error reporting.

Thank you Lupp. I downloaded your example sheet and am going to check it out. I already had one working solution, but I intend to dig into yours and see how it's written. I always learn so much, not just from answers, but from seeing how people arrive at them as well.

If there's anything I could to to finalize this cascading listbox scheme, it would be to pull the value of the "linked cell" from the control into the macro. I have a feeling that's not possible, so this still uses the "Additional information" field to pass a value to the macro. Still, it would be nice to not use fields for "other than intended" if I can help it. ;)
Attachments
ListBoxCascade6.ods
(27.35 KiB) Downloaded 10 times
Apache Open Office 4.1.3 on Windows 7 (64bit)
There are no stupid questions. I mean, except for mine...
(&%#@ code! Don't do what I TELL you to do, do what I WANT!)
Enesin
 
Posts: 12
Joined: Tue Jan 15, 2019 10:02 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests