EXCEL: How To: Reference Command Button Position to WINDOW [RESOLVED]-VBForums
Results 1 to 6 of 6

Thread: EXCEL: How To: Reference Command Button Position to WINDOW [RESOLVED]

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Resolved EXCEL: How To: Reference Command Button Position to WINDOW [RESOLVED]

    Esteemed Forum Participants and Lurkers:
    ===============================
    Excel 2003 VBA

    I want to throw a simple Command Button on a particular worksheet to Call a Macro for that sheet. I do not want the macro button on the tool bar - just on the one sheet.

    I have the button operating and the position of the button is set "Free Floating", but the position is dependend on the SHEET. So, if I scroll the sheet, the button moves also. Is there any simple way to reference the SHEET-SPECIFIC Button position to the WINDOW so that the position is independent of the sheet? That way it should stay put always visible in one location in the window. I did a search on the forum, but I didn't find anything relevant.

    Thank you for any and all comments, suggestions, and assistance.
    Last edited by Webtest; Feb 6th, 2006 at 02:35 PM.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  2. #2
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: EXCEL: How To: Reference Command Button Position to WINDOW???

    One easy way would be to Freeze a pane, either to the left or top the sheet and put the button in that section. That way the user can scroll to their hearts content and the button will never move.
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: EXCEL: How To: Reference Command Button Position to WINDOW???

    Thanks Declan,

    Yes, that works pretty good. Since Column A on my sheet is very narrow, I shortened the Text on the Button to "GO" and froze it in cell "A1" in the top left corner of the sheet. It would still be a problem if I wanted to scroll column A off the left of the sheet, but I'll deal with that if it comes up.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  4. #4
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,128

    Re: EXCEL: How To: Reference Command Button Position to WINDOW???

    You may want to do a right click on the command button and Format Control > Properties > "Move but dont size with cells" > OK. This will scroll the command button left or right as the user resizes columns but not scrolls.

    It may help to get you a little closer to your goal.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Star Wars Gangsta Rap Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel Core 2 Extreme Ed., 2 WD Raptor 10K RPM 300 GB HDs, 2 GBs DDR2 667 MHz RAM, 2 Viewsonic 24" LCDs, Windows 7 SP-1/Windows 8.1, Office 2010, VS 2013

  5. #5
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: EXCEL: How To: Reference Command Button Position to WINDOW???

    How about moving the button every time a cell is selected? This could get really annoying for the user....

    VB Code:
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2.     Me.CommandButton1.Left = Target.Left + Target.Width
    3.     Me.CommandButton1.Top = Target.Top
    4. End Sub
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,044

    Re: EXCEL: How To: Reference Command Button Position to WINDOW???

    Thanks RobDog ... Hey, how 'bout them STEELERS!!!

    I set the Format Control > Properties to "Don't move or size with cells" to keep the control at a fixed reference point on the sheet. It is immune to changes in column width or height. The only problem, which Declan graciously fixed, is that the box scrolls with the page ... Up/Down or Left/Right. If I have a very wide column A and I want to scroll it out of the window, I can't use the "Freeze Pane" technique unlesss I insert a "Dummy" column A. In order to use the Freeze Pane I just need to always keep a column A and row 1 just big enough to accomodate the control. I shifted all of my headers and data down to start in row 2. I just select "B2" and do: ActiveWindow.FreezePanes = True. That puts the button permanently in the Top Left corner of the window.

    Declan ... Your suggestion is interesting, especially if you wanted to do some kind of special operation on the selected range! However, I'll stick with "Freeze Panes" ... I've got that working great now with the button in the Top Left corner of the window. Thanks again.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.