Using a list box to output multiple selections

[drclong]

Hi there and thanks for the help in advance.

I am trying to use a list box, but be able to select multiple items from the list and output to the adjacent column (seperated by a ,) but m having absolutely no luck. I could not find anything relevant on this site (maybe I was not searching the right terms). I did find a solution in excel but that hasn't helped me for calc as I can not open the sheet with the macro enabled. Help with that would be helpful too.

I am hoping someone has done this before or is able to give me step by step instructions for a solution.

Thanks again for the help.

Edit:
Something that might help. Below is code from an Excel sheet that does exactly what I want but I dont know how to convert it. I have used a conversion sheet on the web and attached the code below the VB code. If someone can make the OO code work that would be awesome.

VB Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub
OO Code:

Private Sub Worksheet_Change(ByVal Target As Dim oSheet as Object)
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.getCellRangeByName($1))
Dim rngDV As Dim oSheet as Object
oSheet = ThisComponent.CurrentController.ActiveSheet
oSheet.getCellRangeByName($1)
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

[acknak]

[Moved to Macros and UNO API]

[FJCC]

The code you attached doesn't make sense to me, though I'm not an expert. Could you explain more about what your overall goal is? A List Box may not be the best approach.

[drclong]

The code you attached doesn't make sense to me, though I'm not an expert. Could you explain more about what your overall goal is? A List Box may not be the best approach.

Hi there and thanks for responding.

What I want to do is select from a list and have that selection inserted into the same cell. Then make another selection that is added to the first selection separated by a comma. The VB code above does that exactly. The other option is to select from the list and insert that selection to another cell. Make another selection that is added to the first and so on. It works fine in Excel but not in Calc.

Hope that helps clarify

[FJCC]

I hope this file gets you started toward what you need. After you select the Start button, you can select from a drop down list in cell D2 and the series of selections appears in C2. I'm sure it could be done more elegantly and it has no attempts at error handling. I put in some comments but you'll need to study macros and Basic programing. There are good documents at OpenOffice.org and Andrew Pitonyak's macro web page. There are also examples to study here and at OOoforum.org

[drclong]

Thanks for that. It is along the lines of what I need but I need it to be active on the whole sheet so that any drop down list will perform this action automatically (without pressing start) and so that it will place the text in the cell that the drop list box is located.

[drclong]

Hi Again,

I have just realised that I posted the oo code twice. So I have edited the original post to show the VB code. Maybe someone who is capable with Excel can point me in the direction or replicating the VB code to work in Calc as it is exactly the way I want it to work.

Thanks again everyone.

[FJCC]

Here is a new version that does mostly what you want. The macro starts when the document opens. I left in the ability to stop the macro by selecting "Done" in any of the drop down lists. It was annoying to have no way to stop the macro cleanly when I was working on the code. I marked up the macro showing what lines to remove if you want to get rid of that. One thing to watch out for as you edit the code to fit your needs. Once you add a listener to a cell by using the method Cell1.addModifyListener(oListener) you can't reassign the variable Cell1 to another cell in the spreadsheet. If you do, the code won't work right. That means you can't use a loop to assign a listener to a series of cells. At least, that seemed to be the case as I worked on this.
I also have a output box saying "Ready" when the document opens. That's because it opened in a strange state where I couldn't access the drop down lists without clicking on a tool bar first. I don't know why that is and I won't have time to look at it once the workweek starts.

[drclong]

Thanks so much for that. I have modified it to work on multiple cells and lists in my application.
A couple of questions.

1. Is there some way to get the macro work on any selected cell (or cell with validated Data in my application) rather than specifying the actual cell? This would mean you could have one snippet of code that could be applied to the cell I am using at the time.

2. Is there some way to do the same with any sheet so that the selected sheet is the one that the code is applied to. In Excel you just put the code in the sheet and away you go.

Thanks again for the help, it has made a huge difference.