creating a form in excel using VBA

Question

I am trying to creat a form in excel using VBA, but I am stuck at the Code. I need to find out the code to enter a data to my worksheet using VBA form . here is the code I am using, but doesn't work..

Private Sub cmdAdd_Click()

Dim LastRow As Range
Dim DPDIAdhocRequestTable As ListObject


With LastRow

    Cells(1, 2) = RequesterName.Value
    Cells(1, 3) = RequesterPhoneNumber.Value
    Cells(1, 4) = RequesterBureau.Value
    Cells(1, 5) = DateRequestMade.Value
    Cells(1, 6) = DateRequestDue.Value
    Cells(1, 7) = PurposeofRequest.Value
    Cells(1, 8) = ExpectedDataSaurce.Value
    Cells(1, 9) = Timeperiodofdatarequested.Value
    Cells(1, 10) = ReoccuringRequest.Value
    Cells(1, 11) = RequestNumber.Value
    Cells(1, 12) = AnalystAssigned.Value
    Cells(1, 13) = AnalystEstimatedDueDate.Value
    Cells(1, 14) = AnalystCompletedDate.Value
    Cells(1, 15) = SupervisiorName.Value

End With


End Sub

can you help me to figure out the correct code for enter command?

thank you so much for your help.

Cells(x, y).Value = ObjectName.Value have you tried this syntax? — Aleksandar Misich
– Aleksandar Misich, Commented Mar 31, 2017 at 13:12
You might want to correct the spelling of SupervisiorName - while not an error it would annoy the hell out of me further down the line. :) — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Mar 31, 2017 at 14:30

Darren Bartrup-Cook · Accepted Answer · 2017-03-31 14:24:46Z

As @Adam said - you've created LastRow and not assigned it to anything.
I'm guessing it's the next row you want to paste your data into, so it should be a Long holding the row number rather than an actual reference to the cell.

In the code below you could qualify the form controls by adding Me., for example Me.RequesterName.Value
https://msdn.microsoft.com/en-us/library/office/gg251792.aspx

Private Sub cmdAdd_Click()

    Dim wrkSht As Worksheet
    Dim LastRow As Long

    'The sheet you want the data to go into.
    Set wrkSht = ThisWorkbook.Worksheets("Sheet1")

    'You're after the last row number, rather than referencing the range so LastRow is a Long.
    'This looks for the last cell containing data in column A (the 1 in 'Cells(...)').
    LastRow = wrkSht.Cells(Rows.Count, 1).End(xlUp).Row + 1

    'With... End With block.  Cells is preceded by a '.' notation - indicating it's referencing the 'With wkrSht'#
    'https://msdn.microsoft.com/en-us/library/wc500chb.aspx
    With wrkSht

        'Using LastRow as row number in cell reference.
        .Cells(LastRow, 2) = RequesterName.Value

        'Before adding dates to the sheet you might want to check that the
        'user entered a date and not rubbish.
        .Cells(LastRow, 5) = DateRequestMade.Value
        'You could use CDATE to force it to a date - will try and coerce the entered text into a date.
        'Note - 1 will be changed to 01/01/1900 (so will need to add extra code to check it really is a date).
        .Cells(LastRow, 5) = CDate(DateRequestMade.Value)

    End With

End Sub

Adam Vincent · Accepted Answer · 2017-03-31 14:07:48Z

0

The first problem is that you've created a Range named LastRow but haven't assigned anything to it.

'Declaration
Dim LastRow As Range
'Assigns the last row based on the last item in Column A
Set LastRow = Range("A" & Rows.Count).End(xlUp).Row
With LastRow
    ...
End With

The second issue is a minor syntax error in your With LastRow block.

 With LastRow
      Cells(x,y).Value = "Foo"
 End With

Should be

 With LastRow
     .Cells(x,y).Value = "Foo"
 End With

Which is essentially the same as saying LastRow.Cells(x,y).Value = "Foo". Without the "." in front of Cells() VBA will not apply the With to that object, and assume you meant ActiveSheet.Cells()

edited Mar 31, 2017 at 14:07

answered Mar 31, 2017 at 13:36

Adam Vincent

3,86919 silver badges44 bronze badges

1 Comment

Tom Over a year ago

Their With statement won't have any affect on anything inside it with the way it's currently written either

Collectives™ on Stack Overflow

creating a form in excel using VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related