#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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
|
#5
|
|||
|
|||
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 ... |
#6
|
|||
|
|||
I'm glad you found something.
Maybe one of our more experienced VBA members will see this thread and help if possible. |
#7
|
|||
|
|||
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.
|
#8
|
|||
|
|||
.
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 |
#9
|
|||
|
|||
.
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 |
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 |
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 |