Hi,
I wonder whether someone may be able to help me please.
I have spreadsheet with data in columns C and D with the headers 'EN Number' and 'EN Number Notes' in row 5 and the data proceeding from row 6.
Over the past few days I've been putting togther a userform which I can use to add and edit the data from.
I'm not very good with VB, to be honest pretty poor and quite a bit out of my depth, but through bits and pieces I've found on the Internet, I've managed to put some functionality together, but unfortunately I have a few issues with it that I can't work out.
Please find below elements of the code that I'm having issues with: The first is the 'Next Record' functionality. When I click on the button the code finds the very last record then the next to last but then gets stuck in a loop only selecting the last and second to last record.
Private Sub cmdNextRecord_Click()
With Sheets("Entry").Range("C7:D30")
Debug.Print txtENNumber.Value
Set c = .Find(Me.txtENNumber.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
currow = c.Row
Debug.Print currow
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
currow = currow - 7
txtENNumber.Value = .Cells(currow, 1).Value
txtENNumberNotes.Value = .Cells(currow, 2).Value
End With
Could someone perhaps tell me please where I may be going wrong and how I could rectify this, so that the code starts at the top of the data and goes through all my records when I click the button.
The next problem I have is that I cannot get the 'Previous Record' functionality to work at all. When I click on the button, rather than the previous record in the list being shown the fields become blank. The coding for this is as follows:
Private Sub cmdPreviousRecord_Click()
With Sheets("Entry").Range("C7:D30")
Debug.Print txtENNumber.Value
Set c = .Find(Me.txtENNumber.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
currow = c.Row
Debug.Print currow
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
currow = currow - 5
txtENNumber.Value = .Cells(currow, 3).Value
txtENNumberNotes.Value = .Cells(currow, 4).Value
End With
End Sub
Again, could someone someone perhaps show me how to get this piece of the coding working please.
The last issue I have is around editing the record information. When I make a change to the data for one of the records, I receive the following error 'Run-time error '13': Type mismatch'
Private Sub cmdEdit_Click()
NotNow = False
n = Application.Match(Me.txtENNumber, Range("C:C"), 0)
n = Application.Match(Me.txtENNumberNotes, Range("D:D"), 0)
one = Me.txtENNumber.Text
two = Me.txtENNumberNotes.Text
Cells(n, 1).Value = one
Cells(n, 2).Value = two
NotNow = True
End Sub
When I go into VB Editor and hover over the relevant fields, it does show the new and correct values, so I'm not sure why the data won't change. Again, could someone perhaps show me where I'm going wrong.
Apologies for the lengthy post, but any help on the above would be so greatfully received.
Many thanks and regards
Chris
I wonder whether someone may be able to help me please.
I have spreadsheet with data in columns C and D with the headers 'EN Number' and 'EN Number Notes' in row 5 and the data proceeding from row 6.
Over the past few days I've been putting togther a userform which I can use to add and edit the data from.
I'm not very good with VB, to be honest pretty poor and quite a bit out of my depth, but through bits and pieces I've found on the Internet, I've managed to put some functionality together, but unfortunately I have a few issues with it that I can't work out.
Please find below elements of the code that I'm having issues with: The first is the 'Next Record' functionality. When I click on the button the code finds the very last record then the next to last but then gets stuck in a loop only selecting the last and second to last record.
Private Sub cmdNextRecord_Click()
With Sheets("Entry").Range("C7:D30")
Debug.Print txtENNumber.Value
Set c = .Find(Me.txtENNumber.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
currow = c.Row
Debug.Print currow
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
currow = currow - 7
txtENNumber.Value = .Cells(currow, 1).Value
txtENNumberNotes.Value = .Cells(currow, 2).Value
End With
Could someone perhaps tell me please where I may be going wrong and how I could rectify this, so that the code starts at the top of the data and goes through all my records when I click the button.
The next problem I have is that I cannot get the 'Previous Record' functionality to work at all. When I click on the button, rather than the previous record in the list being shown the fields become blank. The coding for this is as follows:
Private Sub cmdPreviousRecord_Click()
With Sheets("Entry").Range("C7:D30")
Debug.Print txtENNumber.Value
Set c = .Find(Me.txtENNumber.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
currow = c.Row
Debug.Print currow
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
currow = currow - 5
txtENNumber.Value = .Cells(currow, 3).Value
txtENNumberNotes.Value = .Cells(currow, 4).Value
End With
End Sub
Again, could someone someone perhaps show me how to get this piece of the coding working please.
The last issue I have is around editing the record information. When I make a change to the data for one of the records, I receive the following error 'Run-time error '13': Type mismatch'
Private Sub cmdEdit_Click()
NotNow = False
n = Application.Match(Me.txtENNumber, Range("C:C"), 0)
n = Application.Match(Me.txtENNumberNotes, Range("D:D"), 0)
one = Me.txtENNumber.Text
two = Me.txtENNumberNotes.Text
Cells(n, 1).Value = one
Cells(n, 2).Value = two
NotNow = True
End Sub
When I go into VB Editor and hover over the relevant fields, it does show the new and correct values, so I'm not sure why the data won't change. Again, could someone perhaps show me where I'm going wrong.
Apologies for the lengthy post, but any help on the above would be so greatfully received.
Many thanks and regards
Chris
