Consulting

Results 1 to 16 of 16

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

  1. #1
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location

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

    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.
    Last edited by MBACON; 09-29-2020 at 03:44 AM.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,924
    You only need 1 Userform.
    If necessary use a Multipage.

    Please post a illustrative sample file.

  3. #3
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,339
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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.

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,339
    Location
    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
    Attached Files Attached Files
    Last edited by Paul_Hossler; 09-29-2020 at 09:53 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    Thanks Gents.

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

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,924
    No need to improve built-in options.
    Attached Files Attached Files

  8. #8
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,339
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,924
    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'.

  10. #10
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,339
    Location
    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
    Attached Files Attached Files
    Last edited by Paul_Hossler; 09-30-2020 at 08:42 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,924
    Just open the attachment in #7

  12. #12
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,339
    Location
    Quote Originally Posted by snb View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  13. #13
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,924
    tabs in the multipage behave as optionbuttons, and are performing directly where as the optionbuttons need a macro to hide/unhide.

  14. #14
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,339
    Location
    Quote Originally Posted by snb View Post
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,924
    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.
    Attached Files Attached Files

  16. #16
    VBAX Regular
    Joined
    Feb 2020
    Posts
    16
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •