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
WiseOwl ExcelFinance ExxcutiveAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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.
0
WiseOwl ExcelFinance ExxcutiveAuthor Commented:
Respected Sir,

How to Get Value from Previous Control to ActiveControl???

Regards
0
Fabrice LambertFabrice LambertCommented:
Hi,

Have a control type variable declared on form level.

At the end of each control's change event, set the variable to ActiveControl.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

WiseOwl ExcelFinance ExxcutiveAuthor Commented:
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,
0
Martin LissOlder than dirtCommented:
There is no universal function for all controls.

Please try the userform in this workbook.
29075757.xlsm
0
WiseOwl ExcelFinance ExxcutiveAuthor Commented:
Respected Sir,

How It is Configuring Previous Control Name??? Please Guide i didnt get how this is working mstrPrevControlName

Regrads,
0
Fabrice LambertFabrice LambertCommented:
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).
0
Martin LissOlder than dirtCommented:
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.

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

Open in new window

0
Martin LissOlder than dirtCommented:
You asked
How to Activate Previous Control Set focus if Previous Control is Empty

Here's how.
Option Explicit
Private mstrPrevControlName As String
Private Sub TextBox1_Enter()
Validate
End Sub
Private Sub TextBox2_Enter()
Validate
End Sub
Private Sub TextBox3_Enter()
Validate
End Sub
Private Sub TextBox4_Enter()
Validate
End Sub
Private Sub TextBox5_Enter()
Validate
End Sub
Private Sub TextBox6_Enter()
Validate
End Sub
Private Sub Validate()
If mstrPrevControlName <> "" Then
    If Controls(mstrPrevControlName) = "" Then
        MsgBox mstrPrevControlName & " can't be empty"
        Controls(mstrPrevControlName).SetFocus
    End If
End If
mstrPrevControlName = ActiveControl.Name
End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WiseOwl ExcelFinance ExxcutiveAuthor Commented:
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,
0
WiseOwl ExcelFinance ExxcutiveAuthor Commented:
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."
0
Martin LissOlder than dirtCommented:
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:
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

Open in new window


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
1
Martin LissOlder than dirtCommented:
If you still have questions about this, please ask.
0
WiseOwl ExcelFinance ExxcutiveAuthor Commented:
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
0
Martin LissOlder than dirtCommented:
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

Open in new window

1
WiseOwl ExcelFinance ExxcutiveAuthor Commented:
Thanks A loot For Such Positive and Awesome Explanation... I Owe You in this Regard as i learned aloot....!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.