is it possible to list items that are not part of a worksheet

[davidmyers]

Hi,
I have an Excel form for people to fill, in one of the columns I need people to select several items from a list.
I would like this list to be visible on the sheet (for reference only) so people could copy/paste from it. The problem is if I include this list of items in a column and a row was added or deleted then the list would be corrupted. Is it possible to have an excel sheet and a list that appears separately that is not part of the sheet - but from which I could copy from.
I would like to keep this a simple xlsx sheet and not have to add code for a multiselect dropdown list.

Thanks for any help
David

 

[philwojo]

Why not put the list on a separate sheet and then hide that sheet? Is that what you mean, or are you asking something different?

 

[davidmyers]

Hi Phil, Thanks for the quick reply. Yes that is what I'm asking, but I'd like the list to be visible on the same sheet rather than having to switch between sheets to cop/paste each item. Why do you say I should hide the sheet?

Thanks
David

 

[davidmyers]

So is there any way to show a list that's not part of the sheet?

Thanks
David

 

[davidmyers]

Hi the solution I used was to create a UserForm with a ListBox. In properties for listbox set ListStyle = 1, RowSource = SheetName!Named Range.

Then to convert the listbox selections to a comma separated list in the cell use the following code:

Private Sub okBtn_Click()
 Dim text As String
    text = ""
    Dim i As Integer
    For i = 0 To Me.PartsUsedLBox.ListCount - 1
        If Me.PartsUsedLBox.Selected(i) Then
        If (text > " ") Then
            text = text & ", "
        End If
           text = text & Me.PartsUsedLBox.List(i)
        End If
    Next i
    ActiveCell.Value = text
  '  GetSelectedItemsText = text
    Unload Me
End Sub

Hope this helps someone

 

[mikerickson]

You could put this in the ThisWorkbook code module.

' in ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sheet1.Range("A1:A4")
        .Value = Application.Transpose(Array("One", "Two", "Three", "Four"))
    End With
End Sub

 

[davidmyers]

Hi, Don't understand, my question was how to have a list that's not part of the worksheet. UserForm with a ListBox solved that for me.