Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel form

  Asked By: Bakir    Date: Jan 10    Category: MS Office    Views: 1155
  

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....

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Edwin Chavez     Answered On: Jan 10

Sounds like you need to clear out the data  on the form  after the
row has been written. Take a look at this...

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

'Everything was good, reset the form

Me.strProtocolID = ""
Me.txtSiteID.Text = ""
Me.txtPIFirstName = ""
Me.txtPILastName = ""
Me.txtPIPhoneNumber = ""
Me.txtPIFaxNumber = ""
txtPIEmailAddress = ""
txtMaxSiteScreenAmount = ""
txtMaxSiteRandAmount = ""



Else
MsgBox "Invalid row  number"

End If

 
Answer #2    Answered By: Burk Martin     Answered On: Jan 10

I added this but I am still getting
the same issue....If I add  a record  and the current spreadsheet  has 5
rows of data  the first time I click  the button form  clears of data
because the record number changes to row  6. After I save  the data
the form shows the data I just save... If I then select the add
another record cmd button  again, the form clears the data I added to
row 6 from the form, but the rownumber (I use this to determine what
record (row) I am on) stays at row 6.

I think the issue  is centering around the lastrow variable I have...
this variable is used to determine the lastrow and as part of the cmd
button to add a new record, it executes "RowNumber.Text = FormatNumber
(LastRow, 0)". When I save the data I just input to the newly
displayed record, lastrow is still pointing to row 6... how do I get
it to now recognize the lastrow has changed?

 
Answer #3    Answered By: Hubert Taylor     Answered On: Jan 10

Lets try this, don't call your FindLastRow function, at the top of
the code for the button  that adds  a new record  use this instead.

Dim R as long
R = ActiveSheet.Range("A65536").end(xlup).row + 1

 
Answer #4    Answered By: Lurlene Fischer     Answered On: Jan 10

Well I updated the code and it worked but I got an error that the row
was invalid... I worked it through though and now the form  allows me
to go from the first to the last record, forward or back, add  another
record and everything else!!!!

 
Didn't find what you were looking for? Find more on Excel form Or get search suggestion and latest updates.




Tagged: