User forms - Alternatives to Me.Field = "Value"

[MBACON]

Hello.

Quick question.

I have a work book that contains 15 different data entry user forms with some common field some not.
In order to keep the users on the straight and narrow I have introduced validation on certain fields in the user forms to ensure they put things in correctly (numbers/ excluded character, not black, etc..).
However, currently I have this code in each user form referenced by Me.<FieldName> = <Constraint> .

What I would like to do is neaten the whole thing up by, once they select generate, rather than having the code within each userform, have a public sub run that has all the validations in one place.

I am struggling with the references however, below is the code excert :

Public Sub VALIDATE_ALL()


If Me.U_DESC = "" Then
    MsgBox ("You have not specified what work is required")
    VALIDATE = True
End If


If Me.U_AVAIL = False Then
    MsgBox ("PLEASE DO Not raise laboratory request unitl the samples are ready for submission.")
    VALIDATE = True
End If
End sub

I have tried setting a global variable to the userform name (UFORMNAME) and replacing 'Me.' with this (doesnt work).
If I change the 'Me.' to specifically say the form name then it does work.

How can i dynamicaly set this to refer to the form in use (without using me. as the sub is in a module rather than embedded in the form)?

THanks.

[snb]

You only need 1 Userform.
If necessary use a Multipage.

Please post a illustrative sample file.

[Paul_Hossler]

In order to keep the users on the straight and narrow

Good luck with that


This is close to what you were asking I think

Option Explicit


Sub ValidateAll()
    Dim i As Long
    
    'the userform must be loaded first
    Load UserForm1
    Load UserForm2
    Load UserForm3
    
    
    'goes from 0 to Count-1
    For i = 0 To UserForms.Count - 1
        With UserForms(i)
            Select Case .Caption
                Case "UserForm1", "UserForm3"
                    If Len(.TextBox1.Text) = 0 Then
                        MsgBox "Enter something in TextBox on " & .Caption
                    End If
            
                Case "UserForm2"
                    MsgBox "Testbox can be empty on " & .Caption
            End Select
        End With
    Next i


End Sub

However, if it were me, I'd have a smart (that's the 'ValidateFlags' part) Validated function (second macro), but call it when the user tried to each userform

If ValidatedBeforeLeaving (Me, 1) Then
    Me.Hide
    Unload me
Endif

Function ValidatedBeforeLeaving(UF As MSForms.UserForm, ValidateFlags As Long) As Boolean        
    ValidatedBeforeLeaving = False
    
    With UF
         Select Case ValidateFlags
               Case 1
                If Len(.TextBox1.Text) = 0 Then
                    MsgBox "Enter something in TextBox1 on " & .Caption
                    Exit Function
                End If
                If Len(.TextBox2.Text) = 0 Then
                    MsgBox "Enter something in TextBox2 on " & .Caption
                    Exit Function
                End If
                If Len(.TextBox3.Text) = 0 Then
                    MsgBox "Enter something in TextBox3 on " & .Caption
                    Exit Function
                End If
            
            Case 2
                If Len(.TextBox3.Text) = 0 Then
                    MsgBox "Enter something in TextBox3 on " & .Caption
                    Exit Function
                End If
        End Select
    End With


    ValidatedBeforeLeaving = True


End Function

[MBACON]

Thanks Paul, Something like that (second one) that I am looking at, just need to try and convert that into my code.
Incase i didn't explain well enough....

Work flow:
Userform 1 - User selects what type of job is required. this then opens 1 out of 15 other user forms.

Userform 'n' - Various fields for the user to fill in, some of these (x8) are the same in all userform forms, others are specific.

Currently: At the moment each UF has a validation macro run (within the UF code) when the user hits a command button to submit.

Future: this is what I am trying to get to. A global macro that runs these validation checks so that I can make changes in one place only rather than each UF individually.
This is where my problem is, you see within the UF code validation i check it all by using me.Textbox1 whereas what I want is to have the 'Me.' be dynamic, so whichever UF has been selected is used.

Hope that makes more sense.

[Paul_Hossler]

I'd make my life simpler and use a MultiPage control like snb suggests

