Stop Macro button moving with cells

trevorc · #1 09-19-2018, 02:33 PM

Hi Again,
I can't seem to be able to stop my macro buttons from moving with the active sheet. I'd like some of them to stay were I put them while others scroll/move with the cells. I have tried setting the properties to Don't move or size with cells, but it doesn't seem to do anything. Any ideas to point me in the right direction would be great.

jeffreybrown · #2 09-19-2018, 02:37 PM

I think your best bet is to include a sample sheet where we can see this happening. I can't duplicate your problem.

P.S. Have you considered adding a custom toolbar?

trevorc · #3 09-19-2018, 03:08 PM

Thanks for the reply,
What I'm trying to do is have a macro button used in place of having to select the sort button of a table then selecting the sort option, I just need it to be A-Z or Z-A, this part works fine. I will in the future make this a dynamic action on the currently selected column, but only if I can get the button to stay in the active window area and not scroll/move with the cells as I scroll left/right, up/down, by using the scroll bars or by moving cells using the arrow keys.
My code so far...

Code:

Sub Sort_AZ()
        t = ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text
     If ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text = "A" Then
        ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text = "Z"
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Add2 Key:=Range("Clipsal_Customers[[#All],[Status]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects( _
          "Clipsal_Customers").Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With
        Exit Sub
      Else
        ActiveSheet.Shapes("sortAZ").TextFrame.Characters.Text = "A"
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects("Clipsal_Customers"). _
        Sort.SortFields.Add2 Key:=Range("Clipsal_Customers[[#All],[Status]]"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Clipsal Customer").ListObjects( _
          "Clipsal_Customers").Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
        End With
      End If
      
End Sub

jeffreybrown · #4 09-19-2018, 03:27 PM

It sounds like you are saying you want this button to float on top of the spreadsheet while the user scrolls around. I don't know of a way to make this happen, but why not put the button above the freeze panes! Row 1 and 2

trevorc · #5 09-19-2018, 03:46 PM

I just found this snippet, I'll play around with this for a while, the button moves with respect to the current cursor position, I may be able to improve on this later.

Code:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
With ActiveSheet.Shapes("sortAZ")
    .Top = Target.Offset(1).Top
    .Left = Target.Offset(, 1).Left
End With
...

jeffreybrown · #6 09-19-2018, 03:59 PM

I'm glad you found something.

Maybe one of our more experienced VBA members will see this thread and help if possible.

trevorc · #7 09-19-2018, 04:07 PM

I should be able to figure out how to make the button move but not be stuck to the active cell. thanks for the help.

Logit · #8 09-20-2018, 11:50 AM

.
Here is one example you can dissect :

Code:

#If VB7 Then
Private Declare PtrSafe Function FindWindow Lib "user32" _
        Alias "FindWindowA" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long

Private Declare PtrSafe Function GetWindowLong Lib "user32" _
        Alias "GetWindowLongA" ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long) As Long

Private Declare PtrSafe Function SetWindowLong Lib "user32" _
        Alias "SetWindowLongA" ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) As Long
#Else
Private Declare Function FindWindow Lib "user32" _
        Alias "FindWindowA" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLong Lib "user32" _
        Alias "GetWindowLongA" ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" _
        Alias "SetWindowLongA" ( _
        ByVal hWnd As Long, _
        ByVal nIndex As Long, _
        ByVal dwNewLong As Long) As Long
#End If
Const GWL_STYLE = -16
Const WS_SYSMENU = &H80000

Private Sub CommandButton1_Click()
MsgBox "You clicked !!!", vbExclamation, "Active Cell Address: " & ActiveCell.Address
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim hWnd As Long, lStyle As Long
If Val(Application.Version) >= 9 Then
    hWnd = FindWindow("ThunderDFrame", Me.Caption)
Else
    hWnd = FindWindow("ThunderXFrame", Me.Caption)
End If
lStyle = GetWindowLong(hWnd, GWL_STYLE)
SetWindowLong hWnd, GWL_STYLE, (lStyle And Not WS_SYSMENU)
End Sub

Logit · #9 09-20-2018, 11:52 AM

.
Here is another. It is called from ThisWorkbook_Open module. You should also
close it in ThisWorkbook_BeforeClose module ...

Code:

Option Explicit

Private eTime
Sub ScreenRefresh()
    With ThisWorkbook.Worksheets("Sheet1").Shapes(1)
        .Left = ThisWorkbook.Windows(1).VisibleRange(2, 2).Left
        .Top = ThisWorkbook.Windows(1).VisibleRange(2, 2).Top
    End With
End Sub

Sub StartTimedRefresh()
    Call ScreenRefresh
    eTime = Now + TimeValue("00:00:01")
    Application.OnTime eTime, "StartTimedRefresh"
End Sub

Sub StopTimer()
    Application.OnTime eTime, "StartTimedRefresh", , False
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I stop check boxes moving rows when text wraps in an adjacent cell?	PatrickYork	Excel	2	04-04-2018 11:23 AM
Cannot get macro button to sort with merged cells on sheet	lonniepoet	Excel Programming	3	02-04-2016 04:33 PM
data in some cells moving	Sherriann	Excel	1	08-07-2013 06:52 AM
How to stop letter body moving up and down	alankempster	Mail Merge	2	09-13-2012 08:09 AM
Moving formula range multiple cells when moving sum over one cell	FraserKitchell	Excel	4	02-26-2010 10:38 AM