Event Reference


M

meh2030

I have two generic questions for which I will elaborate specifically
and for which I will provide code where necessary: (1) does Excel have
a way to reference an event name (i.e. the text to the left of the “_”
and (2) how well does Excel keep track of the "Me." construct for
UserForms?

(1) My UserForm name: “frmGradeElementary” / My TextBox names:
“txtExpDesign1”; “txtExpDesign2”; etc.

I created a UserForm which has multiple pages and each page has
multiple text boxes. The user inserts values into the text boxes and
upon submitting the form, the values are output to the spreadsheet;
however, the values are tested for accuracy via a Function nested in
the event (see code below). This way, the user receives immediate
feedback if an incorrect value is inserted into the text box. Since I
will be creating the Exit event for a number of text boxes, is there
an easy way to reference the …_Exit name (i.e. the text to the left of
the “_”)? For example, in the Exit event below, the line “score =
validate10(frmGradeElementary.txtExpDesign1.Value)” will be repeated
for …txtExpDesign2.Value, …txtExpDesign3.Value, etc. I know that I
could replace “frmGradeElementary” with “Me” to read
“Me.txtExpDesign1” but is there a similar replacement (like “Me”) for
the “txtExpDesign1”? (Maybe something like “Me.ThisEvent.Value”?).

Option Explicit
Dim blnScore As Boolean

Private Sub txtExpDesign1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
blnScore = validate10(frmGradeElementary.txtExpDesign1.Value)
If Not score Then
MsgBox "You input an incorrect value." & vbCr _
& "The value must be a number and between 0 and 10."
Cancel = True
End If
End Sub

Function validate10(number) As Boolean
If (IsNumeric(number) And number <= 10 And number >= 0) Then
validate10 = True
End If
End Function

(2) I know that “Me” will reference a respective class (e.g. in
ThisWorkbook “Me” refers to ThisWorkbook, and in a UserForm “Me”
refers to the UserForm), but does “Me” get confused? For example, if
I have multiple UserForms and when one UserForm (frmA) is used to show
another UserForm (frmB), will Excel keep track of “Me” if it is used
in both frmA and frmB?

I’m curious because if I build a base UserForm (frmA), copy frmA,
rename the copied frmA as frmB, and then make slight modifications to
frmB, then it may be easier to deal with code changes that have
“Me.xzy” rather than replace frmA.xyz with frmB.xyz after frmA is
copied. (I’m anticipating that referencing the form name is a much
better practice than using “Me” (i.e. it’s better to use frmA.xyz and
frmB.xyz than to use the generic Me.xyz)).

Thanks,

Matthew Herbert
 
Ad

Advertisements

B

Bob Phillips

Me always refers to the containing class, and only the class, it will not
refer to a control on a userform say.

I have never had a problem using Me, with multiple userforms. I fail to see
how a problem can arise as the Me construct can only be used within the form
code itself, it cannot be referenced by Me outside of the form code.

--
__________________________________
HTH

Bob

I have two generic questions for which I will elaborate specifically
and for which I will provide code where necessary: (1) does Excel have
a way to reference an event name (i.e. the text to the left of the “_”
and (2) how well does Excel keep track of the "Me." construct for
UserForms?

(1) My UserForm name: “frmGradeElementary” / My TextBox names:
“txtExpDesign1”; “txtExpDesign2”; etc.

I created a UserForm which has multiple pages and each page has
multiple text boxes. The user inserts values into the text boxes and
upon submitting the form, the values are output to the spreadsheet;
however, the values are tested for accuracy via a Function nested in
the event (see code below). This way, the user receives immediate
feedback if an incorrect value is inserted into the text box. Since I
will be creating the Exit event for a number of text boxes, is there
an easy way to reference the …_Exit name (i.e. the text to the left of
the “_”)? For example, in the Exit event below, the line “score =
validate10(frmGradeElementary.txtExpDesign1.Value)” will be repeated
for …txtExpDesign2.Value, …txtExpDesign3.Value, etc. I know that I
could replace “frmGradeElementary” with “Me” to read
“Me.txtExpDesign1” but is there a similar replacement (like “Me”) for
the “txtExpDesign1”? (Maybe something like “Me.ThisEvent.Value”?).

Option Explicit
Dim blnScore As Boolean

Private Sub txtExpDesign1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
blnScore = validate10(frmGradeElementary.txtExpDesign1.Value)
If Not score Then
MsgBox "You input an incorrect value." & vbCr _
& "The value must be a number and between 0 and 10."
Cancel = True
End If
End Sub

Function validate10(number) As Boolean
If (IsNumeric(number) And number <= 10 And number >= 0) Then
validate10 = True
End If
End Function

(2) I know that “Me” will reference a respective class (e.g. in
ThisWorkbook “Me” refers to ThisWorkbook, and in a UserForm “Me”
refers to the UserForm), but does “Me” get confused? For example, if
I have multiple UserForms and when one UserForm (frmA) is used to show
another UserForm (frmB), will Excel keep track of “Me” if it is used
in both frmA and frmB?

I’m curious because if I build a base UserForm (frmA), copy frmA,
rename the copied frmA as frmB, and then make slight modifications to
frmB, then it may be easier to deal with code changes that have
“Me.xzy” rather than replace frmA.xyz with frmB.xyz after frmA is
copied. (I’m anticipating that referencing the form name is a much
better practice than using “Me” (i.e. it’s better to use frmA.xyz and
frmB.xyz than to use the generic Me.xyz)).

Thanks,

Matthew Herbert
 
M

meh2030

Me always refers to the containing class, and only the class, it will not
refer to a control on a userform say.

I have never had a problem using Me, with multiple userforms. I fail to see
how a problem can arise as the Me construct can only be used within the form
code itself, it cannot be referenced by Me outside of the form code.

--
__________________________________
HTH

Bob


I have two generic questions for which I will elaborate specifically
and for which I will provide code where necessary: (1) does Excel have
a way to reference an event name (i.e. the text to the left of the “_”
and (2) how well does Excel keep track of the "Me." construct for
UserForms?

(1) My UserForm name: “frmGradeElementary” / My TextBox names:
“txtExpDesign1”; “txtExpDesign2”; etc.

I created a UserForm which has multiple pages and each page has
multiple text boxes.  The user inserts values into the text boxes and
upon submitting the form, the values are output to the spreadsheet;
however, the values are tested for accuracy via a Function nested in
the event (see code below).  This way, the user receives immediate
feedback if an incorrect value is inserted into the text box.  Since I
will be creating the Exit event for a number of text boxes, is there
an easy way to reference the …_Exit name (i.e. the text to the left of
the “_”)?  For example, in the Exit event below, the line “score =
validate10(frmGradeElementary.txtExpDesign1.Value)” will be repeated
for …txtExpDesign2.Value, …txtExpDesign3.Value, etc.  I know that I
could replace “frmGradeElementary” with “Me” to read
“Me.txtExpDesign1” but is there a similar replacement (like “Me”)for
the “txtExpDesign1”?  (Maybe something like “Me.ThisEvent.Value”?).

Option Explicit
Dim blnScore As Boolean

Private Sub txtExpDesign1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
blnScore = validate10(frmGradeElementary.txtExpDesign1.Value)
If Not score Then
    MsgBox "You input an incorrect value." & vbCr _
        & "The value must be a number and between 0 and 10."
    Cancel = True
End If
End Sub

Function validate10(number) As Boolean
If (IsNumeric(number) And number <= 10 And number >= 0) Then
    validate10 = True
End If
End Function

(2) I know that “Me” will reference a respective class (e.g. in
ThisWorkbook “Me” refers to ThisWorkbook, and in a UserForm “Me”
refers to the UserForm), but does “Me” get confused?  For example, if
I have multiple UserForms and when one UserForm (frmA) is used to show
another UserForm (frmB), will Excel keep track of “Me” if it is used
in both frmA and frmB?

I’m curious because if I build a base UserForm (frmA), copy frmA,
rename the copied frmA as frmB, and then make slight modifications to
frmB, then it may be easier to deal with code changes that have
“Me.xzy” rather than replace frmA.xyz with frmB.xyz after frmA is
copied.  (I’m anticipating that referencing the form name is a much
better practice than using “Me” (i.e. it’s better to use frmA.xyz and
frmB.xyz than to use the generic Me.xyz)).

Thanks,

Matthew Herbert

Bob,

Thanks for the explaination for "Me." Do you know if there is a way
to solve my question regarding the events (i.e. my "(1)" question)?
I've been searching around, but haven't had any luck finding
information related to this.

Thanks,

Matt
 
B

Bob Phillips

No, you can't because there is no familiar form of the control. But you
could reduce it to

Option Explicit

Private Sub txtExpDesign1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not validate10(Me.txtExpDesign1) Then
MsgBox "You input an incorrect value." & vbCr _
& "The value must be a number and between 0 and 10."
Cancel = True
End If
End Sub

Function validate10(Byref ctl As Object) As Boolean
With ctl
validate10 = IsNumeric(.Value) And .Value <= 10 And .Value >= 0)
End With
End Function

If your concern were with multiple controls, you could use a control array,
but unfortunately the Exit event is exposed in this manner.

--
__________________________________
HTH

Bob

Me always refers to the containing class, and only the class, it will not
refer to a control on a userform say.

I have never had a problem using Me, with multiple userforms. I fail to
see
how a problem can arise as the Me construct can only be used within the
form
code itself, it cannot be referenced by Me outside of the form code.

--
__________________________________
HTH

Bob


I have two generic questions for which I will elaborate specifically
and for which I will provide code where necessary: (1) does Excel have
a way to reference an event name (i.e. the text to the left of the “_”
and (2) how well does Excel keep track of the "Me." construct for
UserForms?

(1) My UserForm name: “frmGradeElementary” / My TextBox names:
“txtExpDesign1”; “txtExpDesign2”; etc.

I created a UserForm which has multiple pages and each page has
multiple text boxes. The user inserts values into the text boxes and
upon submitting the form, the values are output to the spreadsheet;
however, the values are tested for accuracy via a Function nested in
the event (see code below). This way, the user receives immediate
feedback if an incorrect value is inserted into the text box. Since I
will be creating the Exit event for a number of text boxes, is there
an easy way to reference the …_Exit name (i.e. the text to the left of
the “_”)? For example, in the Exit event below, the line “score =
validate10(frmGradeElementary.txtExpDesign1.Value)” will be repeated
for …txtExpDesign2.Value, …txtExpDesign3.Value, etc. I know that I
could replace “frmGradeElementary” with “Me” to read
“Me.txtExpDesign1” but is there a similar replacement (like “Me”) for
the “txtExpDesign1”? (Maybe something like “Me.ThisEvent.Value”?).

Option Explicit
Dim blnScore As Boolean

Private Sub txtExpDesign1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
blnScore = validate10(frmGradeElementary.txtExpDesign1.Value)
If Not score Then
MsgBox "You input an incorrect value." & vbCr _
& "The value must be a number and between 0 and 10."
Cancel = True
End If
End Sub

Function validate10(number) As Boolean
If (IsNumeric(number) And number <= 10 And number >= 0) Then
validate10 = True
End If
End Function

(2) I know that “Me” will reference a respective class (e.g. in
ThisWorkbook “Me” refers to ThisWorkbook, and in a UserForm “Me”
refers to the UserForm), but does “Me” get confused? For example, if
I have multiple UserForms and when one UserForm (frmA) is used to show
another UserForm (frmB), will Excel keep track of “Me” if it is used
in both frmA and frmB?

I’m curious because if I build a base UserForm (frmA), copy frmA,
rename the copied frmA as frmB, and then make slight modifications to
frmB, then it may be easier to deal with code changes that have
“Me.xzy” rather than replace frmA.xyz with frmB.xyz after frmA is
copied. (I’m anticipating that referencing the form name is a much
better practice than using “Me” (i.e. it’s better to use frmA.xyz and
frmB.xyz than to use the generic Me.xyz)).

Thanks,

Matthew Herbert

Bob,

Thanks for the explaination for "Me." Do you know if there is a way
to solve my question regarding the events (i.e. my "(1)" question)?
I've been searching around, but haven't had any luck finding
information related to this.

Thanks,

Matt
 
M

meh2030

No, you can't because there is no familiar form of the control. But you
could reduce it to

Option Explicit

Private Sub txtExpDesign1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not validate10(Me.txtExpDesign1) Then
    MsgBox "You input an incorrect value." & vbCr _
        & "The value must be a number and between 0 and 10."
    Cancel = True
End If
End Sub

Function validate10(Byref ctl As Object) As Boolean
 With ctl
  validate10 = IsNumeric(.Value) And .Value <= 10 And .Value >= 0)
 End With
End Function

If your concern were with multiple controls, you could use a control array,
but unfortunately the Exit event is exposed in this manner.

--
__________________________________
HTH

Bob













Bob,

Thanks for the explaination for "Me."  Do you know if there is a way
to solve my question regarding the events (i.e. my "(1)" question)?
I've been searching around, but haven't had any luck finding
information related to this.

Thanks,

Matt- Hide quoted text -

- Show quoted text -

Thanks again; I appreciate the help.

Matt
 
Ad

Advertisements

Ad

Advertisements


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

Top