Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-19-2018, 02:33 PM
trevorc trevorc is offline Stop Macro button moving with cells Windows 7 32bit Stop Macro button moving with cells Office 2013
Competent Performer
Stop Macro button moving with cells
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default Stop Macro button moving with cells

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.
Reply With Quote
  #2  
Old 09-19-2018, 02:37 PM
jeffreybrown jeffreybrown is offline Stop Macro button moving with cells Windows Vista Stop Macro button moving with cells Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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?
Reply With Quote
  #3  
Old 09-19-2018, 03:08 PM
trevorc trevorc is offline Stop Macro button moving with cells Windows 7 32bit Stop Macro button moving with cells Office 2013
Competent Performer
Stop Macro button moving with cells
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default Dynamic moving sort button

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
Attached Files
File Type: xlsm new Workshop .xlsm (94.4 KB, 7 views)
Reply With Quote
  #4  
Old 09-19-2018, 03:27 PM
jeffreybrown jeffreybrown is offline Stop Macro button moving with cells Windows Vista Stop Macro button moving with cells Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

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
Reply With Quote
  #5  
Old 09-19-2018, 03:46 PM
trevorc trevorc is offline Stop Macro button moving with cells Windows 7 32bit Stop Macro button moving with cells Office 2013
Competent Performer
Stop Macro button moving with cells
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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
...
Reply With Quote
  #6  
Old 09-19-2018, 03:59 PM
jeffreybrown jeffreybrown is offline Stop Macro button moving with cells Windows Vista Stop Macro button moving with cells Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

I'm glad you found something.

Maybe one of our more experienced VBA members will see this thread and help if possible.
Reply With Quote
  #7  
Old 09-19-2018, 04:07 PM
trevorc trevorc is offline Stop Macro button moving with cells Windows 7 32bit Stop Macro button moving with cells Office 2013
Competent Performer
Stop Macro button moving with cells
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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.
Reply With Quote
  #8  
Old 09-20-2018, 11:50 AM
Logit Logit is offline Stop Macro button moving with cells Windows 10 Stop Macro button moving with cells Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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
Attached Files
File Type: xlsm Button Floating.xlsm (18.5 KB, 6 views)
Reply With Quote
  #9  
Old 09-20-2018, 11:52 AM
Logit Logit is offline Stop Macro button moving with cells Windows 10 Stop Macro button moving with cells Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

.
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
Attached Files
File Type: xlsm Button Follow While Scrolling.xlsm (21.1 KB, 9 views)
Reply With Quote
Reply

Thread Tools
Display Modes


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
Stop Macro button moving with cells 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
Stop Macro button moving with cells How to stop letter body moving up and down alankempster Mail Merge 2 09-13-2012 08:09 AM
Stop Macro button moving with cells Moving formula range multiple cells when moving sum over one cell FraserKitchell Excel 4 02-26-2010 10:38 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:53 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft