excelvbatextboxcontrols

'Jump to' text box that is off the screen


I have an Excel worksheet that has a good number of rows - so it's pretty long. There are text boxes and buttons scattered throughout.

I set the tab order and that works, except for one problem. If the next button or text box is off the screen, the user must scroll to it and then click on it.

I've tried .activate, .setfocus, .select, everything I can think of on tab events, keydown events, even clicking.

How do I "jump to" a control that is off screen?


Solution

  • I THINK what you're asking is how to scroll your rows and columns to focus in on a shape. This can be done.
    If, however, you're looking to move a shape to where the user is, just basically follow the steps backwards.
    On the other hand, if I've missed the point of this exercise entirely, my apologies.

    I'm starting with this shape far off screen:
    I've named it "StarHead" because I found it amusing.
    enter image description here

    If we're set at cell "A1",
    enter image description here

    We can arrive at the shape like so:
    enter image description here

    if we run:

    Sub ScrollToObject()
        
        Dim StarHead As Object
        Dim TLCell As Range
        
        Set StarHead = ActiveSheet.Shapes("Starhead")
        Set TLCell = StarHead.TopLeftCell
        
        ActiveWindow.ScrollRow = TLCell.Row - 3
        ActiveWindow.ScrollColumn = TLCell.Column - 3
        
    End Sub
    

    As a further bit of fun, I wrote a bit of code to loop trough every shape on a sheet. Kind of ugly, but it works. If it's something you want to use, it would be easy to clean up.

    Sub ScrollToNextObject()
        
        Dim MyShape As Object
        Dim TLCell As Range
        Dim SHIndex As Long
        
        Dim SHP As Object
        Dim Dict As Object
        Dim CNT As Long
        Dim N As Byte
        Dim I As Long
        
        If TypeName(Selection) = "Range" Then
            SHIndex = 1
            Set MyShape = ActiveSheet.Shapes(SHIndex)
        Else
            Set Dict = CreateObject("scripting.dictionary")
        
            I = 0
            For N = 1 To 2
                For Each SHP In ActiveSheet.Shapes
                    I = I + 1
                    Debug.Print SHP.ID
                    If ActiveSheet.Shapes(Selection.Name).ID = SHP.ID Then CNT = I - 1
                    Dict.Add I, SHP.ID
                Next SHP
            Next N
            
            For Each SHP In ActiveSheet.Shapes
                If Dict(CNT) = SHP.ID Then
                    Set MyShape = SHP
                End If
            Next SHP
            
            Debug.Print ActiveSheet.Shapes(Selection.Name).ID
            SHIndex = Dict(CNT)
            
        End If
        
        Set TLCell = MyShape.TopLeftCell
        
        ActiveWindow.ScrollRow = TLCell.Row
        ActiveWindow.ScrollColumn = TLCell.Column
        MyShape.Select
    
    End Sub
    

    enter image description here
    enter image description here
    enter image description here