Excel 2007 vba user form trouble copying and pasting values in the same column

probio · Sep 13, 2013

Hi everyone,
I have made a user form with a goal to collect some data in excel spreadsheet (columns: Date, Team, Name, and Number of Events). In the column “Name” (column "C") I need to capture the name of the Associate who is reporting and also names of Associates who are involved in tasks. There are maximum number of 8 Associates involved in tasks but very often, the number of them is less than that. I’d like to make the form work so that every time when the number of associates involved in tasks is less than 8, their names will be recorded accordingly and each Date cell (column "A") next to them needs to get the info about same date, and number of events as well (column "J").

With the code that I made I get date (column "A") next to the name of Associate who is reporting and the number of Events in column "J", same row but I don’t get date and number of Events for Associates who are involved.

Any input is appreciated.

Code:

Private Sub CommandButton1_Click()

   
  Dim iRow As Long
  Dim ws As Worksheet
  Set ws = Worksheets("Probios")
   
  'find first empty row in database
  iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
   
  'check for a Date number
  If Trim(Me.textbox_date.Value) = "" Then
   
  MsgBox "Please complete the form"
  Exit Sub
  End If
   
  ws.Cells(iRow, 1).Value = Me.textbox_date.Value
   
  ws.Cells(iRow, 2).Value = UserForm1.listbox_Team.Value
   
  ws.Cells(iRow, 3).Value = UserForm1.listbox_AssociateReporting.Value
   
  ws.Cells(iRow + 1, 3).Value = UserForm1.listbox_AssociateInvolved1.Value
  ws.Cells(iRow + 2, 3).Value = UserForm1.listbox_AssociateInvolved2.Value
  ws.Cells(iRow + 3, 3).Value = UserForm1.listbox_AssociateInvolved3.Value
  ws.Cells(iRow + 4, 3).Value = UserForm1.listbox_AssociateInvolved4.Value
  ws.Cells(iRow + 5, 3).Value = UserForm1.listbox_AssociateInvolved5.Value
  ws.Cells(iRow + 6, 3).Value = UserForm1.listbox_AssociateInvolved6.Value
  ws.Cells(iRow + 7, 3).Value = UserForm1.listbox_AssociateInvolved7.Value
  ws.Cells(iRow + 8, 3).Value = UserForm1.listbox_AssociateInvolved8.Value
   
   
  ws.Cells(iRow, 10).Value = UserForm1.combobox_Events.Value
   
  MsgBox "Data added.", vbOKOnly + vbInformation, "Data Added"
   
  'clear the data
  Me.listbox_AssociateReporting.Value = ""
  Me.textbox_date.Value = ""
  Me.listbox_Team.Value = ""
  Me.listbox_Shift.Value = ""
  Me.listbox_Department.Value = ""
  Me.combobox_Equipment.Value = ""
  Me. combobox_Events.Value = ""
   
  End Sub

Excel 2007 vba user form trouble copying and pasting values in the same column

probio

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

Excel 2007 vba user form trouble copying and pasting values in the same column

probio

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock