Excel - Scroll Right & Reposition Shape 1

[SkipVought]

Hi Gang,

I have a Table where I am freezing the left n columns.

I have a control in a column > n.

I have a button to Scroll Right using ActiveWindow.LargeScroll ToRight:=1. I ALSO want to REPOSITION the control in the window in the same relative position as it was; ie if the window scrolls p pixels, I want to incriment the Left property by p.

How do I determine p?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

Here's where I'm at FYI.

I am freezing column 1
I have 3 labels, one is in COL 1 - lblProduct
I have 4 comboboxes, one is in COL 1 - cbxProduct
I have 4 command buttons for navigation

Sub ScrollRight()
    ActiveWindow.LargeScroll ToRight:=1
    RepositionControlsInActiveWindow True
End Sub
Sub ScrollLeft()
    ActiveWindow.LargeScroll toleft:=1
    RepositionControlsInActiveWindow False
End Sub
Sub RepositionControlsInActiveWindow(rght As Boolean)
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        With sh
            Select Case .Name
                Case "lblProduct", "cbxProduct"
                Case Else
                    If rght Then
                        .Left = .Left + ActiveWindow.UsableWidth - Cells(1, 1).EntireColumn.Width
                    Else
                        .Left = .Left - ActiveWindow.UsableWidth + Cells(1, 1).EntireColumn.Width
                    End If
            End Select
        End With
    Next
End Sub

Using this code has the following problems...

1. I get a GHOST button
2. the ScrollRight scrolls a bit too far to the right.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

Finally solved it!

Sub ScrollRight()
    Dim nLeft As Single
    nLeft = ActiveWindow.VisibleRange.Cells(1, 2).Left
    ActiveWindow.LargeScroll ToRight:=1
    RepositionControlsInActiveWindow True, nLeft
End Sub
Sub ScrollLeft()
    Dim nLeft As Single
    nLeft = ActiveWindow.VisibleRange.Cells(1, 2).Left
    ActiveWindow.LargeScroll toleft:=1
    RepositionControlsInActiveWindow False, nLeft
End Sub
Sub RepositionControlsInActiveWindow(rght As Boolean, nLeft As Single)
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        With sh
            Select Case .Name
                Case "lblProduct", "cbxProduct"
                Case Else
                    x = .Left - nLeft
                    If rght Then
                        .Left = ActiveWindow.VisibleRange.Cells(1, 2).Left + x
                    Else
                        .Left = ActiveWindow.VisibleRange.Cells(1, 2).Left + x
                    End If
            End Select
        End With
    Next
End Sub

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Skip,

I can't help with the ghosting - does it make a difference if you set screenupdating off while your code runs?

I started to post a response about the positioning and then saw your last post. Only difference was I was going to suggest using the ActiveWindow.ScrollColumn before and after the scroll to allow for the partial column included in UsableWidth, but your solution is neater


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SkipVought]

Tony,

Now I am trying to determine how many columns I have frozen.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi Skip,

ActiveWindow.SplitColumn

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SkipVought]

Thanx, Tony!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com