CODEWITHSUNDEEP
excelvba
paul worthington
paul worthington

Reputation: 35

Can't Increment Cmd Next

The code below is supposed to get the next record when a button is clicked in an input sheet.

My button is named CurrRecNew on sheet3 when I click it performs the code below but it doesn't appear to increment. Any suggestions on what I am doing wrong?

The datasheet sheet 1 has cells starting in Row A3 and going down eg

A3 1 B3 a
A4 Blank B4 b
A5 Blank B5 c
A6 2 B6 d
A7 Blank B7 f
A8 Blank B8 g
A9 Blank B9 h
A8 3 B10 ...

Sub ViewLogDown()
    Dim historyWks As Worksheet
    Dim InputWks As Worksheet

    Dim lRec As Long
    Dim lRecRow As Long
    Dim lLastRec As Long
    Dim LastRow As Long
    Dim Rlen As Long
    Dim lCurrentRow As Long

    lCurrentRow = lCurrentRow + 1

    Application.EnableEvents = False

    Set InputWks = Worksheets("Sheet3")
    Set historyWks = Worksheets("Sheet1")

    With historyWks
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row - 1
        lLastRec = LastRow - 1
    End With

    With InputWks
        lCurrentRow = lCurrentRow + 1
        lRec = .Range("CurrRecNew").Value

        Do While Len(Cells(lCurrentRow, 1).Value) = 0
            lCurrentRow = lCurrentRow + 1
        Loop
        lCurrentRow = lCurrentRow - 1

        .OLEObjects("tbRiskID").Object.Value = historyWks.Cells(lCurrentRow, 1)
        .OLEObjects("tbRiskContext").Object.Value = historyWks.Cells(lCurrentRow, 2)
        .OLEObjects("TextBox34").Object.Value = historyWks.Cells(lCurrentRow, 3)
        .OLEObjects("tbRiskEx").Object.Value = historyWks.Cells(lCurrentRow, 4)
        .OLEObjects("tbRiskCat").Object.Value = historyWks.Cells(lCurrentRow, 5)
    End With
    Application.EnableEvents = True
End Sub

Upvotes: 1

Views: 169

Answers (1)

Reafidy
Reafidy

Reputation: 8471

Your code is very confusing, you are finding the lCurrentRow on the InputWks sheet but then setting the textbox objects to the lcurrentrow on the Historywks sheet??? You need to explain clearly what each worksheet does, which sheet you want to find the next row on etc.

I presume that the you are using the named range CurrRecNew to store the current row. And you are wanting get the current row on the historywrks sheet. Therefore as far as finding the next row which is your actual question your code should look something like this:

    Dim rFound As Range

    '// History sheet
    With historyWks  
        '// Get current row, you need to correctly define the sheet name which contains the CurrRecNew Range.
        lCurrentRow = InputWks.Range("CurrRecNew").Value

        Set rFound = .Columns(1).Find(What:="*", After:=.Cells(lCurrentRow, 1))

        If Not rFound Is Nothing Then
            If rFound.Row > lCurrentRow Then
                lCurrentRow = rFound.Row
                txtName.Text = Cells(lCurrentRow, 1).Value
                txtPhone.Text = Cells(lCurrentRow, 2).Value
            End If
        End If

        '// Once again correct the sheet name here I guessed CurrRecNew was on the InputWks sheet
        InputWks.Range("CurrRecNew").Value = lCurrentRow

    End with

Upvotes: 1

Related Questions