Hey thanks a lot for your approach @JoePublic, i'll give it a shot. Could you please try and come up with a solution for my second idea of having the values in listbox selected?
One other thing, right now i'm not sure why when i first select all values in the listbox it only displays one values in the dropdown list of data validation and i don't know why maybe you could give it a look on my code. I'd like to overwrite the name range from the name manager everytime a change my selection from the listbox. Here it is:
Code:
Private Sub cmdOkay_Click()
Dim i As Long, j As Long, msg As String, Check As String
'Generate a list of the selected items
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i) & vbNewLine
End If
Next i
End With
If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nu ati facut nicio selectie! Va rog selectati optiunile dvs.!"
Exit Sub
Else
'Ask the user if they are happy with their selection(s)
Check = MsgBox("Ati ales:" & vbNewLine & msg & vbNewLine & _
"Continuati cu selectia facuta?", _
vbYesNo + vbInformation, "Va rog confirmati!")
End If
If Check = vbYes Then
'Unload the userform since user is happy with selection(s)
'Give a name range to your list in column E
'Create data validation list in active cell using named range
Dim strRange As String
Dim celRng1 As Range
lRow = Worksheets("nota_db_omn").Cells(Rows.Count, "J").End(xlUp).Row
Set celRng1 = Worksheets("nota_db_omn").Range("J1:J" & lRow)
celRng1.Value = ""
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
j = j + 1
celRng1(j).Value = .List(i)
.Selected(i) = False
'MsgBox ListBox1.List(i)
End If
Next i
End With
'Run "CreatDropDownList"
Dim celNm As Range
Dim strRngNumLbl As Integer
Dim nm As Name
On Error GoTo pressedCancel:
Set celNm = Application.InputBox(Prompt:= _
"Selectati celula in care va fi creata lista. Ex. E11", _
Title:="Alegeti celula!", Type:=8)
If celNm Is Nothing Then Exit Sub
'cell range equal to nothing
'Set celRng = Nothing
'asks user to determine range of strings
'Set celRng = Application.InputBox(Prompt:= _
'"Please select the range of cells to be included in list.", _
'Title:="SPECIFY RANGE", Type:=8)
If celRng1 Is Nothing Then Exit Sub
On Error GoTo 0
strRange = "celRng1"
strRngNumLbl = 1
'Increments strRngNumLblb for the number of names present in the workbook to
'ensure list is not referring to duplicate ranges
'For Each nm In ThisWorkbook.Names
' strRngNumLbl = strRngNumLbl + 1
' strRange = strRange & strRngNumLbl
' If strRange = nm Then
' strRngNumLbl = strRngNumLbl + 1
' strRange = strRange & strRngNumLbl
' End If
'Next nm
'strRange = strRange & strRngNumLbl
'user defined data range is now called strRange, refer to it as Range(strRange)
ThisWorkbook.Names.Add Name:=strRange, RefersTo:=celRng1
'format the refernce name for use in Validation.add
strRange = "=" & strRange
'celNm.Offset(-1, 0).Select
'Add the drop down list to the target range using the list range
celNm.Validation.Delete
celNm.Validation.Add xlValidateList, , , strRange
'hide the range where the list came from
'celRng.EntireRow.Hidden = True
pressedCancel:
Unload Me
Else
'User wants to try again, so clear listbox selections and
'return user to the userform
For i = 0 To ListBox1.ListCount - 1
ListBox1.Selected(i) = False
Next
End If
End Sub
It's for one of the userforms for my first listbox1 of the three i've been telling you about earlier. Now i have one for each cell i push in sheet1 (of the three E11, F11, and G11). Thank you so much for your support, and everyone who gives a helping hand!
Userform / SELECT CASE ISSUE
Reply With Quote" & LRowd), or Sheet1.Range("f11") - link with values from column in sheet2, and so on. If anyone could come up with a solution i''d so much appreciate it!
Bookmarks