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

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

Exit For


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144571

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

"sam" wrote in message
...
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

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

--
jb


"sam" wrote:

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

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" wrote:

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

--
jb


"sam" wrote:

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

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.

--
jb


"sam" wrote:

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" wrote:

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

--
jb


"sam" wrote:

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

That worked out great!
Thanks! John

"john" wrote:

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.

--
jb


"sam" wrote:

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" wrote:

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

--
jb


"sam" wrote:

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