View 1st Record through User Form Automatically

Originally Posted by royUK

Need to see the code, but it's not a good idea to use two rows for the header

This is the complete code for the User Form.

Dim Data As Worksheet

Private Sub Add_Record_Click()
    ClearForm
    Frame5.Visible = True
    Frame4.Visible = False
    Add_Record.Enabled = False
    CommandButton1.Enabled = False
End Sub

Private Sub CheckBox1_Click()

End Sub

Private Sub CmdCancel_Click()
    ClearForm
    Frame4.Visible = True
    Frame5.Visible = False
    Add_Record.Enabled = True
    CommandButton1.Enabled = True
End Sub

Private Sub CmdClear_Click()
    ClearForm
End Sub

Private Sub CmdEnter_Click()
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    SaveData LastRow + 1
    ClearForm
End Sub

Private Sub ComboBox14_Change()

End Sub

Private Sub CommandButton1_Click()
    SaveData Val(RowNumber.Value)
End Sub

Private Sub First_Click()
    RowNumber.Value = 3
End Sub

Private Sub Frame5_Click()

End Sub

Private Sub InstallDate2_Change()

End Sub

Private Sub Last_Record_Click()
    LastRow = Data.Cells(Data.Rows.Count, 1).End(xlUp).Row
    RowNumber.Value = LastRow
End Sub

Private Sub Next_Record_Click()
    LastRow = Data.Cells(Data.Rows.Count, 1).End(xlUp).Row
    If RowNumber.Value = "" Then
        RowNumber = 1
        Exit Sub
    End If
    If Val(RowNumber.Value) < LastRow Then
        RowNumber.Value = Val(RowNumber.Value) + 1
    End If
End Sub

Private Sub Payment_Click()

End Sub

Private Sub Previous_Click()
    If Val(RowNumber.Value) > 1 Then
        RowNumber.Value = Val(RowNumber.Value) - 1
    End If
End Sub

Private Sub RowNumber_Change()
    LoadData Val(RowNumber.Value)
End Sub

Private Sub Save_Record_Click()
    LastRow = Data.Cells(Data.Rows.Count, 1).End(xlUp).Row
    SaveData LastRow + 1
    ClearForm
    Frame4.Visible = True
    Frame5.Visible = False
    Add_Record.Enabled = True
    CommandButton1.Enabled = True
End Sub

Private Sub TextBox10_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Dim R As Long
        
        R = CLng(RowNumber)
        
      ' There must be a row number and the user must left click with the mouse.
        If R <> 0 And Button = 1 Then
           On Error Resume Next
              Data.Cells(R, 10).Hyperlinks(1).Follow True
              If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, 10).Value & "'."
           On Error GoTo 0
        End If

End Sub


Private Sub TextBox11_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Dim R As Long
        
        R = CLng(RowNumber)
        
      ' There must be a row number and the user must left click with the mouse.
        If R <> 0 And Button = 1 Then
           On Error Resume Next
              Data.Cells(R, 10).Hyperlinks(1).Follow True
              If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, 10).Value & "'."
           On Error GoTo 0
        End If

End Sub

Private Sub TextBox12_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Dim R As Long
        
        R = CLng(RowNumber)
        
      ' There must be a row number and the user must left click with the mouse.
        If R <> 0 And Button = 1 Then
           On Error Resume Next
              Data.Cells(R, 10).Hyperlinks(1).Follow True
              If Err <> 0 Then MsgBox "Unable to open '" & Data.Cells(R, 10).Value & "'."
           On Error GoTo 0
        End If

End Sub

Private Sub UserForm_Initialize()
    Set Data = Sheets("Data Form")
End Sub

Sub ClearForm()
    Dim ctl As MSForms.Control
    ' Clear the form
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
            ctl.Value = False
        End If
    Next ctl
End Sub

Sub LoadData(SelRow)

    With Data
        If SelRow < 1 Or SelRow > .Cells(.Rows.Count, 1).End(xlUp).Row Then
            Label13.Visible = True
            Exit Sub
        End If
        Label13.Visible = False
        For col = 1 To 12
            Controls("TextBox" & col).Value = .Cells(SelRow, col).Value
        Next col
        For col = 13 To 16
            Controls("ComboBox" & col).Value = .Cells(SelRow, col).Value
            Next col
        ComboBox13.Value = .Cells(SelRow, 13).Value
        ComboBox14.Value = .Cells(SelRow, 14).Value
        InstallDate.Value = .Cells(SelRow, 15).Value
        TextBox13.Value = .Cells(SelRow, 16).Value
        ComboBox15.Value = .Cells(SelRow, 17).Value
        ComboBox16.Value = .Cells(SelRow, 18).Value
        InstallDate2.Value = .Cells(SelRow, 19).Value
        CheckDueDate2.Value = .Cells(SelRow, 20).Value
        TextBox14.Value = .Cells(SelRow, 21).Value
            End With
End Sub

Sub SaveData(SelRow)
   'Write Data to Worksheet
    For col = 1 To 12
        Data.Cells(SelRow, col).Value = Controls("TextBox" & col).Value
    Next col
    Data.Cells(SelRow, 13).Value = ComboBox13.Value
    Data.Cells(SelRow, 14).Value = ComboBox14.Value
    Data.Cells(SelRow, 15).Value = InstallDate.Value
    Data.Cells(SelRow, 16).Value = TextBox13.Value
    Data.Cells(SelRow, 17).Value = ComboBox15.Value
    Data.Cells(SelRow, 18).Value = ComboBox16.Value
    Data.Cells(SelRow, 19).Value = InstallDate2.Value
    Data.Cells(SelRow, 20).Value = CheckDueDate2.Value
    Data.Cells(SelRow, 21).Value = TextBox14.Value
End Sub

That doesn't really help me, I am very new at all this and most of the code has been given by people off the forum.

Could someon advise what I need to do.

Thanks.

Shazz
x

Sorry about that, as I said I am very new to all this.

The Workbook is attached for you to look at.

That did the trick, thanks so much you are a star.

I have added to your Reputation.

Shazz