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?
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.
We can arrive at the shape like so:
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