Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending cboBox values into cells
Hi,
I'm working on a a usrFrm which appends cboBox values into cells when the 'Submit' button is clicked. I want to be able to change the project name (cboBox value) and append fresh data (values of txtBox & cboBox) in the next row down when the 'Submit' button is pressed again, and so on (upto 50 times). Any help would be very much appreciated. I have written the below code to append the first line of data: Private Sub cmdSubmit_Click() Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("Timesheet") ws.Activate ws.Cells(9, 2).Value = Me.cboProjectName.Text ws.Cells(9, 3).Value = Me.txtProjectNumber.Text ws.Cells(9, 4).Value = Me.cboProjectPhase.Text ws.Cells(9, 7).Value = Me.cboMon.Text ws.Cells(9, 8).Value = Me.cboTues.Text ws.Cells(9, 9).Value = Me.cboWed.Text ws.Cells(9, 10).Value = Me.cboThurs.Text ws.Cells(9, 11).Value = Me.cboFri.Text ws.Cells(9, 5).Value = Me.cboSat.Text ws.Cells(9, 6).Value = Me.cboSun.Text End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending cboBox values into cells
Untested...
Private Sub cmdSubmit_Click() Dim wb As Workbook Dim ws As Worksheet Dim NextRow as long Set wb = ThisWorkbook Set ws = wb.Sheets("Timesheet") if me.cboprojectname.text = "" then msgbox "Please complete the project name!" exit sub end if 'no need to activate 'ws.Activate with ws nextrow = .cells(.rows.count,2).end(xlup).row + 1 .Cells(nextrow, 2).Value = Me.cboProjectName.Text .Cells(nextrow, 3).Value = Me.txtProjectNumber.Text .Cells(nextrow, 4).Value = Me.cboProjectPhase.Text .Cells(nextrow, 7).Value = Me.cboMon.Text .Cells(nextrow, 8).Value = Me.cboTues.Text .Cells(nextrow, 9).Value = Me.cboWed.Text .Cells(nextrow, 10).Value = Me.cboThurs.Text .Cells(nextrow, 11).Value = Me.cboFri.Text .Cells(nextrow, 5).Value = Me.cboSat.Text .Cells(nextrow, 6).Value = Me.cboSun.Text end with End Sub I used column B to find the next available row. So the project name has to be filled in. CMcK wrote: Hi, I'm working on a a usrFrm which appends cboBox values into cells when the 'Submit' button is clicked. I want to be able to change the project name (cboBox value) and append fresh data (values of txtBox & cboBox) in the next row down when the 'Submit' button is pressed again, and so on (upto 50 times). Any help would be very much appreciated. I have written the below code to append the first line of data: Private Sub cmdSubmit_Click() Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("Timesheet") ws.Activate ws.Cells(9, 2).Value = Me.cboProjectName.Text ws.Cells(9, 3).Value = Me.txtProjectNumber.Text ws.Cells(9, 4).Value = Me.cboProjectPhase.Text ws.Cells(9, 7).Value = Me.cboMon.Text ws.Cells(9, 8).Value = Me.cboTues.Text ws.Cells(9, 9).Value = Me.cboWed.Text ws.Cells(9, 10).Value = Me.cboThurs.Text ws.Cells(9, 11).Value = Me.cboFri.Text ws.Cells(9, 5).Value = Me.cboSat.Text ws.Cells(9, 6).Value = Me.cboSun.Text End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending cboBox values into cells
Dave,
First up, thanks for your reply. I have run it and unfortunately it's not working. It would appear that each time the @submit' buttonis prssed, it over-writes existing data instead of populating the next row down. Any idea why? Thanks. CMcK "Dave Peterson" wrote: Untested... Private Sub cmdSubmit_Click() Dim wb As Workbook Dim ws As Worksheet Dim NextRow as long Set wb = ThisWorkbook Set ws = wb.Sheets("Timesheet") if me.cboprojectname.text = "" then msgbox "Please complete the project name!" exit sub end if 'no need to activate 'ws.Activate with ws nextrow = .cells(.rows.count,2).end(xlup).row + 1 .Cells(nextrow, 2).Value = Me.cboProjectName.Text .Cells(nextrow, 3).Value = Me.txtProjectNumber.Text .Cells(nextrow, 4).Value = Me.cboProjectPhase.Text .Cells(nextrow, 7).Value = Me.cboMon.Text .Cells(nextrow, 8).Value = Me.cboTues.Text .Cells(nextrow, 9).Value = Me.cboWed.Text .Cells(nextrow, 10).Value = Me.cboThurs.Text .Cells(nextrow, 11).Value = Me.cboFri.Text .Cells(nextrow, 5).Value = Me.cboSat.Text .Cells(nextrow, 6).Value = Me.cboSun.Text end with End Sub I used column B to find the next available row. So the project name has to be filled in. CMcK wrote: Hi, I'm working on a a usrFrm which appends cboBox values into cells when the 'Submit' button is clicked. I want to be able to change the project name (cboBox value) and append fresh data (values of txtBox & cboBox) in the next row down when the 'Submit' button is pressed again, and so on (upto 50 times). Any help would be very much appreciated. I have written the below code to append the first line of data: Private Sub cmdSubmit_Click() Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("Timesheet") ws.Activate ws.Cells(9, 2).Value = Me.cboProjectName.Text ws.Cells(9, 3).Value = Me.txtProjectNumber.Text ws.Cells(9, 4).Value = Me.cboProjectPhase.Text ws.Cells(9, 7).Value = Me.cboMon.Text ws.Cells(9, 8).Value = Me.cboTues.Text ws.Cells(9, 9).Value = Me.cboWed.Text ws.Cells(9, 10).Value = Me.cboThurs.Text ws.Cells(9, 11).Value = Me.cboFri.Text ws.Cells(9, 5).Value = Me.cboSat.Text ws.Cells(9, 6).Value = Me.cboSun.Text End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Appending cboBox values into cells
Dave,
I've figured out why it doesn't work - it was a typo on my part. Really do appreciate your help. THANK YOU!!! CMcK "Dave Peterson" wrote: Untested... Private Sub cmdSubmit_Click() Dim wb As Workbook Dim ws As Worksheet Dim NextRow as long Set wb = ThisWorkbook Set ws = wb.Sheets("Timesheet") if me.cboprojectname.text = "" then msgbox "Please complete the project name!" exit sub end if 'no need to activate 'ws.Activate with ws nextrow = .cells(.rows.count,2).end(xlup).row + 1 .Cells(nextrow, 2).Value = Me.cboProjectName.Text .Cells(nextrow, 3).Value = Me.txtProjectNumber.Text .Cells(nextrow, 4).Value = Me.cboProjectPhase.Text .Cells(nextrow, 7).Value = Me.cboMon.Text .Cells(nextrow, 8).Value = Me.cboTues.Text .Cells(nextrow, 9).Value = Me.cboWed.Text .Cells(nextrow, 10).Value = Me.cboThurs.Text .Cells(nextrow, 11).Value = Me.cboFri.Text .Cells(nextrow, 5).Value = Me.cboSat.Text .Cells(nextrow, 6).Value = Me.cboSun.Text end with End Sub I used column B to find the next available row. So the project name has to be filled in. CMcK wrote: Hi, I'm working on a a usrFrm which appends cboBox values into cells when the 'Submit' button is clicked. I want to be able to change the project name (cboBox value) and append fresh data (values of txtBox & cboBox) in the next row down when the 'Submit' button is pressed again, and so on (upto 50 times). Any help would be very much appreciated. I have written the below code to append the first line of data: Private Sub cmdSubmit_Click() Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook Set ws = wb.Sheets("Timesheet") ws.Activate ws.Cells(9, 2).Value = Me.cboProjectName.Text ws.Cells(9, 3).Value = Me.txtProjectNumber.Text ws.Cells(9, 4).Value = Me.cboProjectPhase.Text ws.Cells(9, 7).Value = Me.cboMon.Text ws.Cells(9, 8).Value = Me.cboTues.Text ws.Cells(9, 9).Value = Me.cboWed.Text ws.Cells(9, 10).Value = Me.cboThurs.Text ws.Cells(9, 11).Value = Me.cboFri.Text ws.Cells(9, 5).Value = Me.cboSat.Text ws.Cells(9, 6).Value = Me.cboSun.Text End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining data from two columns into one - appending new values tothe end of one column | Excel Worksheet Functions | |||
appending text to existing values | Excel Worksheet Functions | |||
Appending Values in the Legend | Charts and Charting in Excel | |||
cboBox to read data from a spreadsheet Range | Excel Programming | |||
cboBox to read data from a spreadsheet Range | Excel Programming |