Solved: Get last cell in Worksheet

[dcraker]

Hello VBAExpress folks.
Hope the title is descriptive enough. I need
I have almost got it done and now I am working on a "Previous" button on the Userform. I have my "First", "Next" and "Last" button working.

"Previous" works only after I have selected either "First" or "Last", then I don't have a problem. Except after I enter new data on the userform, and it refreshes to the next new number that will be for the following row.

What I would like for it to do is, go back one number from what is on the userform. It is also set up to detect if there is an empty row or header, to disable the previous button. Unless user moves forward or goes to the last entry. I had been able to force it to go back two numbers and then forward it one, to get the same result(not desired solution). I have been struggling with these buttons by looking at the help files over time, but now I only have one problem instead of many.
It gives me an error "Object variable or With block varible not set"[vba]Private Sub CmdBtn6_Click() 'Previous button 'txtRow = TxtBx17
'this below line is at the top of the module,
'but put here to show that i have it for example
Dim ws As Worksheet
Dim Clp As Range
Set ws = Worksheets("DataBase")
Set LastCl = ws.Range("a65536").End(xlUp)
'FAILS ON THIS LINE BELOW, when moused over, it says "Clp = Nothing"
Set Clp = ws.Cells.Find(TxtBx17.Value).Offset(-1, 0) - 1
If Clp.Value = "" Or "Quote #" Then
With Me
.CmdBtn5.Enabled = False
.CmdBtn6.Enabled = False
End With
Exit Sub
Else
With Me
.CmdBtn5.Enabled = True 'Go to first entry on database
.CmdBtn6.Enabled = True 'Go to Previous
.CmdBtn7.Enabled = True 'Go to Next
.CmdBtn8.Enabled = True 'Go to Last
.CmdBtn28.Enabled = False 'Clear Form
.CmdBtn28.Visible = False
.CmdBtn4.Enabled = True 'Delete Row from database, hidden under clear button
.CmdBtn4.Visible = True
.CmdBtn3.Enabled = False 'Save Data to WS, hidden under amend button
.CmdBtn3.Visible = False
.CmdBtn27.Enabled = True 'Amend existing information
.CmdBtn27.Visible = True
.TxtBx17.Value = Clp.Value
.TxtBx18.Value = Clp.Offset(0, 1).Value
.TxtBx19.Value = Clp.Offset(0, 2).Value
.TxtBx20.Value = Clp.Offset(0, 3).Value
End With
End If
End Sub[/vba]

The code that retrieves the next available number, but it is not in the worksheet until it has been saved from Userform is:[vba]Private Sub UserForm_Activate()
'ozgrid.com/forum/showthread.php?t=72674 (info from here)

'this below line is at the top of the module,
'but put here to show that i have it for example
Dim ws As Worksheet
Me.TxtBx20.Value = Format(Date, "mm/dd/yy") 'populate today's date, w/option to change
Me.TxtBx17.Enabled = False
Dim iRow As Long
Set ws = Worksheets("DataBase")
'finds last data row from database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
If ws.[a2].Value = "" Then
Me.TxtBx17.Value = "0001"
Else
Me.TxtBx17.Value = ws.Cells(iRow, 1).Value + 1
End If
TxtBx18.SetFocus
End Sub[/vba]All help will be most appreciated and thanked. I could really use it.

[Bob Phillips]

A workbook would help as always.

[dcraker]

Please, when you open this that you or anyone laugh for longer than 10 minutes.
Most of this is copied, then modified code. It is self taught for a year and a half.
If there is any other information needed, please let me know.

Thanks for taking the time to look at it. I haven't seen how big the actual file is because of the userform and codes. So I cut out the vast amount, and still got the same results.

[Bob Phillips]

Why don't you disable the Previous button initilaise as you do with the Next button?

[dcraker]

Why don't you disable the Previous button initilaise as you do with the Next button?

I have thought about that, but I want the user to be able to 'go back' one if they need to modify it or look at it for reference, if they hit the save to quick.

[dcraker]

Also, if you were just perusing the data through the userform, if you reach the last entry, then try to use Previous, it will also lock up.

Thanks for taking the time to look. I am completely baffled on it.

