VBA: Using Multiple User Forms

Question

I am having trouble with using multiple user forms. I know it has to do with transferring the variable but I can't seem to get it right. I would like this code to first ask the user if they are a manager or employee, then if they choose manager then it asks them to enter their manager ID # in the box, then once that happens another user form pops up for the manager. If they select employee then a different user form will show. Here is what I have so far. Can someone let me know what's going wrong here?

Here is the user form code that asks the user if they are a manager or employee:

Private cancel As Boolean

Public Function ShowfrmType()
Public employeeType As String


'Show manager user form
If optManager.Value Then employeeType = "manager"


If optEmployee.Value Then employeeType = "employee"


ShowfrmType = Not cancel
Unload Me

End Function

Here is the module code:

Sub editEvaluation()


'Shows employeeType user form
frmType.Show

'if manager then enter manager ID #
'then allows manager to enter in an overall score for emplpoyee
If employeeType = "manager" Then
frmManager.Show
End if 

If employeeType = "employee" Then
frmEmployee.Show
MsgBox "Please fill out your self evaluation under the column: Self     Evalauation Score"


End If

Answer 1

I believe you can stay with "modal" userform (not to loose control over what's happening where) and:

• have them open in cascade accordingly to user choices

• every time you open a "child" userform, hide the current ("parent") one

• every time you leave a userform

  • has its own code just hide it, instead of closing or unloading it, so that its "parent" userform still have access to its collected data

  • leave its "parent" userform the task to unload it, after exploiting its collected data

so you may have three userforms

1. frmType

   it's the "main" one, called by the "main" Sub editEvaluation()

   it will just have to OptionButtons (called "optManager" and "optEmployee") and the following code

Private Sub optManager_Click() Me.Hide '<--| hide current (frmType) form frmManager.Show '<--| show frmManager to allow manager enter its data Unload frmManager '<--| have current ("parent") userform unload its "child" userforms End Sub

Private Sub optEmployee_Click() Me.Hide '<--| hide current (frmType) form frmEmployee.Show '<--| show employee userform to allow employee enter its data Unload frmEmployee '<--| have current ("parent") userform unload its "child" userforms End Sub

2. frmManager

   it's the "manager" one, called by the "main" frmType userform

   before allowing the manager to edit its fields it will first ask him for his ID, validate it and finally, should validation end successfully, give acces to its fields.

   otherwise it'll hide itself

   to do the above explained manager ID ask&validation routine you could exploit the UserForm_Activate() event that fires as soon as the userform is shown

   so add this code to frmManager code pane

Private Sub UserForm_Activate() If Not CheckManagerID(InputBox("Please input your ID", "Manager ID", "AA000")) Then MsgBox "Sorry, your managerID is not Valid", vbCritical '<--| if managerID is NOT valid then inform the user and exit userform Me.Hide '<--| only hide the userform and let parent userform take care of unloading it Else MsgBox "Welcome!" & vbCrLf & "Now enter in your overall score for employee" '<--| if managerID IS valid then introduce him to the userform and let it shown to have the user (manager) fill its controls End If End Sub

Private Function CheckManagerID(ID As String) As Boolean CheckManagerID = ID Like "[A-Z][A-Z]###" ' check if passed ID has two capital letters followed by three digits End Function

(note: CheckManagerID() function is just an example of a string validation)

and where you'll place all wanted controls to collect data input by the "validated" manager

should you ever have a "Close" button (say you called it BtnClose) then its Click event handler would be:

Private Sub BtnClose_Click() Me.Hide '<--| only hide the userform and let parent userform take care of unloading it End Sub

3. frmEmployee

   where, similarly to frmManager

   • you'll use its UserForm_Activate event handler to introduce employee to this form

Private Sub UserForm_Activate() MsgBox "Please fill out your self evaluation under the column: Self Evaluation Score" End Sub

• should you ever have a "Close" button (say you called it BtnClose) then its Click event handler would be:

Private Sub BtnClose_Click() Me.Hide '<--| only hide the userform and let parent userform take care of unloading it

and finally here's the code for the "main" Sub editEvaluation()

Option Explicit

Sub editEvaluation()

    'Show "main" type userform
    With frmType
        .Show '<--| this userform has to collect user initial data and then call "child"" userforms accordingly
    End With
    Unload frmType '<--| have this sub unload its "child" userform

End Sub

Answer 2

I think what you're after here is a Modeless form. Modeless dialog boxes let you shift the focus between the dialog box and another form without having to close the dialog box. You can continue to work elsewhere in the current application while the dialog box is displayed. So, for instance:

Private cancel As Boolean

Public Function ShowfrmType()
    Public employeeType As String
    If optManager.Value Then employeeType = "manager"
    If optEmployee.Value Then employeeType = "employee"
    ShowfrmType = Not cancel
    Unload Me
End Function

Sub editEvaluation()
    frmType.Show vbModeless
    If employeeType = "manager" Then
        frmManager.Show vbModeless
    End If
    If employeeType = "employee" Then
        frmEmployee.Show vbModeless
        MsgBox "Please fill out your self evaluation under the column: Self     Evalauation Score"
    End If
End Sub

Hope this is of some help.