DCJones
08-28-2008, 05:48 PM
First post so I hope I'm in the right spot!!
This is also my first attempt at programming so please don't laugh at my 'novice' attempts. I have spent over 50hrs trying to overcome this problem, alas to no avail. Not sure what info you will need to possibly help me so I will leave it to you to ask the pertinant questions.
I have a userform that has option buttons on it to direct the user to the next phase of the program via:
Private Sub obEnterOT_Click()
ufMainForm.Hide
ufBundyNum.Show
End Sub
Userform, ufBundyNum, asks the operator to input their Bundy Number as a means of user identification. There are various tests on this entry such as not allowing letters etc. as the operator enters their bundy number. Once the required number of numbers is entered the program automatically progresses to checking the bundy# against a data base, if it matches an entry it then comes back with a message box with a yes/no, asking if the identification was correct. This is all done with the following:
Private Sub tbBundyNumber_Change()
Dim DateEntry As Date
If tbBundyNumber.Text = "" Then Exit Sub
If Not IsNumeric(Right(tbBundyNumber.Text, 1)) Then
tbBundyNumber.SelStart = Len(tbBundyNumber.Text) - 1
tbBundyNumber.SelLength = 1
lblVerify.Caption = "Only numbers are permitted!"
Exit Sub
Else
lblVerify.Caption = ""
End If
If Len(tbBundyNumber.Text) = 5 Then
bundynum = CLng(tbBundyNumber)
x = Sheets.Count
If x > 4 Then
For y = 5 To x
With Sheets(y)
If .Range("I5") = bundynum Then
FirstName = .Range("E5").Characters.Text
Surname = .Range("E6").Characters.Text
response = MsgBox("You have entered the bundy number for " & FirstName & " " & Surname & "! Are you " & FirstName & " " & Surname & "?", vbYesNo, "Have you entered the right Bundy #?")
If response = vbNo Then
tbBundyNumber.SelStart = 0
tbBundyNumber.SelLength = 5
Exit Sub
End If
Entryname = Left(FirstName, 1) & ". " & Surname
ufBundyNum.Hide
ufEnterOTDetails.Show
Exit Sub
End If
End With
Next y
End If
MsgBox ("You do not appear to be a member of Staff.")
ufBundyNum.Hide
ufMainForm.Show
End If
End Sub
If yes is selected from the above mentioned message box it progresses to another userform ufEnterOTDetails.
Now comes the bit that does not work. Once the userform ufBundyNum disappears and ufEnterOTDetails appears there is another text box on the new form that requires a date to be entered. Once again there are tests on the information as it is entered into the text box to make sure a correct date is being entered. This is done, as above, with a 'Private Sub XYZ_Change()'. Now this userform works perfectly when it is run independantly of the main program but once it is activated by the above code the 'Private Sub XYZ_Change()' subroutine is not activated. You can enter information into the text box but the checks do not work enabling anything including letters to be entered for a date.
Hope that is not too confusing! Can anyone help?
This is also my first attempt at programming so please don't laugh at my 'novice' attempts. I have spent over 50hrs trying to overcome this problem, alas to no avail. Not sure what info you will need to possibly help me so I will leave it to you to ask the pertinant questions.
I have a userform that has option buttons on it to direct the user to the next phase of the program via:
Private Sub obEnterOT_Click()
ufMainForm.Hide
ufBundyNum.Show
End Sub
Userform, ufBundyNum, asks the operator to input their Bundy Number as a means of user identification. There are various tests on this entry such as not allowing letters etc. as the operator enters their bundy number. Once the required number of numbers is entered the program automatically progresses to checking the bundy# against a data base, if it matches an entry it then comes back with a message box with a yes/no, asking if the identification was correct. This is all done with the following:
Private Sub tbBundyNumber_Change()
Dim DateEntry As Date
If tbBundyNumber.Text = "" Then Exit Sub
If Not IsNumeric(Right(tbBundyNumber.Text, 1)) Then
tbBundyNumber.SelStart = Len(tbBundyNumber.Text) - 1
tbBundyNumber.SelLength = 1
lblVerify.Caption = "Only numbers are permitted!"
Exit Sub
Else
lblVerify.Caption = ""
End If
If Len(tbBundyNumber.Text) = 5 Then
bundynum = CLng(tbBundyNumber)
x = Sheets.Count
If x > 4 Then
For y = 5 To x
With Sheets(y)
If .Range("I5") = bundynum Then
FirstName = .Range("E5").Characters.Text
Surname = .Range("E6").Characters.Text
response = MsgBox("You have entered the bundy number for " & FirstName & " " & Surname & "! Are you " & FirstName & " " & Surname & "?", vbYesNo, "Have you entered the right Bundy #?")
If response = vbNo Then
tbBundyNumber.SelStart = 0
tbBundyNumber.SelLength = 5
Exit Sub
End If
Entryname = Left(FirstName, 1) & ". " & Surname
ufBundyNum.Hide
ufEnterOTDetails.Show
Exit Sub
End If
End With
Next y
End If
MsgBox ("You do not appear to be a member of Staff.")
ufBundyNum.Hide
ufMainForm.Show
End If
End Sub
If yes is selected from the above mentioned message box it progresses to another userform ufEnterOTDetails.
Now comes the bit that does not work. Once the userform ufBundyNum disappears and ufEnterOTDetails appears there is another text box on the new form that requires a date to be entered. Once again there are tests on the information as it is entered into the text box to make sure a correct date is being entered. This is done, as above, with a 'Private Sub XYZ_Change()'. Now this userform works perfectly when it is run independantly of the main program but once it is activated by the above code the 'Private Sub XYZ_Change()' subroutine is not activated. You can enter information into the text box but the checks do not work enabling anything including letters to be entered for a date.
Hope that is not too confusing! Can anyone help?