correction: this happens when I have the Quote # as part of the condition to not look at. Sorry about that, but I guess I do want that included too

[Bob Phillips]

I am only talking about when you startup. You either disable previous, or you select a record and set the buttons accordingly. When you haven't selected anything previous is meaningless, just as Next is (which is disabled on startup).

Also, I don't understand that last comment.

[dcraker]

Thanks for taking the time to discuss options.

I understand it from the Startup point, but as they enter the data, there may be a time that they would just like to go back to the previous entry and update existing data.
Should I have it loading the last entry, and then have them select the New button? I am trying to make it where all they have to do is, open it up and start typing away. And if necessary they can look at the most recent record.

As for the last comment, lets say that you happen to use "Last" button. Then you use the "Previous" button, as you get to the actual first entry, then you select it again, it will then populate the userform with all the Headers names in the box before disabling the previous.
I hope that clears it up. Is there something that I am not asking, because I can't figure out any other way to say this.

xld, thanks for your patience with me, and any others that may be reading this thread.

[Bob Phillips]

Yeah that is good, so you would just enable Previous as necessary. You would have Previous disabled when nothing is selected and when the first is selected, similarly Next is disabled when nothing is selected and when the last is selected, all other times they are enabled.

I get the comment now, and noticed that myself, you just need to manage it.

Give me a while and I will post what I think it should do.

[Bob Phillips]

Here we are, this is exactly what I mean.

[dcraker]

It works great, I can't figure out where it prevents the headers from loading on the screen, it's minuscule at this time. I'll look some more this week.
I'll make do with having the previous button set the way that you have it now. I would still love to have it be able to just go to the previous.

Otherwise, it works great as of right now. I will release the file tomorrow, and then towards the end of my day at work, if it doesn't seem to be a problem with my higher up, then I will mark this solved.

Thanks xld for your patience and guidance. Hope this helps others as it does for me.

[dcraker]

Hi again, this is becoming a pain in the rear.

Using your revised example, if "Last" is chosen, it will bring up the last row on the other worksheet. Then, if I use only "Previous", and keep pressing it. It will populate with all fields and even bring the Headers as well. Which I want to avoid.

I changed[VBA] If Clp.Row = 3 Then
[/VBA]to[VBA] If Clp.Row < 4 Then
[/VBA]It is doing what I want of it as of right now, I am hoping this will not bug out on me later. Please let me know if this is the wrong way to go about it.

The amendmant on your sample, doesn't amend. But I can fix that myself using what I already have. There is one other thing that keeps happening, but I can't figure out what it was, and I am too trigger happy apparently working out one thing, that I keep ignoring the other. I may post the other one in a new thread, as it may be different than this.

Thanks again for you help and this wonderful forum, I will let all know tomorrow evening, where I am, how it turns out. And possibly post the sample that I have.

[dcraker]

thank you xld for your contribution to me and others in this forum. I have finally got it figured out, and managed to even have the Previous set the way I was aiming for. It may be over kill but it works. I just used another button, used the code from the "Last" and then made it visible false when selected.
I did use some of the other codes with it, and I am working on the naming convention... I am not savvy when it comes to names.
and found the problem with the sample, which I am glad you did, because it helps me troubleshoot. I am trying to learn more.
you had
[vba] .txtQuote.Value = LastCl.Value
.txtCompany.Value = LastCl.Offset(0, 1).Value
.txtCompany.Value = LastCl.Offset(0, 2).Value
.txtDateRec.Value = LastCl.Offset(0, 3).Value
[/vba]should have been[vba] .txtQuote.Value = LastCl.Value
.txtCompany.Value = LastCl.Offset(0, 1).Value
.txtDesc.Value = LastCl.Offset(0, 2).Value'corrected
.txtDateRec.Value = LastCl.Offset(0, 3).Value
[/vba]I am marking this thread solved

Thanks again

Final working sample that I was aiming for is attached. Feel free to use or discuss

[Bob Phillips]

Sorry about the error.

Here is a version without the duplicated button

[dcraker]

dang it xld

you always continue to amaze me, thanks for your latest version, it works beautiful

and as an FYI, I am glad you don't work here at my company... I'd be out of a job real quick

thank you,
David