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

davidmyers

Board Regular
Joined
Jan 29, 2017
Messages
77
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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

Board Regular
Joined
Jan 29, 2017
Messages
77
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

Board Regular
Joined
Jan 29, 2017
Messages
77
So is there any way to show a list that's not part of the sheet?

Thanks
David
 

davidmyers

Board Regular
Joined
Jan 29, 2017
Messages
77

ADVERTISEMENT

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:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,903
You could put this in the ThisWorkbook code module.

Code:
' 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

Board Regular
Joined
Jan 29, 2017
Messages
77
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,461
Messages
5,596,274
Members
414,050
Latest member
Rick Royer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top