Excel UserForm Help

[iuianj07]

Hello Guys,

I have an excel file, and created a UserFORM object. This form has a LoanNumber Textfield, and I have an excel worksheet that has the column LoanNumber.

What I would like to do though, is on the UserFORM, the LoanNumber textfield will show the LoanNumbers on the worksheet, then I would like to create a Next and Previous Buttons, to sort through the LoanNumbers within the worksheet.

Can you help me create the VBA code for the LoanNumber Textfield, Next and Previous buttons on the form?

Thanks and any help is greatly appreciated.

 

[SkipVought]

Hi,

Do you have a list of ALL distinct LoanNumbers in your workbook?

Why do you have more than one sheet with LoanNumbers?

Please answer both questions.
Please

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[iuianj07]

Hello SkipVought,

Yes, we do have a full list of distinct LoanNumbers.

I don't understand your second question though. I guess what I meant was, let's say I have Sheet1 and have all the LoanNumbers in column A.

What the UserForm object will have is the first record of LoanNumber, then the next button will go to the next LoanNumber available on Sheet1 etc. and the previous will go to the previous LoanNumber.

I hope this made sense, if not I apologize and let me know if you have any questions.

Thank you Skip

 

[SkipVought]

Sorry, I misread your original requirement.

You can use the Find, FindNext & FindPrev methods.

Whenever a NEW LoanNumber is selected, use the Find method.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[iuianj07]

Can you please help with the code?

I tried to type the code:

Range("A4").End(xlDown).Next.Select

but it didn't put records into the UserForm Object.

thanks

 

[SkipVought]

What UserForm Object?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

How about this as a starting point?

Option Explicit

Private rgLoanNumber As Range

Private Sub btnPrev_Click()
    Set rgLoanNumber = rgLoanNumber.Offset(-1)
    SetNavigationButtons
End Sub

Private Sub btnNext_Click()
    Set rgLoanNumber = rgLoanNumber.Offset(1)
    SetNavigationButtons
End Sub

Private Sub SetNavigationButtons()
    txtLoanNumber = rgLoanNumber.Value
    btnPrev.Enabled = (rgLoanNumber.Row > 2)
    btnNext.Enabled = (rgLoanNumber.Offset(1).Value > "")
End Sub

Private Sub UserForm_Initialize()
    Set rgLoanNumber = ActiveSheet.Range("A2") ' First loan number
    SetNavigationButtons
End Sub