Userform- Simple

[davidhall]

I have a userform which a user has to input a loan number. If they input the loan number in the textbox, it feeds to cell A2 of the worksheet called UserformPractice.

If the user does not input anything in the textbox, a message box appears reminding them to input the loan number.

I need help with my code so that once this message box appears, it takes them back to the userform so that can put in the loan number.

Additionally, if there was any possibility to not allow the user to proceed with using the spreadsheet unless the loan number is put in the textbox, that would be great.

I'm sure I would have to insert another button titled "ExitExcel" to initiate
that process.


Any help would be great.


Here is my code.

Option Explicit


Private Sub cmbSubmit_Click()
Dim Response1 As VbMsgBoxResult
Dim ws As Worksheet
Set ws = Worksheets("UserformPractice")

ws.Range("A2").Select
Do Until ActiveCell.Value <> ""


If txtLoanNumber.Value <> "" Then
ws.Range("A2") = ""
ws.Range("A2").Value = Me.txtLoanNumber.Value

ElseIf txtLoanNumber.Value = "" Then

Response1 = MsgBox("Please Input The Loan # ", vbOKOnly, "Missing Loan Information")
If Response1 = vbOKOnly Then
End If
Loop
End If



Unload Me

End Sub

Private Sub txtLoanNumber_Change()
End Sub

 

[BrianExcel]

As far as redirecting back to the userform why not just have the "OK" button close the form and re-open?

Option Explicit


Private Sub cmbSubmit_Click()
Dim Response1 As VbMsgBoxResult
Dim ws As Worksheet
Set ws = Worksheets("UserformPractice")

ws.Range("A2").Select
Do Until ActiveCell.Value <> ""


If txtLoanNumber.Value <> "" Then
ws.Range("A2") = ""
ws.Range("A2").Value = Me.txtLoanNumber.Value

ElseIf txtLoanNumber.Value = "" Then

Response1 = MsgBox("Please Input The Loan # ", vbOKOnly, "Missing Loan Information")
If Response1 = vbOKOnly Then
    [COLOR="Red"]exit sub
    load INSERTUSERFORMNAMEHERE
    INSERTUSERFORMNAMEHERE.show[/COLOR]
End If
Loop
End If



Unload Me

End Sub

Private Sub txtLoanNumber_Change()
End Sub

 

[BrianExcel]

As far as not allowing the user to edit the sheet, just have the sheet hidden upon workbook_open and and display the sheet only on successful input of a loan number.

 

[davidhall]

Im running into and error stating Do without loop

 

[BrianExcel]

What is the loop doing in your code? Why is a loop needed?

Also, if you want to post your workbook on box.net I can review and take a closer look....that usually makes it easier...

 

[davidhall]

Well, I thought it was needed for some reason.

I have removed


ws.Range("A2").Select
Do Until ActiveCell.Value <> ""


and

Loop


from the code and when I input nothing in the textbox and it just exits entirely.


Here is my current code:

Private Sub cmbSubmit_Click()
Dim Response1 As VbMsgBoxResult
Dim ws As Worksheet
Set ws = Worksheets("UserformPractice")


If txtLoanNumber.Value <> "" Then
ws.Range("A2") = ""
ws.Range("A2").Value = Me.txtLoanNumber.Value

ElseIf txtLoanNumber.Value = "" Then

Response1 = MsgBox("Please Input The Loan # ", vbOKOnly, "Missing Loan Information")
If Response1 = vbOKOnly Then
Exit Sub
Load UserForm1
UserForm1.Show
End If
End If



Unload Me

End Sub

 

[BrianExcel]

You'll have to substitute the name of your userform for whatever I type in there since I don't know your form names...

Private Sub cmbSubmit_Click()
Dim Response1 As VbMsgBoxResult
Dim ws As Worksheet
Set ws = Worksheets("UserformPractice")

If txtLoanNumber.Value <> "" Then
ws.Range("A2") = ""
ws.Range("A2").Value = Me.txtLoanNumber.Value

ElseIf txtLoanNumber.Value = "" Then

Response1 = MsgBox("Please Input The Loan # ", vbOKOnly, "Missing Loan Information")
     If Response1 = vbOKOnly Then
         Load UserForm1 [COLOR="Red"]<--This is where you need to input your userform name[/COLOR]
         UserForm1.Show [COLOR="red"]<---Make sure you change it here too...[/COLOR]
     End If
End If
End Sub

 

[davidhall]

Ok I figured it out.

This is what I ended up with.

Private Sub cmbSubmit_Click()
Dim Response1 As VbMsgBoxResult
Dim ws As Worksheet
Set ws = Worksheets("UserformPractice")


If txtLoanNumber.Value <> "" Then
ws.Range("A2").Value = Me.txtLoanNumber.Value

ElseIf txtLoanNumber.Value = "" Then

Response1 = MsgBox("Please Input The Loan # ", vbOKOnly, "Missing Loan Information")
If Response1 = vbOK Then
Exit Sub
Load EnterLoanNumber
EnterLoanNumber.Show
End If
End If


Unload Me

End Sub

 

[BrianExcel]

Good to hear.

 

[davidhall]

Brian,

Here is my most updated code.

The following things are functioning properly.
1) Userform pops up. User inputs loan number which feeds to cell D7 of sheet DPmtData.
--there is other data on this same sheet that uses d7 as a vlookup on other pages to pull data..just fyi

2) If the loan number the user input matches a range ("G19: G65000") on another sheet titled LoanDataImport, then information about the index description that loan uses is updated on another sheet called IndexCalc.

This is not working and could use some assistance:

3) My problem occurs when the loan number that a user puts in the text box is not found in the range, nothing happens other than the userform exits.

I would like the date someone inputs to be compared to the range and if it doesn't match, display a message box telling the user to try again and reload the userform titled EnterLoanNumber.

Any suggestions?

Private Sub cmbSubmit_Click()
Dim Response1 As VbMsgBoxResult
Dim Response2 As VbMsgBoxResult
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("CREAM INPUT")
Set ws2 = Worksheets("DPmtData")




If txtLoanNumber.Value <> "" Then
ws2.Range("D7").Value = Me.txtLoanNumber.Value

ElseIf txtLoanNumber.Value = "" Then

Response1 = MsgBox("Please Input The Loan # ", vbOKOnly, "Missing Loan Information")
If Response1 = vbOK Then
Exit Sub
Load EnterLoanNumber
EnterLoanNumber.Show
End If
End If

Unload Me


Dim i As Long
Dim FinalRow As Long
Dim NextRow As Integer
On Error GoTo ErrorHandler

Application.ScreenUpdating = False
FinalRow = Worksheets("IndexImport").Cells(Rows.Count, 2).End(xlUp).Row
NextRow = Worksheets("IndexCalc").Cells(Rows.Count, 1).End(xlUp).Row + 1
Worksheets("IndexCalc").Range("A3:D1000").ClearContents
NextRow = 3
Worksheets("IndexImport").Activate
For i = 1 To FinalRow
If Cells(i, 2).Value = Worksheets("IndexCalc").Range("A1").Value Then
Cells(i, 2).Resize(1, 4).Copy Destination:=Worksheets("IndexCalc").Cells(NextRow, 1)
NextRow = NextRow + 1
End If
Next i

Application.ScreenUpdating = True
ws1.Activate
ws1.Range("A1").Select


Exit Sub

ErrorHandler:
Select Case Application

Case 1
MsgBox ("Non MFL Loan. Please retry")
If Response2 = vbOK Then
Unload Me
End If

Load EnterLoanNumber
EnterLoanNumber.Show

End Select
End Sub