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

 

[NimishK]

Thanks Dave for the great help.
BTW how does Tag property help. Have never used them. But learnt a lot out of your coding
lastly the text boxes remain empty when you insert with some data in textboxes when pressesd next
NimishK

 

[dmt32]

Thanks Dave for the great help.
BTW how does Tag property help. Have never used them.

from the helpfile:

[FONT=&quot]The Tag property syntax has these parts:[/FONT]

[FONT=&quot]Part Description[/FONT]
[FONT=&quot]object Required. A valid object.[/FONT]
[FONT=&quot]String Optional. A string expression identifying the object. The default[/FONT]
[FONT=&quot]is a zero-length string ("").[/FONT]


[FONT=&quot]Remarks[/FONT]

[FONT=&quot]Use the Tag property to assign an identification string to an object[/FONT]
[FONT=&quot]without affecting other property settings or attributes.[/FONT]

[FONT=&quot]For example, you can use Tag to check the identity of a form or control[/FONT]
[FONT=&quot]that is passed as a variable to a procedure.

[/FONT]Basically, it's a text place holder for the control which you can use to hold information - In your projects case, I used it to store an integer (hence the Val function coerce when reading from it) - Tag property is very useful & it saves declaring a variable.

lastly the text boxes remain empty when you insert with some data in textboxes when pressesd next
NimishK

Code you shared is for navigating your worksheet - entering new data in textboxes & then pressing Next button will just move you to the next record.

Dave

 

[NimishK]

Dave very nicely explained about tag property. Thankx . Will keep in mind.
At Present
Purpose is missed Am not able to Search the Value and get the row number of searched value what happens is it goes to first row
using the following Code

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

 

[NimishK]

FYI
while typing in comboSrch and making it blank or deleting the text competely.
The record goes to 1st row. or it displays the Header Row . How could we prevent this.?

 

[dmt32]

FYI
while typing in comboSrch and making it blank or deleting the text competely.
The record goes to 1st row. or it displays the Header Row . How could we prevent this.?

to prevent selection of header row when combobox cleared add following line

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
    If Row < StartRow Then Row = StartRow
    Rows(Row).Select
    Me.Tag = xlOn
End Sub

This will default selection to StartRow.

or you can change it to remove row selection something like this maybe.

If Row >= StartRow Then Rows(Row).Select Else Cells(StartRow, 1).Activate

Dave

 

[NimishK]

If Row < StartRow Then Row = StartRow
and
If Row >= StartRow Then Rows(Row).Select Else Cells(StartRow, 1).Activate

Error in above 2 statement in GetRecord

If Row < StartRow Then Row = StartRow

Had to shift in comboSrch range but still not able to get string searched and display its searched values in comboSrch List

 

[dmt32]

Error in above 2 statement in GetRecord

Had to shift in comboSrch range but still not able to get string searched and display its searched values in comboSrch List

I type in combobox & match returns record - not sure what you are doing differently.

Re-Post ALL the code behind the form & will have a look.

Dave

 

[NimishK]

Option Explicit
Dim lastUsedRow As Long, CurRow As Long, curRec as Integer
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
        
    If Val(Me.Tag) = xlOn Then
       CurRow = Me.ComboSrch.ListIndex + StartRow
       If CurRow < StartRow Then CurRow = StartRow
       Me.txtData1.Text = idWs.Range("A" & CurRow).Value
       Me.txtData2.Text = idWs.Range("B" & CurRow).Value
       Me.ComboSrch.Text = idWs.Range("A" & CurRow).Value
       GetRecord CurRow
    End If
End Sub

Private Sub CmdNext_Click()
    If CurRow < lastUsedRow Then
        CurRow = CurRow + 1
        curRec = curRec + 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
    Me.lblSrNo.Caption = Format$(curRec)
     If row < StartRow Then row = StartRow
'''    If row >= StartRow Then Rows(row).Select Else Cells(StartRow, 1).Activate
    Rows(row).Select
    Me.Tag = xlOn

End Sub

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

Not able to Empty ComboSrch to re-enter any string. Pl check