From cells, auto-sum, formula ranges, regular expressions, combination of data found in different sheets or workbooks, to drop-down lists, Excel offers you everything.
This is just to name a few, there are still plenty of features and tools that may be hiding, which will make your job easier and liven up your experience.
If that is your situation, you have a pivot table or chart and you don't want it to appear that extended in the program, wanting to implement or set an object on it, the ideal would be to use a ActiveX scroll bar.
You probably think you need a program other than Microsoft Excel to use this tool, but no! As complex as it may sound, here you will learn how to access this feature.
Utility of the bar
The ActiveX scroll bar will help you to move through a range of values simply by clicking on the arrows that will show this bar or by moving the scroll box that will be between those arrows.
How to include it in your Excel file
insert or draw the ActiveX scroll bar in your Excel file, you must first note that it should be linked to one of the cells. To proceed to draw the scroll bar it is necessary to follow the instructions below:
- You should have the card Developer available on the Microsoft Excel ribbon.
- If you don't have it available, you just need to right click one of the tabs you have, click customize ribbon, check the box Developer And that's it.
- Once you have entered the previously indicated form, click on the section Inserisci, represented by a box with some tools.
- This will bring up a menu consisting of two parts: form controls and ActiveX controls.
- Click on scrollbar and proceed with the drawing.
One of the best features of this feature, like most Microsoft Excel tools, is that they are very customizable, you don't need more precision.
In this sense, you can change its size, draw them horizontally and vertically, long or short, thick or thin. You must only adapt them to the place you need.
Connecting the bar to a cell
- You need to link the scroll bar to one of the cells in the Excel file. To do this, you need to:
- Double-click on the scroll bar, which will automatically take you to the tab Developer.
- Select the option Property located right next to the section Design mode.
- Link the cell in the property LinkedCell.
- Click on the section LinkedCell and in it you will enter the letter and number of the cell where you will connect the scroll bar (for example F8).
Setting other properties
The procedure doesn't end there, it is need to set also the property that appear in the sections Min e Max. That is, it indicates the minimum and maximum control values for the scroll bar.
At the minimum, you could position, for example: 1. While, at the maximum, you could position: 10. This bar is also deeply customizable, so you can change its configuration regarding the name, the colors, the way the bar does the jumps based on the clicks you can make.
This function is recognized by the section SmallChange, which, by default, will be 1. But you can change it to do 2 by 2 or 3 by 3 jumps.
Now that you know how to use the ActiveX scroll bar in Excel to move between tables, you may be interested in learning the best and most useful Excel keyboard shortcuts so that you can increase the efficiency with which you do your work.