dcraker
10-07-2008, 08:33 AM
Hello VBAExpress folks.
Hope the title is descriptive enough. I need :help
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"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
The code that retrieves the next available number, but it is not in the worksheet until it has been saved from Userform is: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 SubAll help will be most appreciated and thanked. I could really use it.
Hope the title is descriptive enough. I need :help
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"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
The code that retrieves the next available number, but it is not in the worksheet until it has been saved from Userform is: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 SubAll help will be most appreciated and thanked. I could really use it.