Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy to bottom under header based on match combobox on userf

0

Hi

I want  to  copy   the  values  from textbox  to  under  specific  column  based on  select  header from  combobox  on userform . if  the  header  in  combobox is  matched  with  header  in row 1 for  the  sheet and  fill  the  textbox1  then  should  copy  to  the  bottom the  column is  matched  with  combobox  .

Private Sub CommandButton1_Click()
Dim arrData, c As Long
arrData = Worksheets("Sample").Range("A1:F1").Value
    For c = LBound(arrData) To UBound(arrData)
        If arrData = Me.combobox1.Value Then TextBox1.Value =....
    Next c

End Sub

thanks 

Answer
Discuss

Answers

0
Selected Answer

Halk

You didn't attach a file to your question (which often saves us work) so I've created an example file (attached).

It has a green button which launches a UserForm with a ComboBox where the values are set by the RowSource property as:

=Sheet2!A1:A6

where Sheet2 is hidden and the values linked to your column headings on sheet Sample (they can be changed).

The modified version of your code is below (with comments) and I used the _Change sub (not _Click) event. The number of items in the List will depend on how many rows are populated below that item.

Private Sub ComboBox1_Change()

Dim arrData, c As Long
arrData = Worksheets("Sample").Range("A1:F1").Value
' loop through array
For c = LBound(arrData, 2) To UBound(arrData, 2)
    'Match value
    If arrData(1, c) = ComboBox1.Value Then
        ' Determine last Row for match
        LstRw = Cells(Rows.Count, c).End(xlUp).Row
        'Repopulate Listbox
        With ListBox1
            .Clear
            .List = Range(Cells(2, c), Cells(LstRw, c)).Value
        End With
    End If
Next c

End Sub

REVISION

The second file below (Add TextBox value from ComboBox choice v0_b.xlsm) takes the above and adds a textbox and command button to the UserForm. If a ComboBox selection is made, the list box shows the current items. If an entry is then made in the new textbox and the "Add new value" command button clicked, it runs this variant of the code above (changes in bold):

Private Sub CommandButton1_Click()

Dim arrData, c As Long
arrData = Worksheets("Sample").Range("A1:F1").Value
' loop through array
For c = LBound(arrData, 2) To UBound(arrData, 2)
    'Match value
    If arrData(1, c) = ComboBox1.Value Then
        ' Determine last Row for match
        LstRw = Cells(Rows.Count, c).End(xlUp).Row
        ' add to the end
        Cells(LstRw + 1, c).Value = TextBox1.Value
        'Repopulate Listbox
        With ListBox1
            .Clear
            .List = Range(Cells(2, c), Cells(LstRw + 1, c)).Value
             ' scroll to new value and highlight
            .ListIndex = .ListCount - 1
            .Selected(.ListCount - 1) = True
        End With
        'clear textbox
        TextBox1.Value = ""
    End If
Next c

End Sub

The result is that value from the text box appears in the worksheet and is highlighted in the ListBox. For example, add "December" with the CB selected as Months.

Hope this works for you.

Discuss

Discussion

sorry John about my  file  !! I  thought  no  need  file .I think  you  don't  understand  me  what  I  want .  using  listbox  depends  on items  are  already  existed  in sheets  but  in  my  case  I  want  entering   new  items  under  each  header , then  I think  the  right  way  to  use  that  should  be    by textbox or  lable  not  listbox .
Halk (rep: 28) Aug 19, '22 at 11:52 am
Halk, a file nearly always helps us to understand what is needed (and saves us needing to create what we think is a solution. Sorry but to be honest I struggled to understand your question (I assume it's a translation).

Do you mean you want to enter a value in a textbox then just send it to the end of the column whose heading matches the ComboBox selection?
John_Ru (rep: 6102) Aug 19, '22 at 12:19 pm
Sorry but to be honest I struggled to understand your question (I assume it's a translation).
this  means  my  english  is  too poor , sorry!!!

Do you mean you want to enter a value in a textbox then just send it to the end of the column whose heading matches the ComboBox selection?
exactly this  is  what I look for  it .
Halk (rep: 28) Aug 19, '22 at 12:43 pm
See the REVISION to my Answer (and second file)
John_Ru (rep: 6102) Aug 19, '22 at 1:00 pm
this  is  better .
Thanks  very much 
Halk (rep: 28) Aug 19, '22 at 1:10 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login