Excel Userform and record problem

[EliseFreedman]

Hi there

I have a spreadsheet which has a userform to navigate through the records.

The userform has next and previous record buttons on it. When i test it, it navigates through the records but then the first record in the spreadsheet is getting overwritten with the last record that i have opened in the form. Im not sure why this is happening?

Can anyone help

my code is below

Any help would be greatfully appreciated

Option Explicit
Public cancelled As Boolean
Dim RgData As Range
Dim VaData As Variant


Private Sub CommandButton1_Click()
Call SaveRecord
Unload Me
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub CommandButton3_Click()
With Range("Database")
If RgData.Row < .Rows(.Rows.Count).Row Then
sbNavigator.Value = sbNavigator.Value + 1
End If
End With

End Sub

Private Sub CommandButton4_Click()
If RgData.Row > Range("Database").Rows(2).Row Then
sbNavigator.Value = sbNavigator.Value - 1
End If
End Sub

Private Sub CommandButton5_Click()
Dim irowcount As Integer
With Range("database")
irowcount = .Rows.Count + 1
.Resize(irowcount).Name = "database"
sbNavigator.Max = irowcount
sbNavigator.Value = irowcount
End With



End Sub

Private Sub CommandButton6_Click()
Me.Hide
  Sheets("Data Filter").Select


End Sub

Private Sub MultiPage1_Change()

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub RecordNum_Change()

End Sub

Private Sub sbNavigator_Change()
Call SaveRecord
Set RgData = Range("Database").Rows(sbNavigator.Value)
Call LoadRecord

End Sub
Private Sub LoadRecord()

VaData = RgData.Value
txtSite.Value = VaData(1, 1)
txtRA.Value = VaData(1, 2)
txtOp.Value = VaData(1, 4)
txtLoc.Value = VaData(1, 5)
txtAreaCode.Value = VaData(1, 6)
txtResp.Value = VaData(1, 7)
txtStatus.Value = VaData(1, 15)
TxtExpire.Value = VaData(1, 20)
TxtReview.Value = VaData(1, 26)
txtIssue.Value = VaData(1, 19)
txtTLS.Value = VaData(1, 23)
TxtRating.Value = VaData(1, 25)
TxtOperations.Value = VaData(1, 27)
txtReviewer.Value = VaData(1, 7)
txtCurrent.Value = VaData(1, 19)
TxtExpireDate.Value = VaData(1, 20)
TxtReviewDate.Value = VaData(1, 26)
TxtTLS2.Value = VaData(1, 23)
txtReviewStatus.Value = VaData(1, 15)
TxtRecordCount.Value = VaData(1, 29)

End Sub
Private Sub SaveRecord()

 VaData(1, 1) = txtSite
VaData(1, 2) = txtRA
VaData(1, 4) = txtOp
 VaData(1, 5) = txtLoc
VaData(1, 6) = txtAreaCode
VaData(1, 7) = txtResp
VaData(1, 15) = txtStatus
VaData(1, 20) = TxtExpire
VaData(1, 26) = TxtReview
VaData(1, 19) = txtIssue
VaData(1, 23) = txtTLS
VaData(1, 25) = TxtRating
VaData(1, 27) = TxtOperations
VaData(1, 7) = txtReviewer
VaData(1, 19) = txtCurrent
VaData(1, 20) = TxtExpireDate
 VaData(1, 26) = TxtReviewDate
VaData(1, 23) = TxtTLS2
 VaData(1, 15) = txtReviewStatus
VaData(1, 29) = TxtRecordCount




RgData.Value = VaData
End Sub

Private Sub TxtExpire_Change()

End Sub

Private Sub TxtExpireDate_Change()

End Sub

Private Sub TxtRecordCount_Change()

End Sub

Private Sub txtReviewer_Change()

End Sub

Private Sub txtSite_Change()

End Sub

Private Sub txtStatus_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
With Range("database")
Set RgData = .Rows(2)
Call LoadRecord
sbNavigator.Value = 2
sbNavigator.Max = .Rows.Count
End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "Please use only the OK or cancel buttons", vbCritical
Cancel = True
End If

End Sub

 

[Bong]

It looks like the row/column numbers are hard-coded to Row-1.

_________________
Bob Rashkin

 

[kjv1611]

I'm not sure, but just guessing here, I think this is your problem:

 VaData([HIGHLIGHT]1[/HIGHLIGHT], 1) = txtSite
VaData([HIGHLIGHT]1[/HIGHLIGHT], 2) = txtRA
VaData([HIGHLIGHT]1[/HIGHLIGHT], 4) = txtOp
 VaData([HIGHLIGHT]1[/HIGHLIGHT], 5) = txtLoc
VaData([HIGHLIGHT]1[/HIGHLIGHT], 6) = txtAreaCode
VaData([HIGHLIGHT]1[/HIGHLIGHT], 7) = txtResp
VaData([HIGHLIGHT]1[/HIGHLIGHT], 15) = txtStatus
VaData([HIGHLIGHT]1[/HIGHLIGHT], 20) = TxtExpire
VaData([HIGHLIGHT]1[/HIGHLIGHT], 26) = TxtReview
VaData([HIGHLIGHT]1[/HIGHLIGHT], 19) = txtIssue
VaData([HIGHLIGHT]1[/HIGHLIGHT], 23) = txtTLS
VaData([HIGHLIGHT]1[/HIGHLIGHT], 25) = TxtRating
VaData([HIGHLIGHT]1[/HIGHLIGHT], 27) = TxtOperations
VaData([HIGHLIGHT]1[/HIGHLIGHT], 7) = txtReviewer
VaData([HIGHLIGHT]1[/HIGHLIGHT], 19) = txtCurrent
VaData([HIGHLIGHT]1[/HIGHLIGHT], 20) = TxtExpireDate
 VaData([HIGHLIGHT]1[/HIGHLIGHT], 26) = TxtReviewDate
VaData([HIGHLIGHT]1[/HIGHLIGHT], 23) = TxtTLS2
 VaData([HIGHLIGHT]1[/HIGHLIGHT], 15) = txtReviewStatus
VaData([HIGHLIGHT]1[/HIGHLIGHT], 29) = TxtRecordCount

It looks like you are updating the first row/record every time you run this particular procedure. So, it wouldn't matter what data is put into the form, as it's always going to stick that data into Row 1.

You need to use a variable for selecting different rows. That variable would be set by something on your form, I would suppose.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Goodness! I knew I should've refreshed before posting! I was sitting here for a minute, making sure my post looked right!

--

"If to err is human, then I must be some kind of human!" -Me

 

[EliseFreedman]

I have solved the problem

Each field on the form was tied to a control source (being the relevant column of the first row in the field). The minute i removed that, the form works

 

[kjv1611]

Glad to hear you got it sorted out.

--

"If to err is human, then I must be some kind of human!" -Me