Inserting form values into Excel spreadsheet using VBA

Question

I'm trying to insert form values into my Excel spreadsheet using vba but my current code is inserting values into the same row.

As my table starts from row 3, I want to start from there and continue by automatically shifting to the next row each time. The rows are already set and I don't want to insert new rows but overwrite the current 'empty' rows.
When 202 rows (maximum no. of rows available) have been entered then I want the spreadsheet to return an error message dialog.

How can I go about achieving this?

Current Code

    Private Sub btnSubmit_Click()

    Dim ws As Worksheet
    Set ws = Worksheets("main")

    ' Copy the data to the database
    ws.Rows("4:4").Insert Shift:=xlDown
    ws.Range("A3").Value = cbo_deptCode.Value
    MsgBox ("Booking request has been successfully made")

End Sub

brettdj · Accepted Answer · 2012-10-28 11:44:30Z

1

something like this

Private Sub btnSubmit_Click()
    Dim ws As Worksheet
    Dim rng1 As Range
    Set ws = Worksheets("main")
    Set rng1 = ws.Cells(Rows.Count, "a").End(xlUp)
    If rng1.Row > 202 Then
    MsgBox "202 Rows exceeded"  
    Else
    rng1.Offset(1, 0) = cbo_deptCode.Value
    End If
End Sub

answered Oct 28, 2012 at 11:44

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

salih0vicX · Accepted Answer · 2012-10-28 11:01:03Z

1

Please try this and let us know if you have any questions or concerns:

Sub Button1_Click()
    Dim ws As Worksheet
    Dim i As Long
    Set ws = Worksheets("main")

    ' Copy the data to the database
    i = Cells(Rows.Count, 1).End(xlUp).Row + 1 'Get last empty cell in column A
    If i > 202 Then
        MsgBox "Row 203"
        Exit Sub
    End If

    Range("A" & i).Value = cbo_deptCode.Value
    MsgBox ("Booking request has been successfully made")
End Sub

answered Oct 28, 2012 at 11:01

salih0vicX

1,3831 gold badge8 silver badges9 bronze badges

3 Comments

methuselah Over a year ago

Thanks it works really well. But I'm having the problem where it always skips row 3 for some reason. Even though it is the current first empty row on the table, it skips it and starts at row 4. Any idea how I can fix this?

brettdj Over a year ago

@methuselah The code above doesn't actually use ws. It works on the ActiveSheet.

salih0vicX Over a year ago

Sry, I was away... But I see you got your answer :)

Collectives™ on Stack Overflow

Inserting form values into Excel spreadsheet using VBA

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related