Link to home
Start Free TrialLog in
Avatar of WiseOwl Excel
WiseOwl ExcelFlag for Pakistan

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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.
Avatar of WiseOwl Excel

ASKER

Respected Sir,

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.
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,
There is no universal function for all controls.

Please try the userform in this workbook.
29075757.xlsm
Respected Sir,

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

ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,
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."
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
If you still have questions about this, please ask.
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
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

Thanks A loot For Such Positive and Awesome Explanation... I Owe You in this Regard as i learned aloot....!!!