Need some help with VBA and Userform Please

DeezNuts

Board Regular
Joined
Aug 12, 2014
Messages
177
Okay so I tried to do this on my own. Which in someways was good learned somethings. On the flip side I have aged 30 years and have pulled half my hair out. I have tried to get the information to input on the form put I can not get it in how I would like it. It is all going in the same row and It should be like the example below starting at row 6 and using 3 rows 5 columns input the data, then the next one that gets input would skip a row and fill in 3 rows and 5 columns. This pattern will continue down the page. Hopefully someone can edit my current code to work in this fashion.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Assignments[/TD]
[TD][/TD]
[TD][/TD]
[TD]Received[/TD]
[TD]Possible[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Assignment Name[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Due Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Assignment Type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]SKIP THIS ROW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Assignment Name[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Due Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Assignment Type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Code:
Private Sub cmdSubmit_Click()
Application.ScreenUpdating = False

    Dim rNextCl As Range
    Dim NextRw As Long
      
 With Sheet1    'find next empty row using Column A
        NextRw = .Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
      
'This information starts at row 6 and goes down in groups of 3 rows and skips a row, so there is a
'blank row between the groups of 3 rows.

        .Cells(NextRw, 1).Value = Me.txtAssignmentName.Value 'this would go in A6
        .Cells(NextRw, 2).Value = Me.txtDate                 'this would go in A7
        .Cells(NextRw, 3).Value = Me.txtAssignmentType.Value 'this would go in A8
        .Cells(NextRw, 4).Value = Me.txtPointsReceived.Value 'this would go in D6 would be nice if the default if left blank would input a "-" in the sheet
        .Cells(NextRw, 5).Value = Me.txtPointsPossible.Value 'this would go in E6
        
 'So this information would be Row 6:8 Then when I want to input another assignment it would
 'skip a row and input the data. Not sure if it pushes the previous data down.
     
        
    End With
    'confirm data transferred
   
 Unload Me
End Sub

Hopefully this all makes sense as my mind is not functioning correctly. Thank you for looking.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Bummer thought this was going to be a simple fix tells me I got a longs ways to go. Well there is always tomorrow goodnight.
 
Upvote 0
Would some be kind enough to point me to a location that would show how to install this userform data on 3 rows instead of one row. I have looked using many different search variations on google and I cant find a single thing that talks about several rows of data.
 
Upvote 0
If you want to use multiple rows, you need to add to the row number not the column:
Code:
        .Cells(NextRw, "A").Value = Me.txtAssignmentName.Value 'this would go in A6
        .Cells(NextRw + 1, "A").Value = Me.txtDate                 'this would go in A7
        .Cells(NextRw + 2, "A").Value = Me.txtAssignmentType.Value 'this would go in A8
and similarly for other columns/controls.
 
Upvote 0
Thank you RoryA that is working great think I got the skip a line part figured out, would you happen to know how to make this have a startRow of 6? here is what I have so far

Code:
Private Sub cmdSubmit_Click()
Application.ScreenUpdating = False
    Dim NextRw As Long
    Dim ws As Worksheet
    Dim startRow As Long
  
    
    Set ws = Sheet1
    NextRw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
    startRow = 6
     'Add data to worksheet
    ws.Cells(NextRw + 0, "A") = Me.txtAssignmentName.Value
    ws.Cells(NextRw + 1, "A") = Me.txtDate.Value
    ws.Cells(NextRw + 2, "A") = Me.txtAssignmentType.Value
        If (Me.txtPointsReceived.Value & "X" = "X") Then
        ws.Cells(NextRw + 0, "D").Value = "-"
        Else
        ws.Cells(NextRw + 0, "D").Value = Me.txtPointsReceived.Value
        End If
    ws.Cells(NextRw + 0, "E") = Me.txtPointsPossible.Value
 
 Unload Me
End Sub
 
Upvote 0
I wouldn't bother with startRow, just use:
Code:
NextRw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
    If NextRw < 6 then NextRw = 6
 
Upvote 0

Forum statistics

Threads
1,224,395
Messages
6,178,355
Members
452,841
Latest member
GenAkaman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top