I am new to VBA and am trying to create a form that captures data...
I have set up the form such that data on the form is separate from
the data in the spreadsheet until I save it.... the issue I am
having is that the command button I have that adds a new row of data
can only be used once...
I do this by another command button
Private Sub cmdAddNewRecord_Click()
RowNumber.Text = FormatNumber(LastRow, 0)
End Sub
Once I have entered the data, I click the cmdSave button
Private Sub cmdSave_Click()
PutData
End Sub
It calls the PutData sub that adds the data to the spreadsheet
Private Sub PutData()
Dim r As Long
If IsNumeric(RowNumber.Text) Then
r = CLng(RowNumber.Text)
Else
MsgBox "Illegal row number"
Exit Sub
End If
If r > 1 And r <= LastRow Then
Cells(r, 1) = strProtocolID
Cells(r, 2) = txtSiteID.Text
Cells(r, 3) = StrConv(txtPIFirstName, vbProperCase)
Cells(r, 4) = StrConv(txtPILastName, vbProperCase)
Cells(r, 5) = txtPIPhoneNumber
Cells(r, 6) = txtPIFaxNumber
Cells(r, 7) = txtPIEmailAddress
Cells(r, 8) = txtMaxSiteScreenAmount
Cells(r, 9) = txtMaxSiteRandAmount
DisableSave
Else
MsgBox "Invalid row number"
End If
End Sub
It determines the lastrow from a function
Private Function FindLastRow()
Dim r As Long
r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) > 0
r = r + 1
Loop
FindLastRow = r
End Function
All this works fine...
but if I try to add a second record (by clicking the addnewrecord
again), the form continues to display the same data I just saved and
does not progress to the next row....