Page 1 - type of job selected by radio button

Depending on which radio button is selected, the appropriate Page in the MultiPage is made visible


All validation done within Userform when user clicks [Done]

If there's a lot of overlap, you might just get with a single userform, and making inapplicable controls hidden

Example --

Job 3 has some additional information required

Only the first textbox on each page is tested to see if it's non-blank

Option Explicit

Dim bEvents As Boolean


Private Sub UserForm_Initialize()
    
    bEvents = True
    
    Call HidePages
    
    
    bEvents = False
    Me.OptionButton1.Value = False
    Me.OptionButton2.Value = False
    Me.OptionButton3.Value = False
    Me.OptionButton4.Value = False
    bEvents = True




End Sub




Private Sub btnDone_Click()
    
    If Len(Me.MultiPage1.Pages(0).TextBox1.Text) = 0 Then
        MsgBox "Blank input in Common"
        Exit Sub
    End If
    
    'etc.
    'etc.
    'etc.
    
    If Me.MultiPage1.Pages(1).Visible Then
        If Len(Me.MultiPage1.Pages(1).TextBox9.Text) = 0 Then
            MsgBox "Blank input in Job 3"
            Exit Sub
        End If
    End If
    
    Me.Hide
    Unload Me
End Sub


Private Sub OptionButton1_Click()
    Call HidePages
End Sub
Private Sub OptionButton2_Click()
    Call HidePages
End Sub
Private Sub OptionButton3_Click()
    Call HidePages(1)   '   Pages start at 0


End Sub
Private Sub OptionButton4_Click()
    Call HidePages
End Sub




Private Sub HidePages(Optional ExceptPageNum As Long = -1)
    Dim i As Long
    
    If bEvents = False Then Exit Sub
        
    bEvents = False
    
    For i = 1 To Me.MultiPage1.Pages.Count - 1
        Me.MultiPage1.Pages(i).Visible = False
    Next i
    
    If ExceptPageNum > 0 Then Me.MultiPage1.Pages(ExceptPageNum).Visible = True
    
    bEvents = True
    
End Sub

[MBACON]

Thanks Gents.

I'll go back and start overhauling my workbook inline with your suggestions and advice.

[snb]

No need to improve built-in options.

[Paul_Hossler]

My idea was to not present the user with information / options / required inputs that are not applicable to the situation

I.e. if Job1 then all required inputs are just the common fields

If Job3 then open the appropriate Page.

Whole thing could be done with single userform by hiding / showing controls based on Job choice

[snb]

If Job_3 is at stake, no other Jobs will be shown (only their tabs). The tabs do already what your optionbuttons are supposed to perform. So in my view the multipage already has the necessary 'optionbuttons'.

[Paul_Hossler]

If Job_3 is at stake,

Not clear to me me how indicate that Job_3 is at stake, as opposed to Job_1 or some other one

1. Form opens
2. User fills out 'Intro' page
3. User opens Job_1 Page
4. User adds Job_1 specific data
5. User for some reason opens Job_3 page and adds data. Changes mind, doesn't understand, needs more coffee, whatever

Now is it a Job_1 task or a Job_3 task?

If the user is perfect and never makes a mistake or gets confused, it would probably work

Personally, I'd just do a basic UF with option buttons or dropdown to select Job_x, and then show/hide controls as needed for that Job

Capture.JPG

Option Explicit


Dim bEvents As Boolean
Dim iJob As Long


Private Sub UserForm_Initialize()
    
    bEvents = True
    
    bEvents = False
    Me.OptionButton1.Value = False
    Me.OptionButton2.Value = False
    Me.OptionButton3.Value = False
    Me.OptionButton4.Value = False
    
    bEvents = True


    iJob = 0
    ShowAdditional
    
End Sub




Private Sub btnDone_Click()
    
    If Not Validated Then Exit Sub
    
    Me.Hide
    Unload Me
End Sub


Private Sub OptionButton1_Click()
    iJob = 1
    Call ShowAdditional
End Sub
Private Sub OptionButton2_Click()
    iJob = 2
    Call ShowAdditional
