Navigate and Edit Dats in Userform

ir121973

Active Member
Joined
Feb 9, 2008
Messages
371
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,881
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top