none
combo box RRS feed

  • Question

  • 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


    Hi Luna Zhang - MSFT,

    Please do some modification please. when I select value from E4:E in combo box repeat cases. The Text box 5 should automatically take corresponding value from J4:J. For example if E7 has 3-5 and J7 has 3. If CB repeat value select 3-5, TB 5 should take 3. And if CB repeat cases does not select from E4:E. Instead entered by typing name then TB 5 should also require typing any number.

    Thank you,

    dockhem

    Thursday, August 21, 2014 9:41 AM

Answers

  • Hi,

    Please try the code below in your user form. The error is related to the variable "c".

    In addition, I find you fill the combo box "RepeatCases" with the values in Column D instead of Column E as you said in original post.

    Private Sub RepeatCases_Change()
    'clear the text in TextBox5
    TextBox5.Value = ""
    
    Dim ws As Worksheet
    Dim name As String
    Dim c As Range
    
    Set ws = ActiveWorkbook.Worksheets("DATA")
    i = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
    
    If RepeatCases.Value <> "" Then
        name = RepeatCases.Value
        'find the selected value from column E
        Set c = ws.Range("D4:D" & i).Find(name, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            'set the related value in J column to TextBox5
            TextBox5.Value = ws.Range("J" & c.Row).Value
        End If
    End If
    
    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 Wednesday, August 27, 2014 4:55 AM
    Tuesday, August 26, 2014 8:00 AM
    Moderator

All replies

  • Hi,

    In fact, this is a professional and powerful forum, so I think all of the communities will be glad to help you and share their experience. So you just need to clarify your issues clearly and involved all the communities to this thread.

    In addition, in your original thread, you want to populate the items of a combobox in the user form based on the values of a column in the worksheet. But I'm not sure about your other requirements about the user form and the relationship between the controls.

    Based on your description, I think your issue is more related to the logic instead of technology. Would you mind splitting the technology points from your requirement so that we can share our technical experience?

    I suspect the "CB" means the combobox and "TB" means the textbox, but what's the meaning of repeat cases?

    >>The Text box 5 should automatically take corresponding value from J4:J.<<

    How do you make a text box to take corresponding value from a list of cells?


    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.

    Friday, August 22, 2014 8:59 AM
    Moderator
  • Hi,

    In fact, this is a professional and powerful forum, so I think all of the communities will be glad to help you and share their experience. So you just need to clarify your issues clearly and involved all the communities to this thread.

    In addition, in your original thread, you want to populate the items of a combobox in the user form based on the values of a column in the worksheet. But I'm not sure about your other requirements about the user form and the relationship between the controls.

    Based on your description, I think your issue is more related to the logic instead of technology. Would you mind splitting the technology points from your requirement so that we can share our technical experience?

    I suspect the "CB" means the combobox and "TB" means the textbox, but what's the meaning of repeat cases?

    >>>>>>> Yes you are right "CB" and "TB" means the same as you suspect. I think you ignored the code above, repeat case is also a combo box as it named this way. This combo box (repeat case) populates and select value from E4:E. There is data in E4:E and also in neighbouring columns in a row. I want Text box 5 to take value from column J4:J. 

      Is it possible or not ? If not Then I will try formula. whether it works or not  ! I prefer vba code please help.

    dockhem   


    Sunday, August 24, 2014 9:03 AM
  • Hi,

    According to your description, I think you want to get the related value from the J column to fill the Text Box 5 based on the selected value in the Combobox "RepeatCases".

    I think you could resort to the Combobox.Change event, which occurs when the contents of the combobox changes. In the event, we can use Range.Find method to find the cell which stores the selected value in column E and get the data of column J in the same row to fill the Text Box 5.

    Here is a sample for your reference. In my sample, I clear the value of Text Box 5 at the beginning of Combobox.Change event. Then if I type the name in the Combobox instead of selecting and the name doesn't exist in the items of the combobox, the text box won't be filled automatically.

    Private Sub RepeatCases_Change()
    'clear the text in TextBox5
    TextBox5.Value = ""
    
    Dim ws As Worksheet
    Dim name As String
    
    i = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
     SearchDirection:=xlPrevious).Row
    Set ws = ActiveWorkbook.Worksheets("DATA")
    
    If RepeatCases.Value <> "" Then
        name = RepeatCases.Value
        'find the selected value from column E
        Set c = ws.Range("E4:E" & i).Find(name, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            'set the related value in J column to TextBox5
            TextBox5.Value = ws.Range("J" & c.Row).Value
        End If
    End If
    
    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.

    Monday, August 25, 2014 9:55 AM
    Moderator
  • Hi Luna Zhang - MSFT

    Compile error

    variable not defined

    please help,

    dockhem

    Tuesday, August 26, 2014 5:04 AM
  • Hi,

    Which lines pop up the error message? Are you sure your text box names "TextBox5"?

    You have to debug to find the error lines or share your sample workbook through OneDrive for us to troubleshoot.


    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 26, 2014 5:18 AM
    Moderator
  • Hi,

    I am sorry.

    I could not correct problem. Please see this file http://1drv.ms/1pB6UK1

    regards

    dockhem

    Tuesday, August 26, 2014 7:11 AM
  • You need to upload the macro-enable workbook(.xlsm) instead of a xls file, so that we can test you code.

    Actually, no one understand your requirement more than your self. If you don't have any experience of VBA,  I suggest you to learn more about it rather than ask for a code which you don't understand totally.


    Tuesday, August 26, 2014 7:29 AM
  • Tuesday, August 26, 2014 7:45 AM
  • Hi,

    Please try the code below in your user form. The error is related to the variable "c".

    In addition, I find you fill the combo box "RepeatCases" with the values in Column D instead of Column E as you said in original post.

    Private Sub RepeatCases_Change()
    'clear the text in TextBox5
    TextBox5.Value = ""
    
    Dim ws As Worksheet
    Dim name As String
    Dim c As Range
    
    Set ws = ActiveWorkbook.Worksheets("DATA")
    i = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
    
    If RepeatCases.Value <> "" Then
        name = RepeatCases.Value
        'find the selected value from column E
        Set c = ws.Range("D4:D" & i).Find(name, LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            'set the related value in J column to TextBox5
            TextBox5.Value = ws.Range("J" & c.Row).Value
        End If
    End If
    
    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 Wednesday, August 27, 2014 4:55 AM
    Tuesday, August 26, 2014 8:00 AM
    Moderator
  • Hi Luna Zhang - MSFT,

    Thank you,

    Can you please add Text box 7 to 16 using list index.

    ws.Cells(i + 1, ComboBox7.ListIndex + 42).Value = TextBox7.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox8.ListIndex + 42).Value = TextBox8.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox9.ListIndex + 42).Value = TextBox9.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox10.ListIndex + 42).Value = TextBox10.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox11.ListIndex + 42).Value = TextBox11.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox12.ListIndex + 42).Value = TextBox12.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox13.ListIndex + 42).Value = TextBox13.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox14.ListIndex + 42).Value = TextBox14.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox15.ListIndex + 42).Value = TextBox15.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox16.ListIndex + 42).Value = TextBox16.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox17.ListIndex + 42).Value = TextBox17.Text * Cells(i + 1, 10).Value
    ws.Cells(i + 1, ComboBox18.ListIndex + 42).Value = TextBox18.Text * Cells(i + 1, 10).Value

    regards

    dockhem

    Tuesday, August 26, 2014 9:32 AM
  • Hi chunchencool,

    Please forgive me for my ignorance. Actually I am layman in MS OFFICE. I tried to gain things from people like you here. These gave me ability to build this worksheet. I need more help please.

    dockhem

    Tuesday, August 26, 2014 10:03 AM
  • Hi,

    Since your original post has been achieved, please mark the useful reply as the answer.  

    For more questions, in order to involve more partners of this community to share their knowledge or learn from your interaction with us, I suggest you posting a new thread in the forum.


    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.

    Wednesday, August 27, 2014 1:56 AM
    Moderator