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

27242



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