• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 58
  • Last Modified:

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
0
WiseOwl Excel
Asked:
WiseOwl Excel
  • 7
  • 7
  • 2
1 Solution
 
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 ExcelAuthor 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
WiseOwl ExcelAuthor 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 ExcelAuthor 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
 
WiseOwl ExcelAuthor 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 ExcelAuthor 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 ExcelAuthor 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 ExcelAuthor Commented:
Thanks A loot For Such Positive and Awesome Explanation... I Owe You in this Regard as i learned aloot....!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now