dear all,

i have a command button in excel sheet and i want it to move and hold
its place even if the window is scrolled ( i want it to be visible on
the screen when the window is scrolled). Normally,
it stays where you have placed it and disappears when you scroll the
window.

i appreciate your help in this regard,

thanks,
Naweed

One method.

Freeze a row or two at top of sheet and place the button above that freeze line.


Gord Dibben MS Excel MVP

On Wed, 08 Aug 2007 20:57:35 -0700, User wrote:

dear all,

i have a command button in excel sheet and i want it to move and hold
its place even if the window is scrolled ( i want it to be visible on
the screen when the window is scrolled). Normally,
it stays where you have placed it and disappears when you scroll the
window.

i appreciate your help in this regard,

thanks,
Naweed

On Aug 9, 8:04 am, Gord Dibben <gorddibbATshawDOTca wrote:
One method.

Freeze a row or two at top of sheet and place the button above that freeze line.

Gord Dibben MS Excel MVP



On Wed, 08 Aug 2007 20:57:35 -0700, User wrote:
dear all,

i have a command button in excel sheet and i want it to move and hold
its place even if the window is scrolled ( i want it to be visible on
the screen when the window is scrolled). Normally,
it stays where you have placed it and disappears when you scroll the
window.

i appreciate your help in this regard,

thanks,
Naweed- Hide quoted text -

- Show quoted text -

Thanks,

but i want to do it with VBA , freezing isn't ideal for the sheet i m
using.

regards,
Naweed

yes, i snatched some coding off the internet that does exactly that.
unfortunately i don't think i have it here at work........
aha! i found it!

'=============================
'revised 6/28/07 - added the commandbutton movement
'as below - original code did not dim variables

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'by Orlando Magalhães Filho in .programming

Dim x_offset As Long
Dim y_offset As Long
Dim xpos As Long
Dim ypos As Long

'revised 7/16/07
'added a random control controlling this action,
'doing it every time there was a worksheet_change
'was too slow & cumbersome.

If Range("k702") = 4 Then

Application.ScreenUpdating = False

x_offset = 10
y_offset = 10

ActiveSheet.Shapes("CommandButton1").Select

With Cells(ActiveWindow.Panes(1).ScrollRow, _
ActiveWindow.Panes(1).ScrollColumn)
' xpos = .Right + x_offset THERE IS NO .RIGHT PROPERTY
xpos = .Left + x_offset
ypos = .Top + y_offset
End With

With Selection
.Left = xpos
.Top = ypos
End With

Target.Select

Application.ScreenUpdating = True

End If

End Sub
'============================

i made a few modifications - since i didn't want this worksheet change
to fire completely every single time there was a change, i placed

=RAND()*5
in k701 and
=ROUND(K701,0)
in k702

the macro checks that cell (k702), & if it doesn't equal 4 it just
exits - much quicker, and yet it does update the command button to my
current location fairly often. also, adjust the x, y offsets to suit
- for me i set these to 2.
hope it helps!
susan




On Aug 9, 12:15 am, User wrote:
On Aug 9, 8:04 am, Gord Dibben <gorddibbATshawDOTca wrote:





One method.

Freeze a row or two at top of sheet and place the button above that freeze line.

Gord Dibben MS Excel MVP

On Wed, 08 Aug 2007 20:57:35 -0700, User wrote:
dear all,

i have a command button in excel sheet and i want it to move and hold
its place even if the window is scrolled ( i want it to be visible on
the screen when the window is scrolled). Normally,
it stays where you have placed it and disappears when you scroll the
window.

i appreciate your help in this regard,

thanks,
Naweed- Hide quoted text -

- Show quoted text -

Thanks,

but i want to do it with VBA , freezing isn't ideal for the sheet i m
using.

regards,
Naweed- Hide quoted text -

- Show quoted text -

You could FREEZE the Window Pane just below the button, this way the button will remain in view at
all times, no matter how far you scroll down??


Corey....
"User" wrote in message
ups.com...
dear all,

i have a command button in excel sheet and i want it to move and hold
its place even if the window is scrolled ( i want it to be visible on
the screen when the window is scrolled). Normally,
it stays where you have placed it and disappears when you scroll the
window.

i appreciate your help in this regard,

thanks,
Naweed

It sounds like you may be better using a new toolbar (in xl2003 and below).

User wrote:

dear all,

i have a command button in excel sheet and i want it to move and hold
its place even if the window is scrolled ( i want it to be visible on
the screen when the window is scrolled). Normally,
it stays where you have placed it and disappears when you scroll the
window.

i appreciate your help in this regard,

thanks,
Naweed

--

Dave Peterson