npnomad.blogg.se - Why is the scroll bar missing in excel

Why is the scroll bar missing in excel code#

You can also hide the numbers and retain only the years.

Although our formatting is lose, which can certainly improve.And wrap it around IFERROR function for turning off the errors to 0.I will apply a simple INDEX formulato pull up years from sheet 1.Right Click on the Shapes and go to Assign Macroīut we don’t have the years displayed yet.Now Simply Link the Scroll-bars to the Macros And then click on Module (Module is the place where we write macros).Go to the Developer Tab (Press Alt + F11 if you cannot find it).If <= 41 Then = + 1Ĭopy and paste both these macros in the VBA Window. Marco for Right Scroll-Bar Sub custom_scroller_right() Macro for Left Scroll-Bar Sub custom_scroller_left()

Why is the scroll bar missing in excel code#

To apply this logic to the Scroll-bars (that we have made), we need 2 tiny bits of code If the highlighted year is 1 then the previous two cells should be blank.If the highlighted year is 42 then the next two cells should be blank.The highlighted year (as of now 20), can go up to 42 and can go down till 1.There are 42 data points (from 1975 to 2016), correct?.Before we even do that, let me give you a bigger picture first. Now that we have our scrollbars ready, our next job is to link them to the numbers in Sheet 2.

Related : Learn various shapes/objects related shortcuts We can definitely format them more, but for now they are good to go!

And now you have something like scrollbars ready.

Nothing fancy, but I am going to pick up a simple shape and make it look sexy We have also named the middle cell as Highlighted year

On sheet 2, we are writing numbers (and NOT years) because the data could be anything (text, product code, names etc.) for which you want to make a scroll bar, so we are indexing each data point to a number (starting with 1).

In our data we have years from 1975 till 2016 i.e.

20-1 in previous cell and 19 – 1 in previous cell.

20 +1 in the next cell and 21 + 1 in the next cell.

And the scroll-bars to move the years (ahead or backwards).

2 years ahead on the right and 2 years back on the left.

We have a highlighted year in the center.

Our Objective is to make a timeline with customized scroll-bars So we have here some random yearly data (nothing too significant) on Sheet 1. Recommendation : Sit with a latte and enjoy! 😀 In this post I am going to share everything (including pros and cons), about making a customized scroll-bar in Excel. When I got bored with the old scroll-bar and their tricks, I thought of inventing a new one on my own.