A Bookmark to return to the same row when exiting and opening the application

[chazrab]

As astated. I've toyed painfully with this concept for years and came up with this rather clumsy code.

Private Sub cmdNEXT_Click()
Dim count As Integer
Dim n As Long
Dim lastrow As Long
n = ALLVIEWSTB.ListBox1.ListIndex
lastrow = Sheets("ALLVIEWS1").Cells(rows.count, "B").End(xlUp).Offset(-1, 0).Row
If ALLVIEWSTB.ListBox1.ListIndex = lastrow Then
     MsgBox "At last row"
    Exit Sub
Else
    n = Me.ListBox1.ListCount - 1
    Select Case Me.ListBox1.ListIndex
    Case Is < n
        Me.ListBox1.ListIndex = Me.ListBox1.ListIndex + 1
    Case Else
        Me.ListBox1.ListIndex = 0
    End Select
End If
Dim rng As Range
Dim Verse As String
Dim r As Long
Verse = Me.ListBox1.Value 'Me.TextBox3.Value
Set rng = Sheets("ALLVIEWS1").Columns("B:B").FIND(What:=Verse, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Sheets("ALLVIEWS1").Activate
ActiveSheet.Cells(1, 1).Value = "x"
'rng.Select
rng.Offset(0, -1).Value = "x"

The problem with this code is that sometimes it works and sometimes it doesn't If any code is perfect it should work right all the itme.
Briefly,, what this code does is put an "x" in column A of every row the next code button advances to. When the file is saved and the app
exited, upon reopening it, it should place the last row with a x in column A at the top of the userform textbox, when the user enters the Textbox with an
Enter Event or Mousedown Event, advancing down the row exiting, and returning and doing the same thing over and over if it works correctly
and so on.
Rows are displayed in a Textbox1 through Listbox1's Rowsource Property. The Change Event comes into play and its code is

Private Sub ListBox1_Change()
Dim n As Long
n = Me.ListBox1.ListIndex
ListBox1.ListIndex = n
rowno1 = ListBox1.ListIndex + 1
End Sub

When the user Enters the Textbox this code does things:

Private Sub TextBox1_Enter()
Dim lastrow As Long, s As String, r As Range
Dim n As Integer
lastrow = Sheets("ALLVIEWS1").Range("A" & rows.count).End(xlUp).Row
ListBox1.ListIndex = lastrow - 1
Me.Textbox1.SelStart = 0
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
Dim lastrow As Long, s As String, r As Range
Dim n As Integer
lastrow = Sheets("ALLVIEWS1").Range("A" & rows.count).End(xlUp).Row
ListBox1.ListIndex = lastrow - 1
Me.Textbox1.SelStart = 0
Me.rowno1.Visible = True
Me.totrows1.Visible = True
ALLVIEWSTB.number = "1"
Textbox1.SelStart = 0
Textbox1.SetFocus
Textbox1.CurLine = 0

I know this ia a lot of code to look at. I did not want to leave anything out. Some of this
may be redundant and unnecessary. I'm just trying to get it to work right because I've got to
do this for 4 Textboxes and 4 Listboxes.

Any help to refine this would be greatly apprecited.

cr

 

[Edgar_]

If I understand correctly, all you want is to be able to set the selected item in your listbox to the last item that was ever selected every time you open it.

If so, I would make a named range that simply stores that value. That value would change every time a selection is made in the listbox. Then, every time the listbox is loaded, it should check if there is a value in the named range, and if so, set its selection to it.

 

[chazrab]

Interesting approach and seems simple enough. As mentioned this is just a bookmark to return to the same place I left - that is, the same row.
The row number textbox is named rowno on the form. First thoughts are create a textbox named say, currrec. As the row number increments by 1 each time the next button is clicked, currrec.value = rowno. Exit, save, reopen. FIND what = currec. I may be making this more complicated than it really is, but these are my initial thoughts. Will see if this works.

 

[Edgar_]

I may be making this more complicated than it really is, but these are my initial thoughts

Yes, too much work. All you need is a reference that persists across Excel sessions. But will the file be saved or not?

If the file will definitely be saved, you can store the reference in many places:
1. A cell
2. A named range
3. A name
4. Form or control tags
5. etc.

If the file will possibly be closed without saving, then:
1. an external file
2. windows registry
3. environment variables
4. etc

You just need to be able to write the reference on listbox change and retrieve it on form initialization. Here's what it would look using a name:

Option Explicit

Private Sub ListBox1_Change()
    ThisWorkbook.Names("myReference").RefersTo = ListBox1.Value
End Sub

Private Sub UserForm_Initialize()
    'load data
    Dim cl As Range
    For Each cl In Hoja1.Range("ALLVIEWS")
        Me.ListBox1.AddItem cl.Value
    Next cl
    
    'select using myReference
    Dim myReference As String
    myReference = Mid(ThisWorkbook.Names("myReference").RefersTo, 2) - 1
    Me.ListBox1.ListIndex = myReference
End Sub

 

[Edgar_]

There was a little error in the change event, here's the correction:

Option Explicit

Private Sub ListBox1_Change()
    ThisWorkbook.Names("myReference").RefersTo = ListBox1.ListIndex
End Sub

Private Sub UserForm_Initialize()
    'load data
    Dim cl As Range
    For Each cl In Hoja1.Range("ALLVIEWS")
        Me.ListBox1.AddItem cl.Value
    Next cl
    
    'select using myReference
    Me.ListBox1.ListIndex = Mid(ThisWorkbook.Names("myReference").RefersTo, 2)
End Sub

 

[Edgar_]

Now that I remember, this line
Me.ListBox1.ListIndex = Mid(ThisWorkbook.Names("myReference").RefersTo, 2)

can be simplified to:
Me.ListBox1.ListIndex = Evaluate("myReference")

I was using Eval instead of Evaluate, I forgot Excel uses the whole word.

 

[chazrab]

This seems unbelievably easy - I feel a little stupid but I'm having a little difficulty figuring out where to put this code. Traditonally, the way I got this
to work in the past is with the Change Event ,the Enter Event and the Next button. But as stated, it's not perfect.

Private Sub ListBox1_Change()
' ThisWorkbook.Names("myReference").RefersTo = ListBox1.ListIndex

Dim n As Long
n = Me.ListBox1.ListIndex
ListBox1.ListIndex = n
Me.rowno1 = Me.ListBox1.ListIndex
TextBox5 = ListBox1.List(n, 0)
End Sub

As the button code advances to the next row, it puts an "x" in column A

Private Sub cmdNEXT_Click()
Dim count As Integer
Dim n As Long
Dim lastrow As Long
n = ALLVIEWS.ListBox1.ListIndex
lastrow = Sheets("ALLVIEWS1").Cells(rows.count, "B").End(xlUp).Offset(-1, 0).Row
Application.EnableEvents = False
Application.ScreenUpdating = False
If ALLVIEWS.ListBox1.ListIndex = lastrow Then
     MsgBox "At last row"
    Exit Sub
Else
    n = Me.ListBox1.ListCount - 1
    Select Case Me.ListBox1.ListIndex
    Case Is < n
        Me.ListBox1.ListIndex = Me.ListBox1.ListIndex + 1
    Case Else
        Me.ListBox1.ListIndex = 0
    End Select
End If
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim rng As Range
Dim Verse As String
Dim r As Long
Verse = Me.TextBox5.Value
Set rng = Sheets("ALLVIEWS1").Columns("B:B").FIND(What:=Verse, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
Sheets("ALLVIEWS1").Activate
ActiveSheet.Cells(1, 1).Value = "x"
''rng.Select
rng.Offset(0, -1).Value = "x"
Application.EnableEvents = True
Application.ScreenUpdating = True

Then the Enter Event searches down column A for the last used row an "x" is in
Finding that displays the correct value on where I "left off" . As mentioned before, this is a clumsy fix.

Private Sub TextBox1_Enter()
Dim lastrow As Long, s As String, r As Range
Dim n As Integer
lastrow = Sheets("ALLVIEWS1").Range("A" & rows.count).End(xlUp).Row
ListBox1.ListIndex = lastrow - 1
Me.TextBox1.SelStart = 0
End Sub

The few lines of code you have seem much simpler and efficient. I your code but for some reason it did not work.
I followed your guidelines and placed both blocks of code in the Change Event and Initialize Event like this:

Private Sub ListBox1_Change()
ThisWorkbook.Names("myReference").RefersTo = ListBox1.ListIndex
End Sub

Private Sub UserForm_Initialize()
'load data
Dim cl As Range
For Each cl In Hoja1.Range("ALLVIEWS")
Me.ListBox1.AddItem cl.Value
Next cl
'select using myReference
Me.ListBox1.ListIndex = Evaluate("myReference")
End Sub

So what could I be doing wrong ? To help illustrate what needs to happen, see images below:
All I'm doing is telling Excel to mark the last used row some way, save it, exit out, reopen it, and when Textbox1 is
entered, that last row appears as the first row at the top of the form as the form image below shows.
It "sort of" works, but there should be an easier way to bookmark a last used row. This needs to be done for for Textboxes on one form
as 3rd image shows, so code is the same except for reference changes

Thanks for all your help, Edgar
cr







Like Quote Reply
Report

 

[chazrab]

Typing too fast...

..i ["tried]" your code... - correction 1

This needs to be done for for Textboxes - for[four] textboxes... correction 2

 

[Edgar_]

Do you have a file I can work with?

My snippets don't work for you because they represent another setup. My setup is just a listobject table, I'm populating my listbox from that listobject. I see that you did not make any adaptations to my code, so that's why it's failing for you. The principle is the same: whenever a change is made to your listbox, it should store the selection somewhere. Then, after reopening the listbox, the selection that you stored will be used to go back where you left off.

But I don't know how your setup is working. The screenshots are not clear enough.