Please Help: Whats wrong with my For loop?

[sam]

With Me.StudentId.Value

Dim i As Integer

If Me.Name.Value = "" Then

For i = 1 To 3

MsgBox (" please re-enter Student Id number")

Exit For
Next i

Me.Name.Enabled = True
Me.Address.Enabled = True
Me.Ph_No.Enabled = True

End If

End with

I want the students to try inputing their ID 3 times before the Name,
Address and Ph_No fields are enabled for them to input.

Thanks in advance

 

[joel]

Remove the exit statment it is only allowing code to go through the loo
once

Exit Fo

 

[Bernie Deitrick]

Sam,

Try this.

On your userform (Userform1), add four text boxes:

tbStudID, tbName, tbAddress, and tbPh_No

Then add 2 commandbuttons

cbFindData
cbAddDataToDB

Add the code below to the codemodule of the userform. And use this code to show it:

Sub ShowIDForm()
Load UserForm1
UserForm1.Show
End Sub

I've assumed that you Student IDs are in column A, Names in column B, Addresses in column C, and
phone #s in column D, all on the only sheet of the workbook.

HTH,
Bernie
MS Excel MVP


Option Explicit
Dim ICnt As Integer
Dim myR As Long

Private Sub cbFindData_Click()
Me.tbName.Text = ""
Me.tbAddress.Text = ""
Me.tbPh_No.Text = ""

ICnt = ICnt + 1

If Not IsError(Application.Match(tbStudID.Text, Range("A:A"), False)) Then
myR = Application.Match(tbStudID.Text, Range("A:A"), False)
Me.tbName.Text = Cells(myR, 2).Value
Me.tbAddress.Text = Cells(myR, 3).Value
Me.tbPh_No.Text = Cells(myR, 4).Value
ICnt = 0
End If

If Me.tbName.Value = "" Then

If ICnt < 3 Then
MsgBox ("Please re-enter Student Id number")
Exit Sub
Else
MsgBox "Please enter information into boxes"
End If

Me.tbName.Enabled = True
Me.tbAddress.Enabled = True
Me.tbPh_No.Enabled = True
Me.cbAddDataToDB.Enabled = True

End If

End Sub

Private Sub cbAddDataToDB_Click()
If Me.tbStudID.Text <> "" And _
Me.tbName.Text <> "" And _
Me.tbAddress.Text <> "" And _
Me.tbPh_No.Text <> "" Then

myR = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(myR, 1).Value = Me.tbStudID.Text
Cells(myR, 2).Value = Me.tbName.Text
Cells(myR, 3).Value = Me.tbAddress.Text
Cells(myR, 4).Value = Me.tbPh_No.Text
ICnt = 0
Else
MsgBox "Fill it all in"
End If
End Sub

Private Sub UserForm_Activate()
Me.cbAddDataToDB.Enabled = False
Me.tbName.Enabled = False
Me.tbAddress.Enabled = False
Me.tbPh_No.Enabled = False
ICnt = 0

End Sub

 

[john]

Hi Sam,
without seeing the code you use to populate your textboxes, can only guess
the process you are using.

Assuming OP presses a commandbutton after entering their ID then as quick
idea, perhaps an approach like following may work for you?

You will note that I have added word “Student†in front of your textbox
names “Name & “Address†these words are reserved in excel and their use as
textbox names may give unexpected results.

Hope helps I am about catch train home!

Dim i As Integer

Private Sub CommandButton1_Click()

If Me.Student_Name.Text = "" Then


If i < 4 Then

MsgBox (" please re-enter Student Id number" & Chr(10) & Chr(10)
& _
"Attempt " & i)

i = i + 1

Me.Student_ID.SetFocus


Else

MsgBox ("Please enter your Student Data")

Me.Student_Name.Enabled = True
Me.Student_Address.Enabled = True
Me.Ph_No.Enabled = True

Me.Student_Name.SetFocus

i = 1

End If

End If

End Sub

Private Sub UserForm_Initialize()
i = 1
Me.Student_Name.Enabled = False
Me.Student_Address.Enabled = False
Me.Ph_No.Enabled = False
End Sub

 

[sam]

Thanks for the help john, But The "i" value is not imcrementing at "i = i +
1" line
the value always stays as "1". I inserted a break point at i = i + 1 and the
value of i always stays as 1. Am I missing something here?

Thanks in advance

 

[john]

did you place Dim i As Integer inside the procedure? If so, it will always be 1

Place it at the top of the forms code page - sorry if my example did not
make this clear.

 

[sam]

That worked out great!
Thanks! John

did you place Dim i As Integer inside the procedure? If so, it will always be 1

Place it at the top of the forms code page - sorry if my example did not
make this clear.