Userform / SELECT CASE ISSUE

M

mef1sto

New member
Joined
Dec 11, 2013
Messages
8
Reaction score
0
Points
0
Hello
I have this situation:
If Not Intersect(target, Range("E11:G11")) Is Nothing Then
Run "Launch"
End If
-lunch is a user form and on this form i have a listbox1 and a cmdOkay
Code: 
[COLOR=#333333]
Private Sub UserForm_Initialize()
[/COLOR][COLOR=#333333]Dim cld, clf, clg As Range[/COLOR]
[COLOR=#333333]Dim cl As Range[/COLOR]


[COLOR=#333333]LRowd = Worksheets("nota_db_omn").Cells(Rows.Count, "D").End(xlUp).Row[/COLOR]
[COLOR=#333333]LRowf = Worksheets("nota_db_omn").Cells(Rows.Count, "F").End(xlUp).Row[/COLOR]
[COLOR=#333333]LRowg = Worksheets("nota_db_omn").Cells(Rows.Count, "G").End(xlUp).Row[/COLOR]
[COLOR=#333333]'Load the combobox with a variety of household pets[/COLOR]
[COLOR=#333333]With Me.ListBox1[/COLOR]
[COLOR=#333333]'Clear the rowsource in case it has been set[/COLOR]
[COLOR=#333333].RowSource = ""[/COLOR]
[COLOR=#333333]'Dim rng1 As Range[/COLOR]
[COLOR=#333333]'Set rng1 = Sheets("nota_db_omn").Range("D2:D3")[/COLOR]
[COLOR=#333333]'ActiveWorkbook.Names.Add Name:="reglementari", RefersTo:=rng1[/COLOR]
[COLOR=#333333]'Add the items from Cell A10 to the last used row in column A[/COLOR]
[COLOR=#333333]Select Case cl[/COLOR]
[COLOR=#333333]Case cld[/COLOR]
[COLOR=#333333]For Each cld In Worksheets("nota_db_omn").Range("D2:D" & LRowd)[/COLOR]
[COLOR=#333333]'For Each cl In Worksheets("Sheet1").rng1[/COLOR]
[COLOR=#333333].AddItem cld.Value[/COLOR]
[COLOR=#333333]Next cld[/COLOR]
[COLOR=#333333]End Select[/COLOR]
[COLOR=#333333]For Each clf In Worksheets("nota_db_omn").Range("F2:F" & LRowf)[/COLOR]
[COLOR=#333333]'For Each cl In Worksheets("Sheet1").rng1[/COLOR]
[COLOR=#333333].AddItem clf.Value[/COLOR]
[COLOR=#333333]Next clf[/COLOR]
[COLOR=#333333]For Each clg In Worksheets("nota_db_omn").Range("G2:G" & LRowg)[/COLOR]
[COLOR=#333333]'For Each cl In Worksheets("Sheet1").rng1[/COLOR]
[COLOR=#333333].AddItem clg.Value[/COLOR]
[COLOR=#333333]Next clg[/COLOR]
[COLOR=#333333]End With[/COLOR]


-on the listbox1 i have all the values from sheet2 in these 3 ranges: Range("D2:D" & LRowd); Range("F2:F" & LRowf) and Range("G2:G" & LRowg)
-I want that when i select Range("E11") only the cells from cld to show up on the listbox1, NOT all of them, so i think i need a SELECT CASE for these ranges, but I DONT KNOW HOW TO PUT IT, so..
CAN ANYONE PLEASE HELP ME??? THANKS!
 
Last edited by a moderator:
Maybe i don't know..though it doesn't work
Code: 
Private Sub UserForm_Initialize()
Dim cld As Range
Dim clf As Range
Dim clg As Range
Dim cl As Range


LRowd = Worksheets("nota_db_omn").Cells(Rows.Count, "D").End(xlUp).Row
LRowf = Worksheets("nota_db_omn").Cells(Rows.Count, "F").End(xlUp).Row
LRowg = Worksheets("nota_db_omn").Cells(Rows.Count, "G").End(xlUp).Row


Set cld = Worksheets("nota_db_omn").Range("D2:D" & LRowd)
Set clf = Worksheets("nota_db_omn").Range("F2:F" & LRowf)
Set clg = Worksheets("nota_db_omn").Range("G2:G" & LRowg)
'Load the combobox with a variety of household pets
With Me.ListBox1
'Clear the rowsource in case it has been set
.RowSource = ""
'Dim rng1 As Range
'Set rng1 = Sheets("nota_db_omn").Range("D2:D3")
'ActiveWorkbook.Names.Add Name:="reglementari", RefersTo:=rng1
'Add the items from Cell A10 to the last used row in column A
Select Case cl
Case cld
For Each cl In cld
'For Each cl In Worksheets("Sheet1").rng1
.AddItem cl.Value
Next cl
For Each cl In clf
'For Each cl In Worksheets("Sheet1").rng1
.AddItem cl.Value
Next cl
For Each cl In clg
'For Each cl In Worksheets("Sheet1").rng1
.AddItem cl.Value
Next cl
End Select
End With
End Sub
And i don't know how to link each of these ranges with each cell i select on Sheet1 that is Sheet1.Range("e11") - link with values from Worksheets("nota_db_omn").Range("D2:D" & 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!
 
Last edited by a moderator:
I'm afraid I don't follow you. Can you explain clearly how you determine what should go in the listbox - preferably with a few specific examples.
 
Well in listbox when i initialize userform are coming data from three column in the second sheet (Worksheets("nota_db_omn").Range("D2:D" & LRowd), Worksheets("nota_db_omn").Range("F2:F" & LRowf), and Worksheets("nota_db_omn").Range("G2:G" & LRowg)). this userform is shown when i select three cells in the first sheet ( If Not Intersect(target, Range("E11:G11")) Is Nothing Then
Run "Launch"
End If) so cell e11, cell f11 and cell g11 from sheet1. Right now in the listbox i have all of the second sheets' three columns data, and i wanted to make a corelation between my first sheets three cells and the second ones three cells( for sheet1.E11 - sheet2.d2 and so on) and after i populate the listbox with that data in each of the sheet1 cells create a DropDownList of data validation type if possible. So i figured that if i took sheet2 columns data and put it in listbox and afterwards put that data in a cell in sheet2 and with it create the dropdown validation list from the data i previously select from the listbox. Now i hope i made myself a lot clearer to understand. Thanks!
 
Well eventually what i did was i built 3 userforms for each cell in sheet1 with their one events for listbox. Now unfortunatelly when i select one or more values from the listbox it creates as i said a validation list with a range each time i pick different values from the listbox, it doesn't rewrites itself each time i pick other values from the listbox. On the other hand i'd be satisfied with having a selection from the listbox and the next time i popup the listbox those selection remain selected from the last time i selected them. Not sure thats possible but i wish i could do such thing. I need to remember selected values from a list and i believe its the only way like this. Thanks!
 
Basic code for the listbox would be:
Code: 
Private Sub UserForm_Initialize()
   Dim ws As Worksheet
   Dim cld                         As Range
   
   Set ws = Worksheets("nota_db_omn")
   ' check which cell is active
   Select Case ActiveCell.Column
      Case 5 ' column E
         Set cld = ws.Range("D2", ws.Cells(ws.Rows.Count, "D").End(xlUp))
      Case 6 ' column F
         Set cld = ws.Range("F2", ws.Cells(ws.Rows.Count, "F").End(xlUp))
      Case 7 ' column G
         Set cld = ws.Range("G2", ws.Cells(ws.Rows.Count, "G").End(xlUp))
      End Select
   'Load the listbox
   Me.ListBox1.List = cld.Value
End Sub

I'm not sure where the validation lists come into it.
 
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!
 
Last edited by a moderator:
I have this serious issue right now..i can't understand why, as i said the first time when i select multiple values from the listbox in the dropdown list of the data validation range i only have one record regardless that in sheet2 column of that listbox where i store selected values, it actually puts what i select in the listbox, but it only displays one of them, and if i do it again only then it display correct, i mean all of what i selected. Can you adjust this error, i really didn't know how! :(
 
Can you please post a workbook. I am struggling to follow what you are talking about.
 
Well i'm afraid i won't manage to share it. In this area of code:

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

the first time i select item from the listboxm the dropdown list only displays one record from the sheet2 where it stores these values from that column. So now what i managed to do was i've changed last row from sheet2 column with lRow = Worksheets("nota_db_omn").Range("J" & Rows.Count).End(xlUp).Offset(i, 0).Row and now it displays correct only that if i select two out of four it will display a dropdown list of four rows but with only those two values selected from a total of four (the listcount number). Thanks a lot. Though I would appreciate if you could give it a shoot to my desire of keeping the selected item from the listbox each time it launches ..
Set celRng1 = Worksheets("nota_db_omn").Range("J1:J" & lRow)
celRng1.Value = ""With ListBox1For 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 IfNext iEnd With
 
Last edited:
This is the code from Sheet1 where i call the launch of each userforms:
Private Sub Worksheet_SelectionChange(ByVal target As Range)
If ActiveSheet.ProtectContents = False Then


If Not Intersect(target, Range("E11")) Is Nothing Then
Application.OnDoubleClick = "Launch1"
End If
If Not Intersect(target, Range("F11")) Is Nothing Then
Application.OnDoubleClick = "Launch2"
End If
If Not Intersect(target, Range("G11")) Is Nothing Then
Application.OnDoubleClick = "Launch3"
End If

and its module:

Sub Launch1()
UserForm1.Show
End Sub
Sub Launch2()
UserForm2.Show
End Sub
Sub Launch3()
UserForm3.Show
End Sub

Moreover, Now if i double click any other cell besides those three (E11, F11, G11) the output it's still the same, it will bring the last listbox that i called. Any explanation?
 
You must log in or register to reply here.
Back
Top