Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Userform / SELECT CASE ISSUE

  1. #1

    Exclamation Userform / SELECT CASE ISSUE



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    
    Private Sub UserForm_Initialize()
    Dim cld, clf, 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
    '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 cld In Worksheets("nota_db_omn").Range("D2:D" & LRowd)
    'For Each cl In Worksheets("Sheet1").rng1
    .AddItem cld.Value
    Next cld
    End Select
    For Each clf In Worksheets("nota_db_omn").Range("F2:F" & LRowf)
    'For Each cl In Worksheets("Sheet1").rng1
    .AddItem clf.Value
    Next clf
    For Each clg In Worksheets("nota_db_omn").Range("G2:G" & LRowg)
    'For Each cl In Worksheets("Sheet1").rng1
    .AddItem clg.Value
    Next clg
    End With

    -on the listbox1 i have all the values from sheet2 in these 3 ranges: Range("D2" & 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 JoePublic; 2013-12-11 at 11:04 AM.

  2. #2
    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" & 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 JoePublic; 2013-12-11 at 11:02 AM.

  3. #3
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    194
    Articles
    0
    Excel Version
    2010
    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.
    This website wants to know your momentum - | Deny | | Allow |

  4. #4
    Well in listbox when i initialize userform are coming data from three column in the second sheet (Worksheets("nota_db_omn").Range("D2" & 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!

  5. #5
    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!

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    194
    Articles
    0
    Excel Version
    2010
    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.
    This website wants to know your momentum - | Deny | | Allow |

  7. #7
    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 JoePublic; 2013-12-12 at 09:48 AM.

  8. #8
    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!

  9. #9
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    194
    Articles
    0
    Excel Version
    2010
    Can you please post a workbook. I am struggling to follow what you are talking about.
    This website wants to know your momentum - | Deny | | Allow |

  10. #10
    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 by mef1sto; 2013-12-12 at 11:27 AM.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •