Can I lock an object in place, so it stays when user scrolls?

[wojo]

I have searched and read all the help files. I find the properties of
an object, I see how I can "lock", "size and move with cells" or "not
move with cells". No matter what I select, the object moves off the
screen, when the user, scrolls to the right of the spreadsheet.

Is there a way to lock the position, let's say , in the upper right
corner and have it stay there?

This would be quite useful for an EXIT button, that I have created,
that will close the program without saving (it's a read-only file.)

Thanks to all the wonderful people here that have been so helpful and
give us their valuable insight and time.

Jo

 

[Debra Dalgleish]

You could place a button at the top left of the worksheet.
Then select cell B2, and choose Window>Freeze Panes

As you scroll, the button will remain visible

 

[Dave Peterson]

You could readjust the position each time the user changes selection.

But for things like this, you may want to consider using a toolbar that floats
over the screen and can be positioned by the user.

If I want to add a toolbar of my own, here's how I do it:
http://groups.google.co.uk/[email protected]

=========

If you really want the button to move to the top left corner of the visible
screen, you can do it with something like:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("putthenamehere")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

(use the real name of the button instead of "putthenamehere")

Rightclick on the worksheet that should have this behavior. Select view code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you may want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the user moves the
screen using the scroll bars, then it won't show up until they select a range.

 

[wojo]

Somehow, I knew that you would be here to help. I will take a look at
your solutions. I currently am already using the freeze option and it
won't work for my button.

Sounds like this might do the trick. I will have to play with this, as
I am using the freeze option, but it freezes row 17.

Thanks again and again and again!!

Jo

 

[wojo]

Somehow, I knew that you would be here to help. I will take a look at
your solutions. I currently am already using the freeze option and it
won't work for my button.

Sounds like this might do the trick. I will have to play with this, as
I am using the freeze option, but it freezes row 17.

Thanks again and again and again!!

Jo

 

[Dave Peterson]

I'm not sure why the freeze panes doesn't work for you.

Make sure you remove the existing freeze panes.
Show all the rows (if any are hidden/filtered)
Make sure you can see A1 on the screen
select B2 and then
window|freeze panes.

Then column A and Row 1 will always be visible. And A1 will never leave the
visible area.

 

[wojo]

The freeze panes will work, it just isn't feasible because of where I
am currently freezing panes.

I have tried both your solutions and the Toolbar works best. I love
how it stays whereever I have put it, even when the user scrolls.

Thank you!

See you around, I'm sure <g>.

Jo

 

[wojo]

The freeze panes will work, it just isn't feasible because of where I
am currently freezing panes.

I have tried both your solutions and the Toolbar works best. I love
how it stays whereever I have put it, even when the user scrolls.

Thank you!

See you around, I'm sure <g>.

Jo

 

[wojo]

Dave, thanks for the "how to" make a toolbar. This works great. Is
there a way to create the toolbar, so that there is NO option of
'remove buttons/add buttons' , also, delete the ability to close the
toolbar? I'm afraid that someone will close the toolbar, leave the
program running and the next person will not realize that it is
missing.

Jo

 

[Dave Peterson]

The nice think about the toolbar is that it's deleted each time you close the
workbook and rebuilt each time you open the workbook. So if one user closes it,
changes it, deletes it, then it won't affect anyone else--in fact, it won't
affect that user the next time he/she opens the workbook.

But you could stop them by adding a line:

With Application.CommandBars.Add
'...keep that stuff, and add this
.Protection = msoBarNoChangeVisible + msoBarNoCustomize

For i = LBound(mac_names) To UBound(mac_names)
'rest of code

 

[wojo]

Your toolbar solutions work great. I have everything working just the
way I want, thanks to you.

Jo

 

[Dave Peterson]

Glad you got it working nicely.

Your toolbar solutions work great. I have everything working just the
way I want, thanks to you.

Jo