WiseOwl Excel
asked on
Previous Tab or Previous Control Focus VBA Excel Help
Hello Experts,
How to Activate Previous Control Set focus if Previous Control is Empty
is There Any Universal Code to Paste into All Controls Change Event
Regards
How to Activate Previous Control Set focus if Previous Control is Empty
is There Any Universal Code to Paste into All Controls Change Event
Regards
Sorry but there’s no universal change event, and if you want to execute common code for all change events you should create a macro in a code module and call if from every control. Note that you can use ActiveControl.Name to identify the control that’s been changed.
ASKER
Respected Sir,
How to Get Value from Previous Control to ActiveControl???
Regards
How to Get Value from Previous Control to ActiveControl???
Regards
Hi,
Have a control type variable declared on form level.
At the end of each control's change event, set the variable to ActiveControl.
Have a control type variable declared on form level.
At the end of each control's change event, set the variable to ActiveControl.
ASKER
I Have text box with name txtName and then txtPassport and txtInvAmount i want at change event of txtPassport Text Box Change it will If Empty it will Go to txtName Auto and if on change event of txtInvAmount if txtPassport Empty then again it will go and set focus to txtPassport
i want this Dynamically with Help of Active control while manually i have done this as on Change event with If Condition i made it already but i want some function of Universal for All Controls available in form i have approximately 120 Controls in form.
Regards,
i want this Dynamically with Help of Active control while manually i have done this as on Change event with If Condition i made it already but i want some function of Universal for All Controls available in form i have approximately 120 Controls in form.
Regards,
There is no universal function for all controls.
Please try the userform in this workbook.
29075757.xlsm
Please try the userform in this workbook.
29075757.xlsm
ASKER
Respected Sir,
How It is Configuring Previous Control Name??? Please Guide i didnt get how this is working mstrPrevControlName
Regrads,
How It is Configuring Previous Control Name??? Please Guide i didnt get how this is working mstrPrevControlName
Regrads,
Simply by changing the control's properties in design mode. Basic stuff.
I strongly advice that you document yourself on the basic of VBA programming and forms design if you don't know that.
(variables, loops, decisional statement and forms design is a bare minimum).
I strongly advice that you document yourself on the basic of VBA programming and forms design if you don't know that.
(variables, loops, decisional statement and forms design is a bare minimum).
I added the following code to your userform.
Line 1 defines a String variable that I use to store the name of the previous control. Since it is at the top of the form's code (in what is called the Declarations section) it can be used anywhere in the form.
Lines 4, 8, 11 and 13 call the Sub named WhoAmI
Line 24 is a mistake and it shouldn't be there so remove it.
Lines 26 to 34 are the WhoAmI sub. In that sub take a look at line33 first. Each time the sub is called that line places the name of the current control (called the ActiveControl) in the mstrPrevControlName variable, replacing what's there (if anything).
Line 30 displays the name of the current control along with mstrPrevControlName which as I've stated is the name of the now previous control.
Line 28 is there because the first time the sub is called, mstrPrevControlName will not contain any value.
Line 1 defines a String variable that I use to store the name of the previous control. Since it is at the top of the form's code (in what is called the Declarations section) it can be used anywhere in the form.
Lines 4, 8, 11 and 13 call the Sub named WhoAmI
Line 24 is a mistake and it shouldn't be there so remove it.
Lines 26 to 34 are the WhoAmI sub. In that sub take a look at line33 first. Each time the sub is called that line places the name of the current control (called the ActiveControl) in the mstrPrevControlName variable, replacing what's there (if anything).
Line 30 displays the name of the current control along with mstrPrevControlName which as I've stated is the name of the now previous control.
Line 28 is there because the first time the sub is called, mstrPrevControlName will not contain any value.
Private mstrPrevControlName As String
Private Sub TextBox1_Change()
WhoAmI
End Sub
Private Sub TextBox2_Change()
WhoAmI
End Sub
Private Sub TextBox3_Change()
WhoAmI
End Sub
Private Sub TextBox4_Change()
WhoAmI
End Sub
Private Sub TextBox5_Change()
WhoAmI
End Sub
Private Sub TextBox6_Change()
WhoAmI
End Sub
End Sub
Private Sub WhoAmI()
If mstrPrevControlName = "" Then
MsgBox "I am " & ActiveControl.Name
Else
MsgBox "I am " & ActiveControl.Name & " previous control was " & mstrPrevControlName
End If
mstrPrevControlName = ActiveControl.Name
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Respcted Sir,
Code Working Fine and Exactly what i want but still i am confused that mstrPrevControlName is not an object so how it can be empty and how this is giving previous control name???
Please kindy Help me to Understand I owe you in htis regard,
Regards,
Code Working Fine and Exactly what i want but still i am confused that mstrPrevControlName is not an object so how it can be empty and how this is giving previous control name???
Please kindy Help me to Understand I owe you in htis regard,
Regards,
ASKER
How This is Storing Previous Control Name Means Please Explain "Private mstrPrevControlName As String" Rest All Code I Have Understand with you Kind Awesome Explanation.
"Line 1 defines a String variable that I use to store the name of the previous control. Since it is at the top of the form's code (in what is called the Declarations section) it can be used anywhere in the form."
"Line 1 defines a String variable that I use to store the name of the previous control. Since it is at the top of the form's code (in what is called the Declarations section) it can be used anywhere in the form."
VBA provides what's called the Controls Collection which is a grouping of all the controls, regardless of type, on a userform. (There are other types of collections as well.) You can access the members of a collection in two ways and the first way is by referring to a control's position (Index) in the collection, so for example if you had two textboxes, one could be referred to as Controls(0) and the other as Controls(1). The second way is by the control's name. This gives you several ways to refer to any given control. For example all of these are the same:
In any case You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
If Textbox1.Text = "blah" Then
If Controls(0).Text = "blah" Then
If Controls("Textbox1").Text = "blah" Then
' This one works because mstrPrevControlName is a String
If Controls(mstrPrevControlName).Text = "blah" Then
In any case You’re welcome and I’m glad I was able to help.
If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
If you still have questions about this, please ask.
ASKER
Respetced Sir@ Martin Lis,
Still I am Confused with how this Code Configuring mstrPrevControlName Previous Name on the change of Current Textbox.
there was no Collection Loop Working according to my Knowledge.
Please kindly Help me a bit more to understand.
Regards
Still I am Confused with how this Code Configuring mstrPrevControlName Previous Name on the change of Current Textbox.
there was no Collection Loop Working according to my Knowledge.
Please kindly Help me a bit more to understand.
Regards
There is no Collection loop. In the following, I've documented the 'Validate' sub.
Private Sub Validate()
' Check to see if mstrPrevControlName has anything in it
If mstrPrevControlName <> "" Then
' It's not blank so look at the control in the 'Controls' collection that has
' the name that's stored' in the mstrPrevControlName string. (This
' is like my 4th example above)
If Controls(mstrPrevControlName) = "" Then
' The previous control (who's name is stored in mstrPrevControlName) is empty,
' so display the message...
MsgBox mstrPrevControlName & " can't be empty"
' ...and set focus to the previous textbox
Controls(mstrPrevControlName).SetFocus
End If
End If
' Replace the value in mstrPrevControlName with the name of the current
' control so that it will be available when the next control gets focus
mstrPrevControlName = ActiveControl.Name
End Sub
ASKER
Thanks A loot For Such Positive and Awesome Explanation... I Owe You in this Regard as i learned aloot....!!!