Facing two issues with CmdNext and CmdInsert buttons respectively

[NimishK]

I have two issues with CmdNext and CmdInsert buttons respectively

1. going at the last record or last used row. After pressing Next_button with display of lastusedrow i.e after pressing Next after the Last record or lastrowused
A) it goes to First row. How can i prevent it ?
B) and also it should not got to next row after the last row

2. When Inserting a blank row at any row. I am not able to further see records when pressing next button
for eg at row 6 i insert a blank row and then i press next button able to see 7th row details but not able to see records of 8, 9, 10 records.
Why is so that this is preventing to move further?

will appreciate your help by mentioning respective issue nos with its sub(s) too.

Private Sub UserForm_Initialize()
Dim lastUsedRow As Long
curRec = 1
curRow = 2
End Sub

Private Sub CmdNext_Click()
Dim idWs As Worksheet

With Worksheets(Sheet1)
     LastUsedRow = .Cells(Rows.Count, 1).End(xlUp).Row
        If curRec < LastUsedRow Then
           curRec = curRec + 1
           curRow = curRow + 1
           uf1.txtData1.Text =  .Cells(curRow, 1).Value  
          uf1.txtData2.Text = .Cells(curRow, 2).Value 
       End if
End With
End Sub

Private Sub cmdInsert_Click()
Dim idWs As Worksheet

   With Worksheets(Sheet1)
           Rows(curRow).Select
           Selection.EntireRow.Insert
   End With
End Sub

Thanks
NimishK

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Dim lastUsedRow As Long, CurRow As Long
Dim idWs As Worksheet


Private Sub UserForm_Initialize()
    Set idWs = ThisWorkbook.Worksheets("Sheet1")
    lastUsedRow = idWs.Cells(idWs.Rows.Count, 1).End(xlUp).Row
    CurRow = 2
End Sub


Private Sub CmdNext_Click()
    If CurRow < lastUsedRow Then
        CurRow = CurRow + 1
        Me.txtData1.Text = idWs.Cells(CurRow, 1).Value
        Me.txtData2.Text = idWs.Cells(CurRow, 2).Value
    End If
End Sub


Private Sub cmdInsert_Click()
    idWs.Rows(CurRow).EntireRow.Insert
    CurRow = CurRow + 1: lastUsedRow = lastUsedRow + 1
End Sub

note the variables above - these must sit at the TOP of your forms code page OUTSIDE any procedure

Hope helpful

Dave

 

[NimishK]

note the variables above - these must sit at the TOP of your forms code page OUTSIDE any procedure

Yes Extremely helpful. Thankx . Cleared the mess. Also declared the same with Public in module with Option Explicit

Still Issue nos 1A , 1B and 2 are recurring. in Issue 2 after inserting even next record not seen FYI and Next button seems not functioning

 

[dmt32]

Yes Extremely helpful. Thankx . Cleared the mess. Also declared the same with Public in module with Option Explicit

Hi,
you do not need to declare the same variables twice in your project - If any are repeated as Public in a standard module then these should be deleted.

Still Issue nos 1A , 1B and 2 are recurring. in Issue 2 after inserting even next record not seen FYI and Next button seems not functioning

I have made a test file & using the published update of the code - pressing Next Button navigates all records until last one is reached & then stops which I take it is what you wanted?

Dave

 

[NimishK]

Hi,
you do not need to declare the same variables twice in your project - If any are repeated as Public in a standard module then these should be deleted.

FYI have declared only once in the public module

I have made a test file & using the published update of the code - pressing Next Button navigates all records until last one is reached & then stops which I take it is what you wanted?

So above you will help to clear Issues 1A and 1B. Waiting to see your Published update of code. with this update you shall also resolve Issue 2

 

[dmt32]

FYI have declared only once in the public module

So above you will help to clear Issues 1A and 1B. Waiting to see your Published update of code. with this update you shall also resolve Issue 2

Update I was referring to is the code I posted in #Post 2. Providing you are using it as published, it should do what you want.

Dave

 

[NimishK]

Ok isuues 1A and 1B cleared. small error from my end i stated as
if curRec < LastUsedRow Then instead of yours if curRow < LastUsed row. Thanks very much.

But issue no 2 somehow not working. After inserting a row Blank. Next Button command seems Hanged.

 

[dmt32]

But issue no 2 somehow not working. After inserting a row Blank. Next Button command seems Hanged.

the updated version I posted in #Post 2 works ok for me - To try and understand why it's not working for you Post ALL the code behind your form here.


Dave

 

[NimishK]

Dave
Posted all the code. Requesting to check at your end

Option Explicit

Dim lastUsedRow As Long, CurRow As Long, curRec As Integer, myRanges As Range
Dim idWs As Worksheet, myArray As Range, srchRange As Range, fndRow As Range

