Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Val MultiSelect ?
In my quest to find examples of how to select Multiple values from drop down
list, I found this code from "Contextures" website on how to do this in Excel spreadsheet. However, I am using a Userform and want to know how do I incorporate this into a Userform? Can someone please help me with this? I am new to Excel code and I apologize for my lack of knowledge. Thank you in advance for any help you can provide. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range 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 If Target.Column = 3 Then If Target.Value = "" Then GoTo exitHandler If Target.Offset(0, 1).Value = "" Then Target.Offset(0, 1).Value = Target.Value Else Target.Offset(0, 1).Value = _ Target.Offset(0, 1).Value _ & ", " & Target.Value End If End If End If exitHandler: Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Val MultiSelect ?
TotallyConfused,
On your userform, add a listbox and set its multiselect property to 1- fmMultiSelectMulti, and put the values into it like this using the userform's initital event: Private Sub UserForm_Initialize() Me.ListBox1.List = Worksheets("Sheet1").Range("A1:A10").Value End Sub (Assuming your valid list is in cells A1:A10 of Sheet1) Also add a textbox to your ysefrom for the final string... Add a commandbutton to your userform with code like this: Private Sub CommandButton1_Click() Dim i As Integer Dim myStr As String myStr = "" For i = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) Then If myStr = "" Then myStr = Me.ListBox1.List(i) Else myStr = myStr & ", " & Me.ListBox1.List(i) End If End If Next i 'Do something Me.TextBox1.Text = myStr End Sub Then in code load the userform, show it, and have the user select the items from the listbox and click the commandbutton. It will show the selected items in the text box. HTH, Bernie MS Excel MVP "TotallyConfused" wrote in message ... In my quest to find examples of how to select Multiple values from drop down list, I found this code from "Contextures" website on how to do this in Excel spreadsheet. However, I am using a Userform and want to know how do I incorporate this into a Userform? Can someone please help me with this? I am new to Excel code and I apologize for my lack of knowledge. Thank you in advance for any help you can provide. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo exitHandler Dim rngDV As Range 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 If Target.Column = 3 Then If Target.Value = "" Then GoTo exitHandler If Target.Offset(0, 1).Value = "" Then Target.Offset(0, 1).Value = Target.Value Else Target.Offset(0, 1).Value = _ Target.Offset(0, 1).Value _ & ", " & Target.Value End If End If End If exitHandler: Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MultiSelect Listbox | Excel Programming | |||
MultiSelect Popup | Excel Programming | |||
List Box - MultiSelect | Excel Programming | |||
MultiSelect ListBox | Excel Programming | |||
Multiselect Listbox | Excel Programming |