How to always floating a Command Button on worksheet in Excel?
When working with large Excel worksheets that require frequent navigation, you may sometimes need certain controls – such as a Command Button (ActiveX Controls) – to always remain visible to improve workflow efficiency. For example, you might want a "Submit", "Refresh", or "Update" button to stay at the top of your view, so you can easily access it no matter how far up or down you scroll within the sheet. By default, Command Buttons are anchored to the cells where they are placed and will move out of view when you scroll, potentially disrupting your work rhythm. This can be inconvenient, especially in interactive dashboards or forms where immediate access to control buttons is necessary.
This tutorial provides a practical method to make an ActiveX Command Button always float and stay visible on the worksheet as you navigate the data. You will be guided through using VBA code to achieve this effect. The solution is suitable for scenarios where a fixed-action button is required for repeated tasks, form submission, dashboard interaction, or navigation within extensive worksheets.
Always floating a Command Button on worksheet with VBA code
Alternative solutions: Simulate a floating button using Excel's Freeze Panes feature
Always floating a Command Button on worksheet with VBA code
To make an ActiveX Command Button always float in your worksheet, allowing it to remain in view no matter where you scroll, follow the steps below. This approach leverages VBA to dynamically reposition the button in response to your navigation within the sheet. It is especially effective in cases where immediate access to a control is essential for usability and workflow. However, please note that this method is only applicable to ActiveX Command Buttons, not Form Control buttons.
1. In the worksheet containing the ActiveX Control Command Button you want to keep floating, right-click the sheet tab (found at the bottom of the Excel window), and then select View Code from the context menu. This action opens the code window specific to that worksheet.
2. In the appearing Microsoft Visual Basic for Applications window, copy and paste the below VBA code into the large code area (Code window) on the right. This script will continuously position your Command Button at a specified spot relative to the worksheet's visible range, typically the upper center as you scroll.
VBA code: Make a Command Button always floating in worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
On Error GoTo 0
With Cells(Windows(1).ScrollRow, Windows(1).ScrollColumn)
CommandButton1.Top = .Top + 100
CommandButton1.Left = .Left + 300
End With
End SubNote: In the above code, CommandButton1 should be replaced with the actual name of the Command Button you wish to keep visible. To check or change this, select the button in Design Mode, and view or edit its (Name) property in the Properties window.
3. Once you have pasted the code, press the Alt + Q keys together to exit the Microsoft Visual Basic for Applications window and return to your worksheet.
Now, whenever you scroll or select different cells, the Command Button will intelligently reposition itself, always staying visible at the upper center of the worksheet's visible area. Below is an example showing the effect, where the Command Button remains accessible even as the data is navigated:
Key details and tips:
- This method only works for ActiveX Control Command Buttons, not for Form Control buttons.
- If you have multiple floating buttons or customized positions, you need to adapt the code accordingly for each button.
- The VBA code is worksheet-specific. If you want this effect on multiple sheets, repeat the steps for each sheet.
Simulate a floating button using Excel's Freeze Panes feature
If you prefer not to use VBA code, you can simulate the effect of a floating Command Button using Excel’s built-in Freeze Panes feature. While this method does not keep the button truly floating, it ensures that a button placed above the frozen panes remains constantly visible as you scroll through your worksheet data below. This is particularly useful in dashboard scenarios or when using a small set of controls in the header area.
- Advantages: No macros required, more stable, and compatible with most Excel environments, including those with macro restrictions.
To use this method, do the following:
- Insert your Command Button (either Form Control or ActiveX Control) in a row at the top of the worksheet, such as row 1 or 2.
- Select the first cell below the header rows you want to remain visible (e.g., cell A2 to freeze row 1).
- Go to the View tab, and click Freeze Panes > Freeze Panes. Now, your button and header rows remain visible as you scroll downward.
While this does not offer true floating behavior, it is a practical, non-programmatic alternative if your needs are simple and macro use is restricted.
Related articles:
- How to save and close active workbook without prompt by a Command Button in Excel?
- How to update or insert (current) date and time by a Command Button in Excel?
- How to use a Command Button to change a specified cell value in Excel?
- How to create a Command Button to copy and paste data in Excel?
- How to use Command Button to save active worksheet as PDF file in Excel?
- How to insert a blank new row automatically by Command Button in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in