Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Floating buttons (Excel XP)

    I think I've seen Excel spreadsheets where Buttons that activate macros float on the screen as you scroll the worksheet. That is, they stay where the are relative to the computer screen as versus traveling up/down etc. with the worksheet.

    If I'm correct about this can someone tell me how to set a button to do that? In Excel help I've only been able to find something called TURNING ON AND OFF TRANSPARENT FLOATING TOOLBARS, and for that the specfied option in the CUSTOMIZE TOOLBARS tab doesn't appear.

    It's possible I'm competely mistaken about this and the feature is only available in Word......it at all.

    Thanks,
    BH Davis

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Hi There

    This website should help you http://www.contextures.com/xlToolbar02.html and has a working example....enjoy
    Jerry

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Thanks Jerry.

    I read through it and it seems I would do just as well adding a custom button to the existing main toolbar at the top of my screen. The only small negative is that it would be there all the time, even when running other worksheets. That of course is not big deal.

    However, if I went the route of creating the floating toolbar as described in the article it seems I'd have to turn it on and off when I needed the functions on that toolbar. That would be a pain.

    Is there a floating button option available as versus a floating toolbar?

    Thanks,
    BH Davis

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Ummm, I was mistaken on the correct terminology here. What I was calling a floating BUTTON would actually be a floating CONTROL COMMAND BUTTON created from the CONTROL toolbar. Took me a while to track down how had created the thing a long time ago.

    So, can I make that float?

    Thanks,
    BH Davis

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating buttons (Excel XP)

    A very easy solution:
    - Put the command button over cell A1.
    - Resize row 1 and column A so that the command button fits in A1.
    - Select cell B2.
    - Select Window | Freeze Panes.
    Cell A1 (and hence the command button) will now stay visible wherever you scroll.

    To make the command button move with you:
    Right-click the sheet tab and select View Code from the popup menu.
    Create code like this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Me.CommandButton1
    .Left = Target.Left + Target.Width + 10
    .Top = Target.Top
    .Width = 72
    End With
    End Sub

    The command button will not move as you scroll, but it will follow the current selection: when you select a different cell or range of cells, the command button will be displayed just to the right of the selection.

    Note: Excel doesn't have built-in On Scroll events, so if you'd want to monitor scrolling, you'd have to resort to using Windows API functions; this becomes quite complicated.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Thank you Hans. I'll give it a go later this morning.
    BH

  7. #7
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Hans,

    I was able to get both methods to work with one exception.

    On the second option where the control button jumps to the side of each cell selected I can't figure out how to assign a macro to the newly created button. I like that jumping button method but of course need to get the macro assigned somehow.

    On the first method where the button stays in cell A1 and the rest of the worksheet scrolls by, I got it to work with one of my existing control buttons that already has the macro assigned. However, when I create a new control button I'm back to where I was and can't get a macro assigned.

    On my old existing command buttons I get an ASSIGN MACRO option when I right click on them. I don't get that with the newly created command button.

    Thanks,
    BH

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating buttons (Excel XP)

    Excel has two kinds of command buttons: from the Forms toolbar and from the Control Toolbox toolbar.

    To assign a macro to a command button from the Forms toolbar, you right-click the button and select Assign Macro from the popup menu.

    For a command button from the Control Toolbox, it works slightly differently. You must turn on design mode using the first button on the Control Toolbox. Then double click the command button. This will create the On Click event procedure for the button, in which you can write the code to be executed, or call an existing macro.

    Since you mentioned "a floating CONTROL COMMAND BUTTON created from the CONTROL toolbar", I assumed that you meant the latter type.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Hans,

    You assumed right.............I described wrong!

    Thanks,
    BH

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Hans,

    Thanks again. I actually was very close. I had entered the macro code "below" the code that made the button jump around. I also didn't have:

    Private Sub CommandButton1_Click()

    as the first line of the macro code, which probably kept it from working.

    Just curious about one other thing. The button size is fine, but if I happen to enlarge the width it reverts back to a default width when it moves to a new location. This doesn't seem to happen with the height though. It's not a problem, just something I noticed happening.

    Thanks again........you're the BEST !!!

    BH

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating buttons (Excel XP)

    The Worksheet_SelectionChange resets the width of the button to 72 points (1 inch). I did this because if you would ever select a cell in the last column, IV, Excel will reduce the width of the button to 0 because it would otherwise fall off the sheet. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    You can change the number 72 in the code, or if you never move near the right edge of the worksheet, you can remove or comment out the line

    .Width = 72

    The button will then keep the width you give it.

  12. #12
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Thanks! Makes sense. I should have looked more closely at your code and figured that out (or at least all but the right hand side of the sheet issue!).

    Anyway, one final small glitch. Look at the code:





    ' The following is the macro command for the PASTE NAMES button.

    Private Sub CommandButton1_Click()
    Selection.Copy
    Windows("AAINVOICE.xls").Activate
    ActiveSheet.Range("E9").Select
    ActiveSheet.Paste
    Windows("aaNames.xls").Activate
    Range("A1").Select

    Windows("AAINVOICE.xls").Activate
    ActiveSheet.Shapes("Button 41").Select
    Selection.Cut
    ActiveSheet.Range("I9").Select
    Windows("aaNames.xls").Close
    SendKeys "{N}" .................................................. ...............HERE
    End Sub

    ' The following makes the PASTE NAMES button move around the sheet.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Me.CommandButton1
    .Left = Target.Left + Target.Width + 10
    .Top = Target.Top
    .Width = 72
    End With
    End Sub








    Look to the left of where I added "..................................HERE" and you'll see a SendKeys {"N"} command. Everything is working fine excpet for this line. The SendKeys command works elsewhere that I've used it but doesn't automatcially close out the SAVE window here and I have to click on NO on the save window.

    Thanks,
    BH

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating buttons (Excel XP)

    Replace

    Windows("aaNames.xls").Close
    SendKeys "{N}"

    with

    Windows("aaNames.xls").Close SaveChanges:=False

    This tells Excel to close the window without displaying the prompt to save the workbook.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Floating buttons (Excel XP)

    Hans, thanks yet again.

    Unrelated, is there a way to lock the BORDER on a group of cells so when you copy/paste info into them the border remains even though there was no matching border on the cells you copied from? PASTE, SPECIAL, VALUES does it but that makes the process a bit more complicated.

    Thanks,
    BH

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Floating buttons (Excel XP)

    As far as I know, you will have to use PasteSpecial. Standard Copy / Paste copies all aspects of a cell, including borders.

Page 1 of 2 12 LastLast

Posting Permissions

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