UserForm Data Validation. I tried and tried but can't figure it out.

[Greg Maxey]

Steve Hudson was schooling me in UserForms earlier today.

He advised user form code should pretty well only exist in order to provide
data validation trough calling public procedures. I tried employing one of
his "modified" suggestions as follows.

I have a simple userform oFrm1 with a 1) command button and 2) a frame with
one textbox inside.

The purpose of this exercise is to learn to pass information from a calling
macro to a UserForm and report validated information back to the calling
macro. I am using an input box with a default value to set the
Frame.Caption in the UserForm. This part works. I am using the textbox in
the Userform to provide raw data to a Function. The Function is supposed to
validate the data. Then the validated textbox data is used to set the value
of a calling macro msgbox. Everything works if the data is valid. The
problem I can't figure out is correcting invalid data. On the first pass,
invalid data is detected and I am directed back to the Userform. However,
when I then enter valid data the routing complete but the data is lost. I
think my problem is that after I enter valid data, the code returns to the
function at the End With statement following .Show. For some reason this
scrambles the data and I just can't figure out a way around it.

Any help to get me back on track is appreciated.

Here is my calling macro:

Option Explicit
Public oPassedData As String
Public frmObject As myObject
Public myFrm As oFrm1

Sub PassData1()
Set myFrm = New oFrm1
'Pass Caption to UserForm.Frame1
myFrm.Frame1.Caption = InputBox("Enter Label1 Caption: ", _
"Pass Caption", "Enter a shoe size e.g., 10E")
myFrm.Show
MsgBox "Formatted date returned from the UserForm: " _
& myFrm.TextBox1.Text, , "Data Returned"
Set myFrm = Nothing
End Sub

Here is the UserForm Code:

Private Sub CommandButton1_Click()
Me.hide 'Dismiss the form and return processing to the caller
'Call a Function to validate data
TextBox1.Text = ShoeSizeValidator(TextBox1.Text)
End Sub


Here is the validating Function:

Function ShoeSizeValidator(oShoeSize As String) As String
If IsNumeric(oShoeSize) Then
ShoeSizeValidator = oShoeSize
Else
With myFrm
.Frame1.Caption = "Invalid entry. Please enter a valid size."
.TextBox1.Text = oShoeSize
With .TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
.Show
End With
End If
End Function

 

[Helmut Weber]

Hi Greg,

maybe I am on the wrong track,
but more than the following isn't necessary to me.

Sub Initialize()
' create a random character between 48 and 70
Randomize
UserForm1.TextBox1.Value = _
Chr(Int((70 - 48 + 1) * Rnd + 48))
UserForm1.Cmd1.Caption = "right or wrong?"
UserForm1.Show
End Sub

Public Function NumCheck(sSize As String) As String
If IsNumeric(sSize) Then
NumCheck = "alright"
Else
NumCheck = "again"
End If
End Function
plus

Private Sub Cmd1_Click()
Me.Cmd1.Caption = NumCheck(TextBox1.Text)
UserForm1.TextBox1.SetFocus
End Sub

Hmm...
no objects, no global variables required, seemably.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Greg Maxey]

Helmut,

Looks interesting. 7" of fresh snow, but I still have to try to slog off to
work :-(

Will study it later. Thanks.

 

[Greg]

Helmut,

Thanks for the nudge. Trying to get out of your form after getting in
it baffled me for a while, but the absence of a second .Show command
inspired me to a solution I think!!??

I abandoned the shoe size validator in favor of Steve's original
brassiere size validator simply to avoid dealing with half sizes (it
had nothing to do with being a geo-graphical bachelor on a cold and
snowy day ;-))

My objective of course was not necessarily a simple solution, but to
develop and show a process where the calling macro would send data to
customize the UserForm (in this case an Inputbox is provided with
default text to suit the rest of the scenario), the user would enter a
value in the UserForm, that data would be validated externally, a valid
entry would be passed back to the calling macro (in this case a message
box is used), invalid entries are flagged and the user is returned to
the userfrom.

You will need a Userform with a Command Button, a Frame and a TextBox
(in the Frame).

The Calling Macro:

Option Explicit
Sub PassData1()
Dim myFrm As oFrm1
Set myFrm = New oFrm1
'Pass a custom Caption to myFrm.Frame1
myFrm.Frame1.Caption = InputBox("Enter a custom Frame1 Caption: ", _
"Custom Caption", "Enter a standard brassiere size e.g., 34D")
myFrm.Show
MsgBox "You entered size " & myFrm.TextBox1.Text & " . This" _
& " is a valid size.", , "Data Returned"
Set myFrm = Nothing
End Sub


The Validating Function:

Function SizeValidator(sSize As String) As Boolean
Select Case True
Case Is = Val(Left(sSize, 2)) <= 42 _
And Val(Left(sSize, 2)) >= 30 _
And Val(Left(sSize, 2)) Mod 2 = 0
Select Case True
Case Is = sSize Like "##[A-J]"
SizeValidator = True
Case Is = sSize Like "##AA" Or sSize Like "#DD" _
Or sSize Like "##DD" Or sSize Like "##HH" _
Or sSize Like "##JJ"
SizeValidator = True
Case Else
SizeValidator = False
End Select
Case Else
SizeValidator = False
End Select
End Function