Private Sub UserForm_Initialize()
    Set idWs = ThisWorkbook.Worksheets("Sheet1")
    lastUsedRow = idWs.Cells(idWs.Rows.Count, 1).End(xlUp).Row
    CurRow = 2
    Me.ComboSrch.Text = idWs.Cells(CurRow, 1).Value
    Me.txtData1.Text = idWs.Cells(CurRow, 1).Value
    Me.txtData2.Text = idWs.Cells(CurRow, 2).Value
    idWs.Rows(CurRow).Select
    
    idWs.Activate
    Me.ComboSrch.List() = idWs.Range(Cells(2, 1), Cells(lastUsedRow, 2)).Value
End Sub

Private Sub CmdNext_Click()
    If CurRow < lastUsedRow Then
        CurRow = CurRow + 1
        Me.txtData1.Text = idWs.Cells(CurRow, 1).Value
        Me.txtData2.Text = idWs.Cells(CurRow, 2).Value
        Me.ComboSrch.Text = idWs.Cells(CurRow, 1).Value
        idWs.Rows(CurRow).Select
        lastUsedRow = idWs.Cells(idWs.Rows.Count, 1).End(xlUp).Row

    End If
End Sub

Private Sub cmdInsert_Click()
    idWs.Rows(CurRow).EntireRow.Insert
    CurRow = CurRow + 1: lastUsedRow = lastUsedRow + 1
        Me.txtData1.Text = ""
        Me.txtData2.Text = ""
        Me.ComboSrch.Text = ""

End Sub

Private Sub ComboSrch_Change()
Set myRanges = Sheets("Sheet1").Range("a2:B10")

Me.txtData1.Text = Me.ComboSrch.Text

Dim idx As Long
      idx = Me.ComboSrch.ListIndex

          If idx <> -1 Then
          
              Me.txtData1.Text = idWs.Range("A" & idx + 2).Value
              Me.txtData2.Text = idWs.Range("B" & idx + 2).Value
              Me.ComboSrch.Text = idWs.Range("A" & idx + 2).Value

         End If
Set srchRange = idWs.Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp))
Set fndRow = srchRange.Find(Me.ComboSrch.Text, LookIn:=xlValues, lookat:=xlWhole)

 If Not fndRow Is Nothing Then
      idWs.Rows(fndRow.Row).Select  'hilite the findrow
      CurRow = Val(fndRow.Row)
      curRec = CurRow - 1
 End If
End Sub

 

[dmt32]

Hi,
It would have been helpful if you had published all the code from outset -

The problem you have is with Combobox change event code. This is triggered every time your other controls (cmdNext, cmdInsert) make a change to the combobox. When it's event is called, the code changes the val of CurRow variable (again) which becomes a bigger problem when you insert a row as you code has line CurRow = CurRow -1 which may explain why next button no longer functions.

As combobox control performs the same function as your cmdNext control you need to disable it's change event to prevent the CurRow being incorrectly updated.

I don't have lot of time today but try replacing ALL your forms codes with following:

Option Explicit


Dim lastUsedRow As Long, CurRow As Long
Dim idWs As Worksheet


Const StartRow As Long = 2


Private Sub ComboSrch_Change()
    If Val(Me.Tag) = xlOn Then CurRow = Me.ComboSrch.ListIndex + StartRow: GetRecord CurRow
End Sub


Private Sub UserForm_Initialize()
    Set idWs = ThisWorkbook.Worksheets("Sheet1")
    lastUsedRow = idWs.Cells(idWs.Rows.Count, 1).End(xlUp).Row
    CurRow = StartRow
    Me.ComboSrch.List() = idWs.Range(Cells(2, 1), idWs.Cells(lastUsedRow, 2)).Value
    GetRecord CurRow
End Sub


Private Sub CmdNext_Click()
    If CurRow < lastUsedRow Then
        CurRow = CurRow + 1
        GetRecord CurRow
    End If
End Sub


Private Sub cmdInsert_Click()
    idWs.Rows(CurRow).EntireRow.Insert
    lastUsedRow = lastUsedRow + 1
    Me.ComboSrch.List() = idWs.Range(idWs.Cells(2, 1), idWs.Cells(lastUsedRow, 2)).Value
    GetRecord CurRow
End Sub


Sub GetRecord(ByVal Row As Long)
    Me.Tag = xlOff
    idWs.Activate
    Me.ComboSrch.Text = Cells(Row, 1).Value
    Me.txtData1.Text = Cells(Row, 1).Value
    Me.txtData2.Text = Cells(Row, 2).Value
    Rows(Row).Select
    Me.Tag = xlOn
End Sub

Dave