Explanation of code

W

WLMPilot

I posted a question last month for a problem I was having with Setfocus. The
answer I got was something I had not seen before. I am teaching myself VBA
as I go along. I would appreciate it if someone could explain what the code
is doing so I can better understand it.

The problem I was having is when a user would enter an incorrect value in a
textbox, I would display a msg for incorrect entry. I would then execute the
following:

Textbox1 = ""
Textbox1.Setfocus 'to allow user to reenter correct value.

Problem is that the next textbox on userform received the setfocus. If you
need to see my code, let me know. Below is the answer I received.

***THIS IS RESPONSE TO MY QUESTION****
Enter Event of any Control Object occurs before a control actually receives
the focus from a control on the same form. Exit occurs immediately before a
control loses the focus to another control on the same form.

So a possible solution to your problem might be something like this:

Place all code behind your form with this line “Dim MyCancel As Boolean†at
the top outside any procedure.

Dim MyCancel As Boolean

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = MyCancel
MyCancel = False
End Sub

Replace the code Textbox1="" and Textbox1.Setfocus with:
MyCancel = True
TextBox1 = vbNullString

My questions concerning the new code I was given is this:
1) I did not know you could place code outside a subroutine. In this case,
Dim MyCancel As Boolean is by itself. What does this do?

2) What is the macro for Cancel = Mycancel doing?

3) What is being triggered with MyCancel = True?

4) Isn't Textbox1 = "" the same as Textbox1 = vbNullString?

I appreciate any help in explaning this code and what is going on.

Thanks,
Les
 
B

Bob Phillips

WLMPilot said:
I posted a question last month for a problem I was having with Setfocus.
The
answer I got was something I had not seen before. I am teaching myself
VBA
as I go along. I would appreciate it if someone could explain what the
code
is doing so I can better understand it.

The problem I was having is when a user would enter an incorrect value in
a
textbox, I would display a msg for incorrect entry. I would then execute
the
following:

Textbox1 = ""
Textbox1.Setfocus 'to allow user to reenter correct value.

Problem is that the next textbox on userform received the setfocus. If
you
need to see my code, let me know. Below is the answer I received.

***THIS IS RESPONSE TO MY QUESTION****
Enter Event of any Control Object occurs before a control actually
receives
the focus from a control on the same form. Exit occurs immediately before
a
control loses the focus to another control on the same form.

So a possible solution to your problem might be something like this:

Place all code behind your form with this line "Dim MyCancel As Boolean"
at
the top outside any procedure.

Dim MyCancel As Boolean

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = MyCancel
MyCancel = False
End Sub

Replace the code Textbox1="" and Textbox1.Setfocus with:
MyCancel = True
TextBox1 = vbNullString

My questions concerning the new code I was given is this:
1) I did not know you could place code outside a subroutine. In this
case,
Dim MyCancel As Boolean is by itself. What does this do?
Click to expand...


That is declaring a variable which has module scope rather than just the
procedure scope, so it is allowed.

2) What is the macro for Cancel = Mycancel doing?
Click to expand...


It is setting the procedure's Cancel argument to a value that is set
elsewhere and is captured in that MyCancel variable.

3) What is being triggered with MyCancel = True?
Click to expand...


That is setting that variable which is used elsewhere.

4) Isn't Textbox1 = "" the same as Textbox1 = vbNullString?
Click to expand...


Yeah, but vbNullString more clearly states what is happening, better than
embedded constants.
 
L

Luke M

1) yes, you can define parameters outside of subroutines. This is handy if
you want to declare global variables (so that you can pass info from one sub
to another). Another common declarations are "Option explicit" - all
variables must be defined.

Thus, by defining the global parameter of MyCancel as a Boolean, whenever a
value is assigned to MyCancel, it retains that value even when the subroutine
ends.

2) Thus, the Cancel routine is checking the value of MyCancel (which would
be set to true in your other routine). If MyCancel is 'true', then
cancellation proceeds. The MyCancel = false is to reset the parameter so that
you don't automatically cancel the form should that Cancel subroutine get
called again.

3) This is tied into #2. Doesn't really trigger anything, it just causes the
Cancel subroutine to proceed when activated.

4) Pulling up the VB help file on "vbNullString" we get this definitions:
"Not the same as a zero-length string (""); used for calling external
procedures"

So, while similar, it's not exactly the same thing.

As a fellow self-taught user, if anyone else wants to expand/correct on
these explainations, please do.
 
J

JLGWhiz

4) Isn't Textbox1 = "" the same as Textbox1 = vbNullString?
Click to expand...


Yeah, but vbNullString more clearly states what is happening, better than
embedded constants.


While this is generally true, it is technically not true that they are the
same. vbNullString has a value of 0 while "" has a value of 1. So it
depends a lot on how you use them. i.e. IsEmpty vs Value = .
 
J

Jarek Kujawa

how do you check that?
on my Excel 2003 Val(vbNullString) and Val("") are both 0
 
J

JLGWhiz

The Val function returns the numeric value of a string variable. The code
value of a constant is a different animal. You find the code values in
tables within the VBA help files or programming manuals.



how do you check that?
on my Excel 2003 Val(vbNullString) and Val("") are both 0
 
P

Peter T

JLGWhiz said:
Yeah, but vbNullString more clearly states what is happening, better than
embedded constants.


While this is generally true, it is technically not true that they are the
same. vbNullString has a value of 0 while "" has a value of 1. So it
depends a lot on how you use them. i.e. IsEmpty vs Value = .
Click to expand...

I don't follow that value 0 and 1 stuff, and IsEmpty(some VB string) is
never empty. In VB/A "" and vbNullString are functionally equivalent when
writing to a variable.

vbNullString was introduced to be able to pass a null pointer to an external
DLL function via a String parameter, so in that context indeed not same as
"".

Regards,
Peter T

PS, The only posts in this thread currently visible to the OP, and anyone
else using the Communities web interface, are his own and Luke's (which is
ticked as the correct answer). Maybe the others will get propagated in due
course.
 
P

Peter T

"Peter T" wrote in message
PS, The only posts in this thread currently visible to the OP, and anyone
else using the Communities web interface, are his own and Luke's (which is
ticked as the correct answer). Maybe the others will get propagated in due
course.
Click to expand...

OK, a backlog of about a weeks worth of NNTP posts are now getting through
to WebNews within the last hour or two.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SetFocus problem 3
Set focus on controls of modeless form 4
Set focus to textbox after spin-button control is pressed?? 3
TextBox1.SetFocus not responding 2
Two types of textbox input 1
validation of textbox entries 3
SetFocus problem 7
UserForm Textbox.setfocus help 4

Top