none
combo box RRS feed

  • Question

  • Hi,

    I have a combo box in user form. This combo form uses data column as drop down to populate. The data is like this,

    1-4

    5-8

    9-11

      -

      -

      0

    12-56

    57-58

    and so on. I want combo box to populate only 1-4, 5-8, not the - or 0 etc. And above should be like this,

     

    1-4

    5-8

    9-11

    12-56

    57-58

    Please help,

    dockhem

     

    Monday, August 18, 2014 6:15 PM

Answers

  • Hi,

    >>Is it correct ? Though it is working.<<

    I think it's correct since you said it works.

    If you just want to make the code short, you could remove the duplicate lines as followed.

    Private Sub UserForm_Initialize()
     i = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
     SearchDirection:=xlPrevious).Row
     Set ws = ActiveWorkbook.Worksheets("DATA")
             If Cells(4, 1) = "" Then
                  TxtCount.Text = 1
                     SpinButton1.Max = 0
             Else
               TxtCount.Text = ws.Cells(i, 1) + 1
                  End If
                     Me.TxtDate = Format(Date, "mm/dd/yyyy")
                     Me.ComboBox1.List = WorksheetFunction.Transpose(ws.Range("K3:P3"))
     'MEDICINE GROUP 1
                     Me.ComboBox7.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
    
    
     Dim rng As Range
     For Each rng In ws.Range("E4:E" & i)
         If Trim(rng.Value) <> "0" And Trim(rng.Value) <> "-" Then
             RepeatCases.AddItem rng.Value
         End If
     Next rng
    
     End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by dockhem2 Thursday, August 21, 2014 8:04 AM
    Thursday, August 21, 2014 3:31 AM
    Moderator
  • Hi,

    >>I want combo box to populate only 1-4, 5-8, not the - or 0 etc.<<

    According to your description, I think you want to populate the items of a combobox in the user form based on the values of a column in the worksheet.

    If I understand correctly, you could loop through all the cells in the column and check whether its value is "0" or "-". Then if no, you can resort to AddItem method of combobox object to add the value.

    Here is a sample for your reference. In my sample, the data comes from Column D of Sheet1 and I populate the items in the Initialize event of the user form.

    Private Sub UserForm_Initialize()
    Dim rng As Range
    Dim sh As Worksheet
    Set sh = ActiveWorkbook.Sheets("Sheet1")
    r = sh.Range("D" & sh.Rows.Count).End(xlUp).Row
    For Each rng In sh.Range("D1:D" & r)
        If Trim(rng.Value) <> "0" And Trim(rng.Value) <> "-" Then
            ComboBox1.AddItem rng.Value
        End If
    Next rng
    
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 19, 2014 7:14 AM
    Moderator

All replies

  • Hi,

    >>I want combo box to populate only 1-4, 5-8, not the - or 0 etc.<<

    According to your description, I think you want to populate the items of a combobox in the user form based on the values of a column in the worksheet.

    If I understand correctly, you could loop through all the cells in the column and check whether its value is "0" or "-". Then if no, you can resort to AddItem method of combobox object to add the value.

    Here is a sample for your reference. In my sample, the data comes from Column D of Sheet1 and I populate the items in the Initialize event of the user form.

    Private Sub UserForm_Initialize()
    Dim rng As Range
    Dim sh As Worksheet
    Set sh = ActiveWorkbook.Sheets("Sheet1")
    r = sh.Range("D" & sh.Rows.Count).End(xlUp).Row
    For Each rng In sh.Range("D1:D" & r)
        If Trim(rng.Value) <> "0" And Trim(rng.Value) <> "-" Then
            ComboBox1.AddItem rng.Value
        End If
    Next rng
    
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 19, 2014 7:14 AM
    Moderator
  •  Hi Luna Zhang - MSFT

    Thank you for your kind reply,

    please forgive my ignorance. I tried this way, Is it correct ? Though it is working.

    Private Sub UserForm_Initialize()
    i = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    Set ws = ActiveWorkbook.Worksheets("DATA")
            If Cells(4, 1) = "" Then
                 TxtCount.Text = 1
                    SpinButton1.Max = 0
            Else
              TxtCount.Text = ws.Cells(i, 1) + 1
                 End If
                    Me.TxtDate = Format(Date, "mm/dd/yyyy")
                    Me.ComboBox1.List = WorksheetFunction.Transpose(ws.Range("K3:P3"))
    'MEDICINE GROUP 1
                    Me.ComboBox7.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))

    Dim rng As Range
    Dim sh As Worksheet
    Set sh = ActiveWorkbook.Sheets("DATA")
    i = sh.Range("E" & sh.Rows.Count).End(xlUp).Row
    For Each rng In sh.Range("E4:E" & i)
        If Trim(rng.Value) <> "0" And Trim(rng.Value) <> "-" Then
            RepeatCases.AddItem rng.Value
        End If
    Next rng
    End Sub


    • Edited by dockhem2 Wednesday, August 20, 2014 5:53 PM
    Wednesday, August 20, 2014 5:34 PM
  • Can you make it short please

    dockhem

    Wednesday, August 20, 2014 5:54 PM
  • Hi,

    >>Is it correct ? Though it is working.<<

    I think it's correct since you said it works.

    If you just want to make the code short, you could remove the duplicate lines as followed.

    Private Sub UserForm_Initialize()
     i = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
     SearchDirection:=xlPrevious).Row
     Set ws = ActiveWorkbook.Worksheets("DATA")
             If Cells(4, 1) = "" Then
                  TxtCount.Text = 1
                     SpinButton1.Max = 0
             Else
               TxtCount.Text = ws.Cells(i, 1) + 1
                  End If
                     Me.TxtDate = Format(Date, "mm/dd/yyyy")
                     Me.ComboBox1.List = WorksheetFunction.Transpose(ws.Range("K3:P3"))
     'MEDICINE GROUP 1
                     Me.ComboBox7.List = WorksheetFunction.Transpose(ws.Range("AP3:FK3"))
    
    
     Dim rng As Range
     For Each rng In ws.Range("E4:E" & i)
         If Trim(rng.Value) <> "0" And Trim(rng.Value) <> "-" Then
             RepeatCases.AddItem rng.Value
         End If
     Next rng
    
     End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by dockhem2 Thursday, August 21, 2014 8:04 AM
    Thursday, August 21, 2014 3:31 AM
    Moderator
  • Hi Luna Zhang - MSFT

    Thanks

    dockhem

    Thursday, August 21, 2014 8:04 AM