Creating a command button to jump between rows and editing them

[FindingMyself]

Hello, so I'm trying to create a form similar to excel's built-in dataform as such:

Is there a way to create a command button that can move to the previous and next row in the excel worksheet and be able to edit them?

 

[bertie]

Here is how I would go about this.

Create a global variable to store the row number for the data.

[color=darkblue]Public[/color] rw [color=darkblue]As[/color] [color=darkblue]Long[/color]

In the UserForm_Initialize event set the starting row and populate the textboxes.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
  rw = 2
  PopulateTextBoxes rw
  ResetButtons rw
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

The PopulateTextBoxes procedure uses the row number to get the information from the spreadsheet.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] PopulateTextBoxes([color=darkblue]ByVal[/color] rw [color=darkblue]As[/color] [color=darkblue]Long[/color])
  [color=darkblue]With[/color] Sheets("[COLOR="Red"]Sheet1[/COLOR]")
    txtDate.Value = .Range("A" & rw).Value
    txtPmType.Value = .Range("B" & rw).Value
    txtEquipment.Value = .Range("C" & rw).Value
    txtSpecification.Value = .Range("D" & rw).Value
    txtValue.Value = .Range("E" & rw).Value
  [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

You don't want the Previous button to go below your first row of data. You can disable it if the current record is the first record in your data set. You may also want to code the Next button if you have an upper limit to your data.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ResetButtons([color=darkblue]ByVal[/color] rw [color=darkblue]As[/color] [color=darkblue]Long[/color])
  [color=darkblue]If[/color] rw < 3 [color=darkblue]Then[/color]
    cmdPrevious.Enabled = [color=darkblue]False[/color]
  [color=darkblue]Else[/color]
    cmdPrevious.Enabled = [color=darkblue]True[/color]
  [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

The Next and Previous buttons would just add, or take away, one from the row count and populate the text boxes.
Notice how the Previous button checks the lower limit of the row counter by calling the ResetButtons procedure.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdNext_Click()
  rw = rw + 1
  PopulateTextBoxes rw
  cmdPrevious.Enabled = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdPrevious_Click()
  [color=darkblue]If[/color] rw > 2 [color=darkblue]Then[/color]
    rw = rw - 1
    PopulateTextBoxes rw
  [color=darkblue]End[/color] [color=darkblue]If[/color]
  ResetButtons rw
End [color=darkblue]Sub[/color]

The Update button is the reverse of the PopulateTextBoxes code.
NB There is no data validation set up.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdUpdate_Click()
  [color=darkblue]With[/color] Sheets("[COLOR="red"]Sheet1[/COLOR]")
    
[COLOR="SeaGreen"]    'data validation goes here[/COLOR]   

    .Range("A" & rw).Value = txtDate.Value
    .Range("B" & rw).Value = txtPmType.Value
    .Range("C" & rw).Value = txtEquipment.Value
    .Range("D" & rw).Value = txtSpecification.Value
    .Range("E" & rw).Value = txtValue.Value
  [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps.
Bertie

 

[FindingMyself]

Here is how I would go about this.

Create a global variable to store the row number for the data.

[color=darkblue]Public[/color] rw [color=darkblue]As[/color] [color=darkblue]Long[/color]

In the UserForm_Initialize event set the starting row and populate the textboxes.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
  rw = 2
  PopulateTextBoxes rw
  ResetButtons rw
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

The PopulateTextBoxes procedure uses the row number to get the information from the spreadsheet.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] PopulateTextBoxes([color=darkblue]ByVal[/color] rw [color=darkblue]As[/color] [color=darkblue]Long[/color])
  [color=darkblue]With[/color] Sheets("[COLOR="Red"]Sheet1[/COLOR]")
    txtDate.Value = .Range("A" & rw).Value
    txtPmType.Value = .Range("B" & rw).Value
    txtEquipment.Value = .Range("C" & rw).Value
    txtSpecification.Value = .Range("D" & rw).Value
    txtValue.Value = .Range("E" & rw).Value
  [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

You don't want the Previous button to go below your first row of data. You can disable it if the current record is the first record in your data set. You may also want to code the Next button if you have an upper limit to your data.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] ResetButtons([color=darkblue]ByVal[/color] rw [color=darkblue]As[/color] [color=darkblue]Long[/color])
  [color=darkblue]If[/color] rw < 3 [color=darkblue]Then[/color]
    cmdPrevious.Enabled = [color=darkblue]False[/color]
  [color=darkblue]Else[/color]
    cmdPrevious.Enabled = [color=darkblue]True[/color]
  [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

The Next and Previous buttons would just add, or take away, one from the row count and populate the text boxes.
Notice how the Previous button checks the lower limit of the row counter by calling the ResetButtons procedure.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdNext_Click()
  rw = rw + 1
  PopulateTextBoxes rw
  cmdPrevious.Enabled = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdPrevious_Click()
  [color=darkblue]If[/color] rw > 2 [color=darkblue]Then[/color]
    rw = rw - 1
    PopulateTextBoxes rw
  [color=darkblue]End[/color] [color=darkblue]If[/color]
  ResetButtons rw
End [color=darkblue]Sub[/color]

The Update button is the reverse of the PopulateTextBoxes code.
NB There is no data validation set up.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdUpdate_Click()
  [color=darkblue]With[/color] Sheets("[COLOR="red"]Sheet1[/COLOR]")
    
[COLOR="SeaGreen"]    'data validation goes here[/COLOR]   

    .Range("A" & rw).Value = txtDate.Value
    .Range("B" & rw).Value = txtPmType.Value
    .Range("C" & rw).Value = txtEquipment.Value
    .Range("D" & rw).Value = txtSpecification.Value
    .Range("E" & rw).Value = txtValue.Value
  [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps.
Bertie

Hi Bertie, this is perfect! Thanks!