Userform Control Focus Loop

Art3mis9

New Member
Joined
Jul 31, 2014
Messages
17
Hi,

I have a userform that I want to work in a way that focused control have a different back colour, making it obvious to the user which field they are in. I know I can apply this on a control-by-control basis, but I hate that manual stuff. It's inelegant.

I've come up with a loop structure. It's not throwing up any errors, but it's not working either. Maybe the code is in the wrong place.

Code:
Dim ctl As MSFORMS.Control   
For Each ctl In Me.Controls
      Select Case TypeName(ctl)
         Case "TextBox"
           If (ctl Is ActiveControl) Then
           ctl.BackColor = &H500050
           End If
         Case "ComboBox"
           If (ctl Is ActiveControl) Then
                ctl.BackColor = &H500050
           End If
      End Select
   Next ctl

Any idea would be greatly appreciated.

Art3mis9
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sounds like you don't know whether the code is running, or whether it is running but not doing what you want it to do.

Stick a break point in it (cursor in the Select Case line then hit F9), and you will know when the code runs and hits that line. Then hit F8 to step through the code a line at a time and see whether each If statement does as expected.

If you have the Watch window visible at the same time then you can monitor how your variables change over time, or you could use the Immediate window to ask specific questions at any time, or use debug.print to report these values in real time. E.g. after the select case line try adding
Code:
debug.print ctl.name, activecontrol.name
 
Upvote 0
Maybe this :

Code in the userform module:
Code:
Option Explicit

Private bClosing As Boolean

Private Sub UserForm_Activate()

    [COLOR=#008000][B]'Run any existing code here before the loop !![/B][/COLOR]
        
    Dim oPrevCtrl As Control
    Dim lPrevColor As Long
    
    Do
        If oPrevCtrl Is ActiveControl Then
            GoTo Nxt
        ElseIf Not oPrevCtrl Is Nothing Then
            oPrevCtrl.BackColor = lPrevColor
        End If
        
        Set oPrevCtrl = ActiveControl
        lPrevColor = ActiveControl.BackColor
        ActiveControl.BackColor = &H500050
        
Nxt:
        DoEvents
    Loop Until bClosing

End Sub

Private Sub UserForm_Terminate()
    bClosing = True
End Sub

The code will need some tweaking if you have controls inside a frame or Multipage.
 
Upvote 0
Use te following improved alternative for a more generic code that highlights the current active control that has the focus ... This now includes controls located inside frames as well.

Code:
Option Explicit

Private bClosing As Boolean

Private Sub UserForm_Activate()

   [B][COLOR=#008000] 'Run any existing code here before the loop !![/COLOR][/B]
    
    On Error Resume Next
    
    Dim oPrevCtrl As Control
    Dim lPrevColor As Long
    
    Do
        If oPrevCtrl Is RealActiveControl Then
            GoTo Nxt
        ElseIf Not oPrevCtrl Is Nothing Then
            oPrevCtrl.BackColor = lPrevColor
        End If
        
        Set oPrevCtrl = RealActiveControl
        lPrevColor = RealActiveControl.BackColor
        RealActiveControl.BackColor = &H500050
        
Nxt:
        DoEvents
    Loop Until bClosing

End Sub


Private Sub UserForm_Terminate()
    bClosing = True
End Sub


Private Function RealActiveControl() As Control
    Dim oControl As Object
    
    On Error Resume Next
    Set oControl = ActiveControl
    Do
        Set oControl = CallByName(oControl, "ActiveControl", VbGet)
        DoEvents
    Loop Until Err.Number <> 0
    Set RealActiveControl = oControl
End Function
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,943
Messages
6,175,552
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top