Hi,



I am trying to change the current month date through Form Control (Scroll Bar).

But after setting up when I click on the scroll bar the data changes to 1990. How can I fix it to this month, next month and so on.....

File is attached for reference.

Thanks

Attached Files

Change Date.xlsx (10.5 KB, 4 views)

The reason you're getting year 1900 is that Excel may show a date as 1/30/1900 or 3/15/2022, but it is an Excel date value of a number. Try typing 0, 20000, and 45677 in cells, then applying the date number format, and they will become 1/0/1900, 10/3/1954 and 1/20/2025 respectively. Your scroll data is set up to scroll between 1 and 31. Excel is translating that as a date to 1/1/1900 through 1/31/1900.

The hitch is that in a normal scroll bar there is a limit of 1 to 30,000, so to get to the dates for this month (44835 to 44865) you can't use the default range limits, but you'll have to use values in cells to give you the range you want.

I found this little YouTube video helpful in visualizing the process: https://www.youtube.com/watch?v=EFr0rRxrTBc&t=107s

If you always want your sheet to stay in the current month, somewhere off to the side outside the print/display area, you might want to set up the following:

First cell - let's say it's cell O3, return today's date =TODAY()
Second cell, return the first day of the current month =EOMONTH(O3,-1)+1
Third cell, return the last day of the current month =EOMONTH(O3,0)

Then point your scroll bar to the min in cell P3, and the max in Q3.

You could, of course, do all that in one formula, but this way it will give you a display of all three values for you to review.

Attached Images

dates.jpg (19.0 KB, 10 views)

Thanks for the solution..

But I can't point the max and min to a cell. I can only give the value as I am using Form Control not Active X control. If you can set up in excel and shared that it would be really helpful.