-
Feb 6th, 2006, 12:47 PM
#1
Thread Starter
Frenzied Member
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 03:35 PM.
Blessings in abundance,
All the Best,
& ENJOY!
Art . . . . Carlisle, PA . . USA
-
Feb 6th, 2006, 01:00 PM
#2
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
-
Feb 6th, 2006, 01:54 PM
#3
Thread Starter
Frenzied Member
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
-
Feb 6th, 2006, 02:07 PM
#4
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!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Feb 6th, 2006, 02:45 PM
#5
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:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.CommandButton1.Left = Target.Left + Target.Width
Me.CommandButton1.Top = Target.Top
End Sub
Declan
Don't forget to mark your Thread as resolved.
Take a moment to rate posts that you think are helpful
-
Feb 6th, 2006, 03:32 PM
#6
Thread Starter
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|