+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    wojo
    Guest

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

    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


  2. #2
    Debra Dalgleish
    Guest

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

    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

    wojo wrote:
    > 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
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


  3. #3
    Dave Peterson
    Guest

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

    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/groups?th...5B41%40msn.com

    =========

    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 wrote:
    >
    > 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


    --

    Dave Peterson

  4. #4
    wojo
    Guest

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

    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


  5. #5
    wojo
    Guest

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

    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


  6. #6
    Dave Peterson
    Guest

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

    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 wrote:
    >
    > 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

  7. #7
    wojo
    Guest

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

    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


  8. #8
    Dave Peterson
    Guest

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

    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 wrote:
    >
    > 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

  9. #9
    wojo
    Guest

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

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

    Jo


  10. #10
    Dave Peterson
    Guest

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

    Glad you got it working nicely.

    wojo wrote:
    >
    > Your toolbar solutions work great. I have everything working just the
    > way I want, thanks to you.
    >
    > Jo


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1