Answered by:
how to send data to multiple rows

Question
-
Hi,
I want to send data to multiple rows from user form to worksheet. Is it possible ?
This way,
regards
- Edited by drsantoshsinghrathore Monday, June 22, 2015 1:24 AM
Thursday, June 18, 2015 4:43 PM
Answers
-
Hello:
The answer to your question is yes. It is a common developer's procedure to get input from forms and place them in various cells.
First, as you probably know, every combo-box and text box has a name. One common method is to also include a button on the form so that when all the data is collected, the button is pushed. That button would be assigned a "Click" event. In that event, you would move the value from the named combo-box or text box to the cell(s) of your choice. As sample statement might be something like this:
Private Sub SaveContactInformation() Select Case intGblLastContactNumberUpdated Case 1 wksContactStagingArea.Cells(56, 2) = Me.txtContactTitle.Value wksContactStagingArea.Cells(57, 2) = Me.txtContactName.Value wksContactStagingArea.Cells(58, 2) = Me.txtOfficePhone.Value wksContactStagingArea.Cells(59, 2) = Me.txtCellPhone.Value wksContactStagingArea.Cells(60, 2) = Me.txtFaxNumber.Value wksContactStagingArea.Cells(61, 2) = Me.txtEmailAddress.Value If IsDate(wksContactStagingArea.Cells(62, 2)) Then wksContactStagingArea.Cells(63, 2) = Date Else wksContactStagingArea.Cells(62, 2) = Date wksContactStagingArea.Cells(63, 2) = Date End If If IsDate(wksContactStagingArea.Cells(56, 3)) Then Range(wksContactStagingArea.Cells(56, 4), wksContactStagingArea.Cells(61, 4)) = Date Else Range(wksContactStagingArea.Cells(56, 3), wksContactStagingArea.Cells(61, 3)) = Date End If Call UpdateMasterCreatedRevisedDates(Date) Case 2 wksContactStagingArea.Cells(66, 2) = Me.txtContactTitle.Value wksContactStagingArea.Cells(67, 2) = Me.txtContactName.Value wksContactStagingArea.Cells(68, 2) = Me.txtOfficePhone.Value wksContactStagingArea.Cells(69, 2) = Me.txtCellPhone.Value wksContactStagingArea.Cells(70, 2) = Me.txtFaxNumber.Value wksContactStagingArea.Cells(71, 2) = Me.txtEmailAddress.Value If IsDate(wksContactStagingArea.Cells(72, 2)) Then wksContactStagingArea.Cells(73, 2) = Date Else wksContactStagingArea.Cells(72, 2) = Date wksContactStagingArea.Cells(73, 2) = Date End If If IsDate(wksContactStagingArea.Cells(66, 3)) Then Range(wksContactStagingArea.Cells(66, 4), wksContactStagingArea.Cells(71, 4)) = Date Else Range(wksContactStagingArea.Cells(66, 3), wksContactStagingArea.Cells(71, 3)) = Date End If Call UpdateMasterCreatedRevisedDates(Date) Case 3 wksContactStagingArea.Cells(76, 2) = Me.txtContactTitle.Value wksContactStagingArea.Cells(77, 2) = Me.txtContactName.Value wksContactStagingArea.Cells(78, 2) = Me.txtOfficePhone.Value wksContactStagingArea.Cells(79, 2) = Me.txtCellPhone.Value wksContactStagingArea.Cells(80, 2) = Me.txtFaxNumber.Value wksContactStagingArea.Cells(81, 2) = Me.txtEmailAddress.Value If IsDate(wksContactStagingArea.Cells(82, 2)) Then wksContactStagingArea.Cells(83, 2) = Date Else wksContactStagingArea.Cells(82, 2) = Date wksContactStagingArea.Cells(83, 2) = Date End If If IsDate(wksContactStagingArea.Cells(76, 3)) Then Range(wksContactStagingArea.Cells(76, 4), wksContactStagingArea.Cells(81, 4)) = Date Else Range(wksContactStagingArea.Cells(76, 3), wksContactStagingArea.Cells(81, 3)) = Date End If Call UpdateMasterCreatedRevisedDates(Date) End Select End Sub
Notice that the target cells (on the left) are fully qualified. This application used multiple worksheets and it's a best practice to always qualify which worksheet is the recipient of the data. The right side uses the names of the form objects which is the forms's source of the data.
I hope this helps.
Regards,
Rich Locus, Logicwurks, LLC
- Proposed as answer by Starian chenMicrosoft contingent staff Tuesday, June 30, 2015 5:41 AM
- Marked as answer by Fei XueMicrosoft employee Tuesday, June 30, 2015 8:38 AM
Friday, June 19, 2015 4:58 AM -
Hi,
>>I want to send data to multiple rows from user form to worksheet. Is it possible ?
Yes it is, as RichLocus said that you just need to specify the value to the range with the textbox/combox value. The UpdateMasterCreatedRevisedDates is the custom function, you don’t need to use it.
For Range and Cell properties, please refer to:
# Worksheet.Range Property (Excel)
https://msdn.microsoft.com/EN-US/library/office/ff836512.aspx
# Worksheet.Cells Property (Excel)
https://msdn.microsoft.com/EN-US/library/office/ff194567.aspx
We also could setting a range of cells via an array.
# Fun with Excel--setting a range of cells via an array
http://blogs.msdn.com/b/eric_carter/archive/2004/05/04/126190.aspx
>> Can I make an option that data from particular groups of source rows to be transferred ?
The new issue is not directly related to the original issue, it would be better if you open up a new thread for the new question, in this way, our discussion here will not deviate too much from the original issue, this will make answer searching in the forum easier and be beneficial to other community members as well.
Regards
Starain
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.- Proposed as answer by Starian chenMicrosoft contingent staff Tuesday, June 30, 2015 5:39 AM
- Marked as answer by Fei XueMicrosoft employee Tuesday, June 30, 2015 8:38 AM
Tuesday, June 23, 2015 3:15 AM
All replies
-
Hello:
The answer to your question is yes. It is a common developer's procedure to get input from forms and place them in various cells.
First, as you probably know, every combo-box and text box has a name. One common method is to also include a button on the form so that when all the data is collected, the button is pushed. That button would be assigned a "Click" event. In that event, you would move the value from the named combo-box or text box to the cell(s) of your choice. As sample statement might be something like this:
Private Sub SaveContactInformation() Select Case intGblLastContactNumberUpdated Case 1 wksContactStagingArea.Cells(56, 2) = Me.txtContactTitle.Value wksContactStagingArea.Cells(57, 2) = Me.txtContactName.Value wksContactStagingArea.Cells(58, 2) = Me.txtOfficePhone.Value wksContactStagingArea.Cells(59, 2) = Me.txtCellPhone.Value wksContactStagingArea.Cells(60, 2) = Me.txtFaxNumber.Value wksContactStagingArea.Cells(61, 2) = Me.txtEmailAddress.Value If IsDate(wksContactStagingArea.Cells(62, 2)) Then wksContactStagingArea.Cells(63, 2) = Date Else wksContactStagingArea.Cells(62, 2) = Date wksContactStagingArea.Cells(63, 2) = Date End If If IsDate(wksContactStagingArea.Cells(56, 3)) Then Range(wksContactStagingArea.Cells(56, 4), wksContactStagingArea.Cells(61, 4)) = Date Else Range(wksContactStagingArea.Cells(56, 3), wksContactStagingArea.Cells(61, 3)) = Date End If Call UpdateMasterCreatedRevisedDates(Date) Case 2 wksContactStagingArea.Cells(66, 2) = Me.txtContactTitle.Value wksContactStagingArea.Cells(67, 2) = Me.txtContactName.Value wksContactStagingArea.Cells(68, 2) = Me.txtOfficePhone.Value wksContactStagingArea.Cells(69, 2) = Me.txtCellPhone.Value wksContactStagingArea.Cells(70, 2) = Me.txtFaxNumber.Value wksContactStagingArea.Cells(71, 2) = Me.txtEmailAddress.Value If IsDate(wksContactStagingArea.Cells(72, 2)) Then wksContactStagingArea.Cells(73, 2) = Date Else wksContactStagingArea.Cells(72, 2) = Date wksContactStagingArea.Cells(73, 2) = Date End If If IsDate(wksContactStagingArea.Cells(66, 3)) Then Range(wksContactStagingArea.Cells(66, 4), wksContactStagingArea.Cells(71, 4)) = Date Else Range(wksContactStagingArea.Cells(66, 3), wksContactStagingArea.Cells(71, 3)) = Date End If Call UpdateMasterCreatedRevisedDates(Date) Case 3 wksContactStagingArea.Cells(76, 2) = Me.txtContactTitle.Value wksContactStagingArea.Cells(77, 2) = Me.txtContactName.Value wksContactStagingArea.Cells(78, 2) = Me.txtOfficePhone.Value wksContactStagingArea.Cells(79, 2) = Me.txtCellPhone.Value wksContactStagingArea.Cells(80, 2) = Me.txtFaxNumber.Value wksContactStagingArea.Cells(81, 2) = Me.txtEmailAddress.Value If IsDate(wksContactStagingArea.Cells(82, 2)) Then wksContactStagingArea.Cells(83, 2) = Date Else wksContactStagingArea.Cells(82, 2) = Date wksContactStagingArea.Cells(83, 2) = Date End If If IsDate(wksContactStagingArea.Cells(76, 3)) Then Range(wksContactStagingArea.Cells(76, 4), wksContactStagingArea.Cells(81, 4)) = Date Else Range(wksContactStagingArea.Cells(76, 3), wksContactStagingArea.Cells(81, 3)) = Date End If Call UpdateMasterCreatedRevisedDates(Date) End Select End Sub
Notice that the target cells (on the left) are fully qualified. This application used multiple worksheets and it's a best practice to always qualify which worksheet is the recipient of the data. The right side uses the names of the form objects which is the forms's source of the data.
I hope this helps.
Regards,
Rich Locus, Logicwurks, LLC
- Proposed as answer by Starian chenMicrosoft contingent staff Tuesday, June 30, 2015 5:41 AM
- Marked as answer by Fei XueMicrosoft employee Tuesday, June 30, 2015 8:38 AM
Friday, June 19, 2015 4:58 AM -
So I guess it'a a problem of writing textbox value to excel problem, you can try Interop. Import "Microsoft.Office.Core" and "Microsoft.Office.Interop.Excel" COM references. Then try following code:
public void WriteToExcel() { string myPath = @"C:\Excel.xls"; // this must be full path. FileInfo fi = new FileInfo(myPath); if (!fi.Exists) { Console.Out.WriteLine("file doesn't exists!"); } else { var excelApp = new Microsoft.Office.Interop.Excel.Application(); var workbook = excelApp.Workbooks.Open(myPath); Worksheet worksheet = workbook.ActiveSheet as Worksheet; Microsoft.Office.Interop.Excel.Range range = worksheet.Cells[1,1] as Range; range.Value2 = "5"; //excelApp.Visible = true; workbook.Save(); workbook.Close(); } }
refer post here.
Friday, June 19, 2015 6:49 AM -
Hi RichLocus Logicwurks,
Thank you for your reply.
I could not make it work. There is an error.
please see it,Friday, June 19, 2015 1:45 PM -
Beside this with user form. Can we use Excel work sheet format as we do with user form ? or not.
regards
Friday, June 19, 2015 1:47 PM -
Hi,
This code
Sub Copy_Over()
Dim v As Variant, dst As Worksheet
Dim s As String, LastRow As Long
Dim Sh As Worksheet, lr As Long
Set dst = Sheets("DATA")
s = "ISSUE"
v = Split(s, ",")
For Each Sh In ThisWorkbook.Worksheets
If Not IsError(Application.Match(CStr(Sh.Name), v, 0)) Then
LastRow = dst.Cells(Cells.Rows.Count, "A").End(xlUp).Row
lr = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Sh.Rows("7:" & lr).Copy dst.Cells(LastRow + 1, 1)End If
Next Sh
End Subworks fine. I need make some modifications. I want it to paste value and formatting only. No formula or others present on source sheet. Can I make an option that data from particular groups of source rows to be transferred ?
regards
- Edited by drsantoshsinghrathore Saturday, June 20, 2015 2:31 PM
Saturday, June 20, 2015 2:29 PM -
Hi,
>>I want to send data to multiple rows from user form to worksheet. Is it possible ?
Yes it is, as RichLocus said that you just need to specify the value to the range with the textbox/combox value. The UpdateMasterCreatedRevisedDates is the custom function, you don’t need to use it.
For Range and Cell properties, please refer to:
# Worksheet.Range Property (Excel)
https://msdn.microsoft.com/EN-US/library/office/ff836512.aspx
# Worksheet.Cells Property (Excel)
https://msdn.microsoft.com/EN-US/library/office/ff194567.aspx
We also could setting a range of cells via an array.
# Fun with Excel--setting a range of cells via an array
http://blogs.msdn.com/b/eric_carter/archive/2004/05/04/126190.aspx
>> Can I make an option that data from particular groups of source rows to be transferred ?
The new issue is not directly related to the original issue, it would be better if you open up a new thread for the new question, in this way, our discussion here will not deviate too much from the original issue, this will make answer searching in the forum easier and be beneficial to other community members as well.
Regards
Starain
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.- Proposed as answer by Starian chenMicrosoft contingent staff Tuesday, June 30, 2015 5:39 AM
- Marked as answer by Fei XueMicrosoft employee Tuesday, June 30, 2015 8:38 AM
Tuesday, June 23, 2015 3:15 AM