Multi list problems - Newbie

[Darren Guess]

Hi

I am a new user to form controls and VBA and I know most of you will be thinking I could search for the answer, but after around 4 hours of reading posts I am still a little confused and can't find what I'm looking for, as they all go a little more complex

All I need is a List Box that a user can select multiple options. once selected these enter in to another cell (conmma seperated, or carriage returned). Cell "J17"

Previously I had a free text box that individuals could fill out with any text, but my Director now wants some 'sanitised data entries' only to be selectable.

I do not want to make it too complex or flash, so was against using a User Form (unless necessary), I would just like to embed a list box and size it to the same as a cell, which would scroll as required. Also could the list box be placed over the cell I want the data to end up in "J17"?

I would then like to hard code the list as below, so users could not add or change the selections.

Spares Shortages - MAEL to supply
Spares Shortages - Customer to supply
Awaiting repair scheme from Airbus
Awaiting repair scheme from Boeing
Awaiting final repair approval - Airbus
Awaiting final repair approval - Boeing
Positive planned progress being achieved
Unplanned issues being encountered

I'm sure this will be fairly simple for the advanced users out there, so I appreciate the help and the steep learning curve I am on

Best Regards
Darren

 

[kevinb11]

What about a validation list?

 

[Darren Guess]

Hi

Thanks for the quick reply, if this will allow my users to select multiple items from a list and then the data is auto populated in to another cell, I would be happy with this solution.

Without reading another rake of posts, could you explain to me how this is set up? It would be much appreciated.

Best regards
Darren

 

[repairman615]

What about a validation list?

I too thought that. However, the needed multi-select is missing.

I am thinking using an ActiveX listbox.
with some VBA to populate, show/hide when a target cell is triggered and to place its value into J17 in the appropriate fashon.


I do not have any examples as of yet...

 

[Daniellel]

-- removed inline image ---

 

[Daniellel]

Hi, Cell Validation would be the best way to go I think, try this really usefull link on how it all works...

http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010072600.aspx

 

[Darren Guess]

Hi Daniellel

That is a very good link & I will definately use this for future requirements, but I cannot see that it allows multiple selections from a 'Drop down list'?

I need a multi selection requirement as we may have more than one of the conditions on the list at the same time.

This is how I ended up thinking I would need something like a list box?

Is it more complex than I may have first thought?

 

[Daniellel]

Hi Daniellel

That is a very good link & I will definately use this for future requirements, but I cannot see that it allows multiple selections from a 'Drop down list'?

I need a multi selection requirement as we may have more than one of the conditions on the list at the same time.

This is how I ended up thinking I would need something like a list box?

Is it more complex than I may have first thought?

Hi Darren, I am myself just a beginner so can not answer your question. I only thought I knew the answer as I just did this last week!

Sorry again, Hope you find what you need...

 

[Darren Guess]

Good Afternoon All

I appreciate your feedback from my first post and although I didn't get the solution I was looking for, it gave me more info to search for a resolution. I have now created a user form that is activated when the user selects cell "J17". Most of the code is from (this post), slightly modified.

This has a ListBox1 and CommandButton1 (Okay)

I now require a couple of tweaks to make it work 100%.

Firstly when a user selects multiple items from the list and selects the 'Okay' button the form should close - which it does, but takes several clicks of the 'Okay' button. What have I missed here? It's almost as if the more selections the more clicks to close?

Secondly I would like to wrap the items on the list when the text is transferred the cell the list points to "J17", what additional code would be required?

Lastly if I were to add another button 'Clear' would I be able to use a simple statement to clear all of the items from cell "J17" to allow the user to start again with the list?

Hopefully I have attached the code tag correctly

In the UserForm code I have used

Private Sub UserForm_Initialize()
    With ListBox1
        .List = Range("E78:E85").Value    'Range of cells with the list Reasons
        .MultiSelect = fmMultiSelectMulti
    End With
 
    UserForm1.Caption = "Select Reason"
    CommandButton1.Caption = "Okay"
 
End Sub
 
Private Sub CommandButton1_Click()
    Dim i As Long, strTemp As String
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then strTemp = strTemp & .List(i) & ", "
        Next i
    End With
    If Len(strTemp) Then
        strTemp = Left(strTemp, Len(strTemp) - 2)
        ActiveCell.Value = strTemp
    End If
    Unload Me
 
End Sub

Code in the worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count >= 1 Then
        'Range of cells to have the pop-up userform
        If Not Intersect(Range("J17"), Target) Is Nothing Then
            UserForm1.Show
        End If
    End If
End Sub

Many Thanks for your help, I am slowly learning

Best Regards
Darren

 

[Darren Guess]

Bump!

Can anyone help me please?

Best regards
Darren