Next Record VBA Code Produces Error

mcarsonsr
mcarsonsr used Ask the Experts™
on
On the attached, the cmdbNextRecord Sub produces and error and I am not sure why.   Thank you for your assistance.
Asset-Register---8-14-15.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
GrahamSkanRetired
Top Expert 2012

Commented:
You have declared rData as a Range type, but it is uninstantiated -  i.e. you haven't set it to any particular range. 

Option Explicit
Dim ws As Worksheet
Dim rData As Range
Dim lRw As Long

Private Sub cmdbNextRecord_Click()
 If lRw = rData.Rows.Count - 1 Then
        MsgBox "You have selected the last record", vbCritical, "Cancel"
        Exit Sub
    Else: lRw = lRw + 1
        LoadBoxes
    End If
End Sub

Open in new window

GrahamSkanRetired
Top Expert 2012

Commented:
Neither of us has listed the error which is:

Run-time error '91':
Object variable or With block not set
Roy CoxGroup Finance Manager

Commented:
See the Initialize event of the UserForm

I provided you with the code to use to navigate records in your earlier post

Previous post
Asset-Register---8-14-15.xlsm
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
mcarsonsr

Author

Commented:
Hi,

Thank you.  I have tried to define the range, but I still receive the run time error shown in the attached screen print.  I am very new to VBA.  Please advise and thank you.  The Excel workbook is attached.

Thank you!
Asset-Register---8-14-15.xlsm
CodeError.docx
Retired
Top Expert 2012
Commented:
You have added a procedure to define the values of the lRw Long variable and the rData Range: 
Private Sub setinitialrange()
Set rData = ActiveSheet.Range("A4").CurrentRegion
lRw = rData.Cells(rData.Rows.Count).End(xlUp).Row
End Sub

Open in new window

But there is no code to call the procedure.
Try calling it at the end of the form initialise procedure:
...
'        .cboClass.Value = rdata.Cells(lRw, 8).Value
'        .txtCustodian.Value = rdata.Cells(lRow, 9).Value
'        .txtYears.Value = rdata.Cells(lRw, 10).Value
'        .cboProtect.Value = rdata.Cells(lRw, 11).Value
'    End With

setinitialrange 
End Sub

Open in new window

mcarsonsr

Author

Commented:
Thank you.  That helped!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial