Solved: Lost Focus

[DCJones]

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:

[VBA]Private Sub obEnterOT_Click()
ufMainForm.Hide
ufBundyNum.Show
End Sub
[/VBA]
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:

[VBA]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[/VBA]

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?

[darthobra]

DCJones,

What is XYZ? Is it a textbox for the date?

Are you going to be using ufBundyNum after ufEnterOTDetails?

Was ufMainForm open before ufBundyNum?

Darth

[Bob Phillips]

Howabout posting the workbook for us to see in in (in?)action?

[DCJones]

DCJones,

What is XYZ? Is it a textbox for the date?

Are you going to be using ufBundyNum after ufEnterOTDetails?

Was ufMainForm open before ufBundyNum?

Darth

Darth...Yes the XYZ is actually a text box called tbOTDate. The program runs by starting with user form ufMainForm with various options on it chosen by option buttons. It is the form from which all options that the program will perform are chosen, and hence the program returns to this form once a specific task has been achieved (such as adding a new staff member, printing a report etc).
This particular option button is obEnterOT. When it is clicked the main form (ufMainForm) hides and the bundy number user form (ufBundyNum) comes up. Once the correct number of digits (only) have been entered it checks to identify that bundy number against the data base. In this case each staff member has their own sheet in excel with their particulars on it. So the program searches each sheet until it finds a matching bundy number, then extracts the name from that sheet. If no match is found you get:

MsgBox ("You do not appear to be a member of Staff.")

and the program returns to the ufMainForm. If a match is found you get:




resp have entered the bundy number for " & FirstName & " " & Surname & "! Are you " & FirstName & " " & Surname & "?", vbYesNo, "Have you entered the right Bundy #?")

If resp Then

tbBundyNumber.SelStart = 0
tbBundyNumber.SelLength = 5
Exit Sub

End If

Entryname = Left(FirstName, 1) & ". " & Surname
ufBundyNum.Hide
ufEnterOTDetails.Show
Exit Sub


So control then progress to user form ufEnterOTDetails where there are further text boxes with '_Change' conditions on them but this time they do not work!! As I said in the original post, the user form ufEnterOTDetails when run as a stand alone subroutine works just fine, but call it from the nested subroutines and the '_Change' condition no longer works in the text boxes the require an input from the user. The "If resp Then" section allows the user to re-enter a bundy number in case the wrong one was put in originally, identifying the wrong person.

NOTE: All of the above steps are done without the use of clicking a button to proceed to the next user form. The only input from the user is to click on Yes/No message boxes, option buttons or inputting data into text boxes. The focus even shifts to the next field (i.e. text box) without hitting the enter key. Progress is only achieved once the current text box if filled out correctly.

xld....willing to do that but I don't know how!!!

[mikerickson]

If ufBundyNum is a modal userform, there may be a difficuly with the order of excitution. I've added comments to illustrate my concern.
in ufMain
[VBA]Private Sub obEnterOT_Click()
ufMainForm.Hide: 'line m1
ufBundyNum.Show 'line m2
End Sub' line m3[/VBA]
in ufBundyNum
[VBA]ufBundyNum.Hide 'line b1
ufEnterOTDetails.Show 'line b2
Exit Sub[/VBA]

If ufBundyNum is modal, it seems to me that code execution would be
line m1, line m2, (data entry), line b1, line m3 at which point things get weird.

You might want to pass the validation information back to UF1 rather than making sub-sub-sub userforms. Something like
in ufMain[vba]ufMain.Hide
ufBundyNum.Show
If ufBundyNum.Tag = "OKuser" Then
Unload ufBundyNum
ufOTDetails.Show
Else
Unload ufBundyNum
Msgbox "unauthorized user"
ufMain.Show
End If
[/vba]
in ufBundyNum
[VBA]If (userIsValidated) Then
ufBundyNum.Tag = "OKuser"
End If
ufBundyNum.Hide[/VBA]

[DCJones]

mikerickson,

Sorry for the tardy reply...been at work without computer/internet.

Your reply has gone over my head with the use of modal as I do not understand its use/function. I have however incorporated the redirection of the validation as I (and this coming from a total novice, so treat it for what it is worth) feel that it is a better programming technique. I don't like the flow of sub-sub-sub as the program jumps all over the place as it returns to the calling sub till end/exit sub is executed.

DESPITE my lack of knowledge of 'Modal' you have helped me solve my problem. I was cutting and pasting sections of the program into a new workbook and seeing how they interacted. I discovered that all the userforms except the ufBundyNum were interacting as you would expect. So I decided to scrap that copy of ufBundyNum and make up a new userform that would hopefully work. While doing this I thought I would compare the two userform properties to see if I could detect the problem. I then noticed 'Modal' as one of the properties and low and behold there was the problem one userform was true the other false in the properties.
When the original uf was changed back the form worked perfectly. Six weeks of utter frustration over a little thing as that!

So it is on to my next 'challenge' that of pasting check boxes onto a blank userform, one for each employee (the number of which changes through the year) with the name of the check box the same as the name of the employee. The user of the pregame can then choose multiple employees that then progress onto the next part of the program. This userform is choosing which employees are eligible for overtime. Those chosen employees are then ranked most eligible to least eligible depending on the number of overtime hours they have performed to date. The overtime is then offered to the employees in that ranked order until one of them accepts the shift. I have no idea if this is feasible or how I am going to do it but I 'enjoy' the process as that is how I am teaching myself VBA.

To mikerickson and the rest who responded, I say thankyou for freely giving of your time and knowledge. This is a great forum...keep up the great work!
</IMG></IMG>

[mikerickson]

"Modal" If a userform has to be unloaded or hidden before work can be done in the spreadsheet cells, it is modal. If you can see the userform while working with spreadsheet cells, the userform is "modeless". The VB help system will have more information.

About your list of employees, rather than creating a bunch of check boxes (each of which needs its own event code to be written), you might think of useing a multi-select ListBox set to .ListStyle = fmListStyleOption. There is only one list box and no event code needs to be written on the fly. A list box can accommidate a varying number of list entries (employees.)

[DCJones]

I did not know such a beast as the 'multi-select' ListBox existed. Thought ListBoxes did only that, show you a list that was non interactive. As you say a far better approach, thanks again for the tips! I was wondering how I would get a userform to expand and contract with different numbers of employees listed, now don't need to bother.