[Solved] Find Sheet name containing control

[Enesin]

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?

[FJCC]

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

  oDrawPages = ThisComponent.getDrawPages()
  oObj1 = oDrawPages.getByIndex(0)
  oForms = oObj1.getForms()
  
  oObj2 = oForms.getByIndex(0)
  oElementNames = oObj2.getElementNames()

[Enesin]

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.

[FJCC]

I didn't think through this in detail but start with this

Code: Select all

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

[Lupp]

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:

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

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

[Villeroy]

Code: Select all

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

[Enesin]

This is solved.

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.