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
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