End Sub
Private Sub OptionButton3_Click()
    iJob = 3
    Call ShowAdditional
End Sub
Private Sub OptionButton4_Click()
    iJob = 4
    Call ShowAdditional
End Sub




Private Sub ShowAdditional()
    Dim i As Long
    
    If bEvents = False Then Exit Sub
        
    bEvents = False
    
    With Me
        .TextBox9.Visible = False
        .TextBox10.Visible = False
        .CheckBox1.Visible = False
        .Frame3.Visible = False
        
        Select Case iJob
            Case 1
                .Frame3.Visible = True
                .Frame3.Caption = "Enter Job 1 Specific Data"
                .TextBox9.Visible = True
                .TextBox9.Text = "Enter Job 1 Size"
                
            Case 2
                .Frame3.Visible = True
                .Frame3.Caption = "Enter Job 2 Specific Data"
                .TextBox9.Visible = True
                .TextBox9.Text = "Enter Job 2 Size"
                .TextBox10.Visible = True
                .TextBox10.Text = "Enter Job 2 Date"
        
            Case 3
                .Frame3.Visible = True
                .Frame3.Caption = "Enter Job 3 Specific Data"
                .TextBox10.Visible = True
                .TextBox10.Text = "Enter Job 3 Manager"
                .CheckBox1.Visible = True
                .CheckBox1.Value = True
                .CheckBox1.Caption = "Check if Job 3 is Overhead"
        End Select
    End With
        
    bEvents = True
    
End Sub




Function Validated() As Boolean
    
    Validated = False
    
    Select Case iJob
        Case 1
            MsgBox "Bad Validation Data for Job Type = 1"
            'Exit function
        Case 2
            MsgBox "Bad Validation Data for Job Type = 2"
            'Exit function
        Case 3
            MsgBox "Bad Validation Data for Job Type = 3"
            'Exit function
        Case 4
            MsgBox "Bad Validation Data for Job Type = 4"
            'Exit function
    End Select
    
    '------------------------------ Always assume TRUE to test
    Validated = True
End Function

[snb]

Just open the attachment in #7

[Paul_Hossler]

Just open the attachment in #7

I did open the attachment

Just read my comments in #10 about it

Not clear to me me how indicate that Job_3 is at stake, as opposed to Job_1 or some other one

1. Form opens
2. User fills out 'Intro' page
3. User opens Job_1 Page
4. User adds Job_1 specific data
5. User for some reason opens Job_3 page and adds data. Changes mind, doesn't understand, needs more coffee, whatever

Now is it a Job_1 task or a Job_3 task?

If the user is perfect and never makes a mistake or gets confused, it would probably work

I just think the single form approach is easier to implement and and simpler to maintain

[snb]

tabs in the multipage behave as optionbuttons, and are performing directly where as the optionbuttons need a macro to hide/unhide.

[Paul_Hossler]

tabs in the multipage behave as option buttons, and are performing directly where as the option buttons need a macro to hide/unhide.

Been thinking on this, and I partially agree

I still prefer the OB approach to make an explicit choice (unhiding a MP Page if required) to guide the user into a single choice

If there were a MP with a common page and multiple job pages, it is possible that the user could enter data onto more than one job page, then you'd need to sort out which job was intended

With an OB explicitly chosen, then it clear to the user and the macro which path to take, even if there is leftover data on other job pages

[snb]

If there were a MP with a common page and multiple job pages,

In that case I would put those 'common' controls in the Userform and the Multipage alongside those controls.

[MBACON]

Thanks for the great discussion guys, it's really insightfull these different approachs.

What I have ended up doing is starting the whole project from scratch again as the validation I was trying to do was just tidying up at the end.
(fortuantly there is no rush for the solution as it is a sideline pet project that i am working on).

I have gone with the the generic UF with a MP section in it, however as I really dont want the users getting confused I am using a dropdown to select which page tab is visible (other others get disabled) for them to fill in the details and submit. (this then get passed into a log sheet within the workbook to store ALL data/records and then generate a word document which is the report template pre-populated with this information).
Slowly getting there!

M