The UserForm Code:

Option Explicit
Private Sub CommandButton1_Click()
Dim bCheckSize As Boolean
bCheckSize = SizeValidator(Me.TextBox1.Text)
If bCheckSize Then
Me.Hide
Else
With Me
.Frame1.Caption = Me.TextBox1.Text & " is invalid." _
& " Please enter a valid size."
With .TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
End With
End If
End Sub

It seems to work (I couldn't break it). As you said, "no objects, no
global variables required, seemably."

What I don't know is if I have create robust code or a time bomb.

Steve if I haven't caugt the fish I think I have hooke it. Pleas advise.

 

[Helmut Weber]

Hi submariner,

It seems to work (I couldn't break it).
As you said, "no objects, no
global variables required, seemably."

What I don't know is if I have created robust code or a time bomb.

I don't know either and would like to hear from the co-readers,
if I have to load a form and unload it afterwards,
and how, to avoid all possible complications.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Greg Maxey]

Helmut,

Here is what Steve said about load and unload:

"Finally, I did some pretty exhaustive testing, and asked around inside, and
found that effectively:
Unload MyForm is the same as Set MyForm=Nothing
and
Load MyForm is the same as Set MyForm = New DINGOFORM1
Load and Unload are only there for magic forms support, as in
Load DINGOFORM1"

I don't know what he means by "magic forms support."

 

[Tony Jollans]

I figure you'll get as many views on this as there are programmers respond!

Personally I don't see any conceptual difference between module level public
(i.e. global) variables and (module level) public functions. They can both
be used to good effect or bad.

As a general rule I would say that userform-specific (or more generally
object-specific) functionality belongs in the (userform) object. But,
wherever they are, that all variables, and functions, have the narrowest
scope consistent with requirements.

If you are developing an application (your own little thing or something
major, like Word, say) then there will be common code which can be
encapsulated somehow and made available across the whole application (in a
VB Project it may be a public Function, in Windows it may be an API in a
DLL). When you have finished development you package up the application
including all the comon elements and you distribute it somehow.

What can easily happen (without proper control) is that you use common code
that exists somewhere in your development environment but which doesn't
belong to the application you are developing. For example you have you
BraSize application and in developing PerfectWoman V6 (or whatever it was)
you use the bra-size validation routine which belongs to the BraSize
application. Now, when you package up and ship your PerfectWoman
application, it calls, but does not contain, the bra-size validation. I
install it and it doesn't work for me. Now, you could say that the BraSize
app is a pre-requisite for PerfectWoman, and that might be OK in this
instance. This happens all the time in practice with, for example, the .NET
framework being a pre-req; it also causes many problems and many questions
to be asked here and elsewhere when people want to ensure that target
machines have the correct references set.

Now suppose I had your BraSize app installed and I developed my own CatWalk
application, using your validation function in the process. In this case my
built application would not - and could not - contain your valiadtion
routine. Now I have a problem (this also regularly happens in practice). If
I had made sure that all my userform code was in (or at least very close to)
my userform this wouldn't have happened.

Now, of course, you don't want to be constantly re-inventing the wheel and
having common code is a great boon, but what you must do is organise and
control it properly. How you, or any other individual, uses it is a matter,
to some extent, of choice - the really important thing is being aware of
what you are doing.

 

[Greg Maxey]

Thanks for your views Tony. I am not a programmer or familiar enough with
programing to know if I have been enlightened or misled ;-). Everything you
said certainly seems to make sense.

I would be interested in your views on Load and Unload

Some people that I have learned an awfully lot from say us:
Set myFrm as New oFrm
Load myFrm
Unload myFrm
Set myFrm = Nothing

Others say:
Set myFrm as New oFrm
'Load myFrm (Leave this out)
Unload myFrm
Set myFrm = Nothing

And at least one says:
Set myFrm as New oFrm
'Load myFrm (Leave this out)
'Unload myFrm (Leave this out)
Set myFrm = Nothing

Thanks.

 

[Tony Jollans]

Loading and unloading forms is really quite complex and the behaviour is not
quite the same in all Word versions.

What I presume is meant by "magic forms support." is the implicit
instantiation of userforms on first reference. A Userform definition is an
object definition - it contains the Form and a Class Module. When you have a
UserForm in your project you also have, in effect, this module-level
declaration ...

Dim UserForm1 As New UserForm1

Which declares (and tells VBA to load on first use) an object variable,
called UserForm1, of type UserForm1. You can explicitly code this if you
like

Whenever you reference Userform1, if it does not point to an actual
UserForm1 object, one is automatically created. You can, if you like make
the instantiation slightly more obvious by using "Load UserForm1" but that
doesn't really do anything. This, for example:

Load UserForm1
Msgbox UserForm1.Frame1.Caption

is the same as this:

Msgbox UserForm1.Frame1.Caption

If you don't do the Load explicitly, it is automaticaly done for you. The
userform's Initialize event is fired when the userform is loaded.

You can override the implicit instantiation if you want To stop the default
declaration being provided you can explicitly code:

