WHY IS THIS CODE IS RANDOMLY POSTING CHANGES TO THE WRONG CELL ROW IN THE SHEET FROM THE USERFORM ?

[chazrab]

This code does not work right every time.. Sometimes notes and/or changes in Textbox2 of Userform1 are posted to the correct Sheet row cell ,and sometimes the notes posts at the very top in row 2 when the Save button is clicked. I cannot figure out why its doing this. It should post changes to the correct cell row and column every time all the time.

Does it have to do with the code being able to identify the correct row number in the sheet cell that is being displayed in the userform that the user is making a note in the textbox in.? I did not put this lastrow code line in the code block below because I didn’t think it was necessary – thinking, what does the lastrow have to do with finding the current row?

lastrow = Sheets("BIBLETEXT").Cells(rows.count, 1).End(xlUp).Row

This is the simple one line of code below that's supposed to save Textbox2 note to the correct cell row in column C

Private Sub cmdSAVE_Click()
Sheets("BIBLETEXT").Cells(rowno, 3) = TextBox2

I did look for solutions and all that keeps coming up from Stack Overflow and ChatGPT (which gets its solutions from ChatGPT most of the time)
and all I get are long lines of code that use a For Loop . I tend to stay away from a For loop if I can, but I'm not opposed to it if that solves this problem.

I put images in the sheet and where this line of code takes the note randomly. All this is done from a button click.
Any help and guidance on this - which seems very simple to fix - will be very much appreciated.

Thanks, cr

 

[mumps]

How do you arrive at the value of the variable "rowno"?

 

[dreid1011]

Is there more to the code than that single line? You're using a variable that we can't see how/when it is declared or given a value. Please post the entire code.

 

[offthelip]

You haven't shown us much of your code, from what you have posted it would appear that rowno is undefined. How is it defined and where is it set to a value? what is the scope of the variable? you might find it useful to read tthis MS article:
Understanding scope and visibility (VBA)

 

[chazrab]

Sorry everyone - the rowno value comes from the change event in Listbox1. Yes. I have a Listbox on the userform which is populated by
a FIND code block. I should have mentioned this earlier. Here is the code for the Change Event for
Listbox1 with an image of the userform and a Listbox selected item displayed
Listbox1 Change Event:

Private Sub ListBox1_Change()
Dim n As Long
n = ListBox1.ListIndex
Verse = ListBox1.Value
TextBox1 = ListBox1.List(n, 1)
TextBox2 = ListBox1.List(n, 2)
rowno = n + 1
'currentrow = currentrow + 1  'currentrow is just a global variable.  
End Sub

rowno is assigned the value of 1 in the Initialize event:

Private Sub UserForm_Initialize()
Dim lastrow, currentrow As Long
ListBox1.ListIndex = 0
lastrow = Sheets("BIBLETEXT").Cells(rows.count, 1).End(xlUp).Row
currentrow = 1
Verse = Sheets("BIBLETEXT").Cells(currentrow, 1)
TextBox1 = Sheets("BIBLETEXT").Cells(currentrow, 2)
TextBox2 = Sheets("BIBLETEXT").Cells(currentrow, 3)
rowno = 1
totrows = lastrow
End Sub

I do see that I did not define rowno in this code block, nor as a global variable.

When the form runs, 1 is in the textbox named rowno.

Image below:

Thanks for all your help.
cr

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

 

[offthelip]

It would appear that my guess as to what your problem is correct, you need to define rowno as global variable since you are using it in two different subs in two different modules.