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
==============================

 

[My Aswer Is This]

When I enter your script into a submit button it works for me.
Are you wanting this to happen when you enter a value into the Combobox or when you press a submit button?

 

[nadrian]

Thanks for reply....it only copy data once for combo box. I need to copy data again in next empty cell upon submit.

 

[My Aswer Is This]

Normally a Combobox is initially loaded with values. And then as users select values in the combobox the value selected will then be entered into another cell or sheet.

The Combobox would not need to be linked to a cell. Linking the combobox to a cell will not work for what you want. As far as I know.

The Combobox would then have a script installed telling the combobox what to do when a item in the list is selected.
Explain more what your attempting to do and maybe I can help you.

 

[My Aswer Is This]

You did not answer my question.

"Are you wanting this to happen when you enter a value into the Combobox or when you press a submit button?"

 

[My Aswer Is This]

And as I previously mentioned when I run the script you supplied it adds the values to the next empty cell as you want.

 

[nadrian]

Thank you for reply. You are right i want exactly what you said. I want user in our local network fill this form and submit; it should save data into next empty cell. At the moment it only copy data from tables into next empty cell when I submit but combo box repeatedly save it to first cell.
Kindly help me code for combo box to save value into. Sheet2 empty cell.

Thanks in advance

 

[My Aswer Is This]

When you say:

local network fill this form and submit

What is a "Form"

Do you mean a "UserForm"

 

[nadrian]

This excel sheet will be shared on intranet and user will be filling it personal detail and in tables and choice from dropdown (combo box) with submit button. Data need to be saved in sheet2 according.

Thanks

 

[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 successfuly 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 ADD IMAGE HERE?

You did not answer my question.

"Are you wanting this to happen when you enter a value into the Combobox or when you press a submit button?"