Dim UserForm1 As UserForm1 ' Without the New keyword

Now if you do try and execute either of the above bits of code, they will
fail with "Object variable not set". You must first do:

Set UserForm1 = New UserForm1

after which you can do either of the above but, again, the explicit Load
does nothing. Note, however, that "Load UserForm1" is not the same as "Set
UserForm1 As New UserForm1"; you cannot code:

Dim UserForm1 As UserForm1
Load UserForm1

The Load (even though it does nothing) cannot be executed until the UserForm
object has been created. All this behaviour (apart from the implicit
declaration) is pretty much analagous to what happens with other object
types - consider:

Dim myDoc As New Document
myDoc.Range.InsertAfter "I was automagically created"

Now, if you choose to use a different name for your UserForm1 object
variable, you can use:

Dim myForm As UserForm1

or:

Dim myForm As New UserForm1

The behaviour of working with myForm will be exactly the same as the
behaviour working with UserForm1 above.

This post seems to be quite long enough so I'll leave Unload / Set to
Nothing for later

 

[Jonathan West]

Loading and unloading forms is really quite complex and the behaviour is
not
quite the same in all Word versions.

What I presume is meant by "magic forms support." is the implicit
instantiation of userforms on first reference. A Userform definition is an
object definition - it contains the Form and a Class Module. When you have
a
UserForm in your project you also have, in effect, this module-level
declaration ...

Dim UserForm1 As New UserForm1

More accurately this

Public UserForm1 As New UserForm1

This hidden public declaration is generally called the default instance, but
called the magic form by some.

Which declares (and tells VBA to load on first use) an object variable,
called UserForm1, of type UserForm1. You can explicitly code this if you
like

Whenever you reference Userform1, if it does not point to an actual
UserForm1 object,

Better to keep the terminology straight. It loads the default instance
(called UserForm1) of the UserForm1 object.

one is automatically created. You can, if you like make
the instantiation slightly more obvious by using "Load UserForm1" but that
doesn't really do anything. This, for example:

Load UserForm1
Msgbox UserForm1.Frame1.Caption

is the same as this:

Msgbox UserForm1.Frame1.Caption

If you don't do the Load explicitly, it is automaticaly done for you. The
userform's Initialize event is fired when the userform is loaded.

You can override the implicit instantiation if you want To stop the
default
declaration being provided you can explicitly code:

Dim UserForm1 As UserForm1 ' Without the New keyword

I would strongly recommnend that you never explicitly declare a variable
with the same name as the default instance. While it will work within the
routine which contains the declaration, there is tremendous scope for
confusion since the default instance will still work from any other routine.
It will be preety much impossible to know whether the specific or the
default instance is in use.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Tony Jollans]

Hi Jonathan,

More accurately this

Public UserForm1 As New UserForm1

Agreed, Public UserForm1 ... is more correct than Dim UserForm1 ... I
should have made this clearer.

Better to keep the terminology straight. It loads the default instance
(called UserForm1) of the UserForm1 object.

I deliberately didn't want to differentiate between the default instance
and non-default ones. The behaviour of both is the same - only in the case
of the default one there is, effectively, a hidden declaration.

I would strongly recommnend that you never explicitly declare a variable
with the same name as the default instance. While it will work within the
routine which contains the declaration, there is tremendous scope for
confusion since the default instance will still work from any other routine.
It will be preety much impossible to know whether the specific or the
default instance is in use.

It is not good practice to declare variables with the same name both at
module level and procedure level. That has got nothing to do with UserForms.

As already said, I should have been clearer about the scope of the default
instance.

 

[Tony Jollans]

I would strongly recommnend that you never explicitly declare a variable

It is not good practice to declare variables with the same name both at
module level and procedure level. That has got nothing to do with UserForms.

As already said, I should have been clearer about the scope of the default
instance.

Sorry, Jonathan, that reads as a bit abrupt - it was not meant that way. I
never meant to suggest that a name be used for more than one instance but I
can see how that could perhaps arise from what I wrote. Your warning is a
good reminder that it is important to understand the scope of names -
especially 'hidden' ones - and to avoid writing code that will confuse
humans even if the machine understands perfectly well.

 

[Jonathan West]

Sorry, Jonathan, that reads as a bit abrupt - it was not meant that way. I
never meant to suggest that a name be used for more than one instance but
I
can see how that could perhaps arise from what I wrote. Your warning is a
good reminder that it is important to understand the scope of names -
especially 'hidden' ones - and to avoid writing code that will confuse
humans even if the machine understands perfectly well.

It didn't seem abrupt to me, but you have touched on something I regard as
important, but which is too often forgotten by programmers.

Source code serves two quite separate purposes

1. Instructions to be executed by the computer once the code has been
compiled.

2. A message to yourself (or your successor) as to what the program is
intended to do and why, so that when you revisit the code for bug-fixing or
update, you can find your way around.

A large proportion of the recommendations about good coding practice are
about this second point - making the code easy to understand and therefore
to maintain. It seems to me that many people read the recommendations,
accept them without understanding the underlying objective, and therefore
embrace them with a religious fervor.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org