Coding for Combo Box to copy value as text to next empty cell in sheet2

[nadrian]

Hi,

I am new here and I would like to have data from 6 combo boxes (Text) only to second sheet for record. I have linked cells to combo boxes but its not saving value to next available cell. There text fields as well which works fine when I click on Submit button.


Please help

Code:
===================================================
Private Sub CommandButton1_Click()



Range("C10").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "B").End(xlUp).Offset(1, 0)


Range("C11").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "C").End(xlUp).Offset(1, 0)


Range("C12").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "D").End(xlUp).Offset(1, 0)


Range("C13").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "E").End(xlUp).Offset(1, 0)


Range("C14").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "F").End(xlUp).Offset(1, 0)


Range("C15").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "G").End(xlUp).Offset(1, 0)


Range("C16").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "H").End(xlUp).Offset(1, 0)


Range("C19").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "K").End(xlUp).Offset(1, 0)


Range("C22").Copy Destination:=Worksheets("Record").Cells(Worksheets("Record").Rows.Count, "N").End(xlUp).Offset(1, 0)






Range("C10").Value = ""
Range("C11").Value = ""
Range("C12").Value = ""
Range("C13").Value = ""
Range("C14").Value = ""
Range("C15").Value = ""
Range("C16").Value = ""
Range("C19").Value = ""
Range("C22").Value = ""




End Sub
==============================

 

[nadrian]

Apologies for late reply, its was late night in Australia,

I am attaching screen shot herewith for clear picture of what I want. Fist image is tables in excel where combo boxes are also added. Tables data successful copies data to Sheet2 but combo box repeats copying data to linked cell only. I need combo box copy value to next empty cell.

Thanks for time


HOW TO ATTACH FILE?

 

[My Aswer Is This]

I'm sorry we are not understanding each other. And maybe someone else will hop in here and help out but until then. I have asked several questions which you have not answered.

1. Do your comboboxes have values your selecting. Or are you manually entering values into a Combobox

Normally a Combobox has a list of values to choose from. You click on a value and the script in the Combobox runs.

If your users are manually entering values into a combobox I'm not able to help you. Unless you want the script to run when you press a Command button after entering a value in the combobox

And actually if that is how your doing things then you should be using "Textboxes" not Comboboxes.

 

[nadrian]

Hi,
1. I have 6 combo box and it has value where user will select from list only.

I need script for it to populate sheet2 each time user fill.

thanks

 

[My Aswer Is This]

Ok. Thanks. That is the answer I needed.

So Combobox1. value goes to Column "B" and Combobox2 value goes to column "C"
On and on to column "G"

This is to sheet named "Record"

Is that correct?

 

[My Aswer Is This]

Assuming the answer to my previous question is "Yes" then:

You need to put this script into each of your Comboboxes

Modify to each Combobox:

Private Sub [COLOR=#ff0000]ComboBox1[/COLOR]_Change()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Record").Cells(Rows.Count, "[COLOR=#ff0000]B[/COLOR]").End(xlUp).Row + 1
Sheets("Record").Cells(Lastrow, "[COLOR=#ff0000]B[/COLOR]").Value = [COLOR=#FF0000]ComboBox1[/COLOR].Value
Application.ScreenUpdating = True
End Sub

Modify the values highlighted in red for each combobox

"B" in this example refers to Column "B" in sheet named "Record"

 

[nadrian]

Thank you for taking me out of NIGHTMARE.... You code works but there is only one issue. When I select a value and click on any where on sheet it populate Sheet2 (Record) twice. I hope you can figure this out.

I select a value from list in combo box: I see two records in sheet2

Regards

Assuming the answer to my previous question is "Yes" then:

You need to put this script into each of your Comboboxes

Modify to each Combobox:

Private Sub [COLOR=#ff0000]ComboBox1[/COLOR]_Change()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets("Record").Cells(Rows.Count, "[COLOR=#ff0000]B[/COLOR]").End(xlUp).Row + 1
Sheets("Record").Cells(Lastrow, "[COLOR=#ff0000]B[/COLOR]").Value = [COLOR=#FF0000]ComboBox1[/COLOR].Value
Application.ScreenUpdating = True
End Sub

Modify the values highlighted in red for each combobox

"B" in this example refers to Column "B" in sheet named "Record"

 

[My Aswer Is This]

You must click on a value in the combobox list.
You cannot click in the ComboBox and type in a value.
You must just click on a value that was previously loaded into the list.

Every time you click on a value in the list it will enter a value into the sheet "Record"

If you want the user to only be allowed to choose from the list and never enter a value other then what is in the list we should be using a listbox not a Combobox

 

[My Aswer Is This]

If you want the user to be allowed to enter a value manually let me know and I have a plan for that.

 

[nadrian]

Thank for helping me,

User are not allowed to add any value to combo box list. They can only select from available list.

If you want the user to be allowed to enter a value manually let me know and I have a plan for that.

 

[My Aswer Is This]

So are things working for you now? You should never be having the problem you mentioned in post #16. Unless some how you have not modified each combobox script properly.

Thank for helping me,

User are not allowed to add any value to combo box list. They can only select from available list.