Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: What is wrong with my For loop
Hi All,
I have an excel userfom with a text box that accepts Student_ID. Once a student inputs ID in this textbox, other form fields like Name, Address, Ph_No will auto populate. The button to launch this form is on Sheet1 and the students data that auto populates is in sheet2. NOW, There are instances where new student data is not available in Sheet2 until I manually update Sheet2 with their info. So even if students are enrolled and they input their ID they dont see their Name, Address and Ph_No autopopulate in the fields. Is there a way where a student inputs the ID and if his or hers data is not present in Sheet2 the student can manually input the data which will then populate the Sheet2. Initially I have kept the Name, Address and Ph_No fields disabled as they are autopopulated, But for new students if it doesnt autopopulate, I want these fields to be enabled once a student inputs the ID for 3 times and the Name, Address, Ph_No fields dont autopopulate. SO for eg, IF a student inputs "123ef" in the ID fields and the Name, Address, Ph_No doesnt populate, the student will see a msgbox saying please input your ID again, This will happen for a total of 3 times, and the fourth time if the student inputs the Id and the fields dont autopopulate then the Name, Address and Ph_No fields should be enabled and editable With Me.Student_ID Dim i As Integer If Me.Name.Value = "" Then For i = 1 To 3 MsgBox ("please re-enter Student ID number") Next i End If Me.Name.Enabled = True Me.Address.Enabled = True Me.Ph_No.Enabled = True End With Hope I made it clear Thanks in Advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my For loop
Is the text box for the student's name named Name? There's a confusing
question. I assumed that the input method is through text boxes for all the data. "sam" wrote in message ... Hi All, I have an excel userfom with a text box that accepts Student_ID. Once a student inputs ID in this textbox, other form fields like Name, Address, Ph_No will auto populate. The button to launch this form is on Sheet1 and the students data that auto populates is in sheet2. NOW, There are instances where new student data is not available in Sheet2 until I manually update Sheet2 with their info. So even if students are enrolled and they input their ID they dont see their Name, Address and Ph_No autopopulate in the fields. Is there a way where a student inputs the ID and if his or hers data is not present in Sheet2 the student can manually input the data which will then populate the Sheet2. Initially I have kept the Name, Address and Ph_No fields disabled as they are autopopulated, But for new students if it doesnt autopopulate, I want these fields to be enabled once a student inputs the ID for 3 times and the Name, Address, Ph_No fields dont autopopulate. SO for eg, IF a student inputs "123ef" in the ID fields and the Name, Address, Ph_No doesnt populate, the student will see a msgbox saying please input your ID again, This will happen for a total of 3 times, and the fourth time if the student inputs the Id and the fields dont autopopulate then the Name, Address and Ph_No fields should be enabled and editable With Me.Student_ID Dim i As Integer If Me.Name.Value = "" Then For i = 1 To 3 MsgBox ("please re-enter Student ID number") Next i End If Me.Name.Enabled = True Me.Address.Enabled = True Me.Ph_No.Enabled = True End With Hope I made it clear Thanks in Advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my For loop
I think I would use a Do ..Loop for this:
ctr = 0 Do 'Code her for entering the student ID If Me.Name.Value = "" Then MsgBox "Please re-enter Studernt-ID number" Else Exit Do End If ctr = ctr + 1 Loop Until ctr = 3 If ctr = 3 Then Me.Name.Enabled = True Me.Address.Enabled = True Me.Ph_No.Enabled = True End If This way if the name populates, then it goes on its merry way. If not, it will cycle the three times and enable the other text boxes. "sam" wrote in message ... Hi All, I have an excel userfom with a text box that accepts Student_ID. Once a student inputs ID in this textbox, other form fields like Name, Address, Ph_No will auto populate. The button to launch this form is on Sheet1 and the students data that auto populates is in sheet2. NOW, There are instances where new student data is not available in Sheet2 until I manually update Sheet2 with their info. So even if students are enrolled and they input their ID they dont see their Name, Address and Ph_No autopopulate in the fields. Is there a way where a student inputs the ID and if his or hers data is not present in Sheet2 the student can manually input the data which will then populate the Sheet2. Initially I have kept the Name, Address and Ph_No fields disabled as they are autopopulated, But for new students if it doesnt autopopulate, I want these fields to be enabled once a student inputs the ID for 3 times and the Name, Address, Ph_No fields dont autopopulate. SO for eg, IF a student inputs "123ef" in the ID fields and the Name, Address, Ph_No doesnt populate, the student will see a msgbox saying please input your ID again, This will happen for a total of 3 times, and the fourth time if the student inputs the Id and the fields dont autopopulate then the Name, Address and Ph_No fields should be enabled and editable With Me.Student_ID Dim i As Integer If Me.Name.Value = "" Then For i = 1 To 3 MsgBox ("please re-enter Student ID number") Next i End If Me.Name.Enabled = True Me.Address.Enabled = True Me.Ph_No.Enabled = True End With Hope I made it clear Thanks in Advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What is wrong with my For loop
I think I would modify the Name textbox name to StdtName or something other
than Name. Name is a reserved word and can confuse the compiler in a case like Me.Name. If Me refers to a UserForm then you could get the name of the UserForm. It can screw up your code and cause all kinds of debugging headaches. I try to avoid using reserved words as variables altogether by using abbreviations, prefixes or suffixes to modify them so the compiler knows they are variables and not built in constants. "sam" wrote in message ... Hi All, I have an excel userfom with a text box that accepts Student_ID. Once a student inputs ID in this textbox, other form fields like Name, Address, Ph_No will auto populate. The button to launch this form is on Sheet1 and the students data that auto populates is in sheet2. NOW, There are instances where new student data is not available in Sheet2 until I manually update Sheet2 with their info. So even if students are enrolled and they input their ID they dont see their Name, Address and Ph_No autopopulate in the fields. Is there a way where a student inputs the ID and if his or hers data is not present in Sheet2 the student can manually input the data which will then populate the Sheet2. Initially I have kept the Name, Address and Ph_No fields disabled as they are autopopulated, But for new students if it doesnt autopopulate, I want these fields to be enabled once a student inputs the ID for 3 times and the Name, Address, Ph_No fields dont autopopulate. SO for eg, IF a student inputs "123ef" in the ID fields and the Name, Address, Ph_No doesnt populate, the student will see a msgbox saying please input your ID again, This will happen for a total of 3 times, and the fourth time if the student inputs the Id and the fields dont autopopulate then the Name, Address and Ph_No fields should be enabled and editable With Me.Student_ID Dim i As Integer If Me.Name.Value = "" Then For i = 1 To 3 MsgBox ("please re-enter Student ID number") Next i End If Me.Name.Enabled = True Me.Address.Enabled = True Me.Ph_No.Enabled = True End With Hope I made it clear Thanks in Advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: What is wrong with my For loop
Sam-
Try the following in your code: With Me.Student_ID Dim i As Integer Msgbox Me.Name.Value If Me.Name.Value = "" Then For i = 1 To 3 There isn't enough information here to be sure, but if "me" is the control, then it has a codename that will never be blank, so your loop would never occur. As an aside, it isn't clear why you would make a user enter an ID 3 times. If their data isn't there, it isn't there- and I'd go straight to having them enter it as soon as it isn't found. HTH, Keith "sam" wrote: Hi All, I have an excel userfom with a text box that accepts Student_ID. Once a student inputs ID in this textbox, other form fields like Name, Address, Ph_No will auto populate. The button to launch this form is on Sheet1 and the students data that auto populates is in sheet2. NOW, There are instances where new student data is not available in Sheet2 until I manually update Sheet2 with their info. So even if students are enrolled and they input their ID they dont see their Name, Address and Ph_No autopopulate in the fields. Is there a way where a student inputs the ID and if his or hers data is not present in Sheet2 the student can manually input the data which will then populate the Sheet2. Initially I have kept the Name, Address and Ph_No fields disabled as they are autopopulated, But for new students if it doesnt autopopulate, I want these fields to be enabled once a student inputs the ID for 3 times and the Name, Address, Ph_No fields dont autopopulate. SO for eg, IF a student inputs "123ef" in the ID fields and the Name, Address, Ph_No doesnt populate, the student will see a msgbox saying please input your ID again, This will happen for a total of 3 times, and the fourth time if the student inputs the Id and the fields dont autopopulate then the Name, Address and Ph_No fields should be enabled and editable With Me.Student_ID Dim i As Integer If Me.Name.Value = "" Then For i = 1 To 3 MsgBox ("please re-enter Student ID number") Next i End If Me.Name.Enabled = True Me.Address.Enabled = True Me.Ph_No.Enabled = True End With Hope I made it clear Thanks in Advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
slow (and wrong?) For...Next loop | Excel Programming | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |