can command button move with scrolling?

[bhandari]

if iam scrolling my sheets i want to move my command button to keep visible while scrolling
is it possible?

 

[My Aswer Is This]

You can try this:
When you click on any cell the command button will be moved to where you click.

So scroll down then click a cell and presto your command button has moved.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Change the name of the Command Button in the script to meet your needs

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Shapes("CommandButton1").Top = ActiveCell.Top
ActiveSheet.Shapes("CommandButton1").Left = ActiveCell.Left
End Sub

 

[My Aswer Is This]

Or try this:

Any time you double click on a cell your command button will now be moved to the cell you double clicked on.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
ActiveSheet.Shapes("CommandButton1").Top = ActiveCell.Top
ActiveSheet.Shapes("CommandButton1").Left = ActiveCell.Left
End Sub

 

[bhandari]

first time click to edit cell,double click to call command button right..might be helpful in some places

is it not possible with scrolling bar (Bottom Right)?

 

[My Aswer Is This]

I do not know of any way to do it by just using scroll bar.
Now we could have command button be placed one cell to the right or left of active cell like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
ActiveSheet.Shapes("CommandButton1").Top = ActiveCell.Top
ActiveSheet.Shapes("CommandButton1").Left = ActiveCell.Offset(0, 1).Left
End Sub

first time click to edit cell,double click to call command button right..might be helpful in some places

is it not possible with scrolling bar (Bottom Right)?

 

[bhandari]

Thank you.. this will be helpful

 

[Logit]

.
In a routine module paste this macro :

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

In ThisWorkbook paste this :

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTimer
End Sub


Private Sub Workbook_Open()
StartTimedRefresh
End Sub

These settings will specify where the upper left corner of the button will rest after each scroll :

 .Left = ThisWorkbook.Windows(1).VisibleRange(2, 2).Left
        .Top = ThisWorkbook.Windows(1).VisibleRange(2, 2).Top

 

[Eric W]

You could make the top row tall enough to hold your button(s). Then go to the View tab > Freeze Panes > Freeze top row, and now the button will stay visible as you scroll up and down. Depends on how you have your sheet organized.

Another option, depending on what version of Excel you have, is to add the button to the ribbon. In 2012, click File > Options > Customize Ribbon > choose Commands from: Macros > and click the macro that your button points to. In the right panel, select the tab you want to add it to (or create a new tab), and click Add.

 

[bhandari]

it looks like each time i have to run module?

this is not tiggering

 

[Logit]

.
Download example: https://www.amazon.com/clouddrive/share/o6gG4kZLJKMmiucND6YTrgN8H0wBl41RAMkAbeE2001