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

Answer 1

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

http://www.logicwurks.com

Answer 2

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.

Answer 3

Hi RichLocus Logicwurks,
Thank you for your reply.
I could not make it work. There is an error.
please see it,

Answer 4

Beside this with user form. Can we use Excel work sheet format as we do with user form ? or not.

regards

Answer 5

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 Sub

works 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

Answer 6

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.