Combo boxes with date formula list and linked cells HIGH IMPORTANCE - hope someone can help!

[willow1985]

I need help with getting a combo box that gets it list from a particular formula to relay the selection to a linked cell with the proper value.

I will do my best to explain.

There are 3 Sheets: List, Graph Data and Dashboard

The combo boxes are on the Dashboard sheet. When the user selects the year the lists change accordingly on the List sheet. The Graph Data sheet is where the linked cells to the combo box's are however the linked cells are not displaying the proper year and I do not know why/how to fix this.

Combo Boxes:

List:

Customer Part Analysis Dashboard.xlsm
	C	D	E	F
1	Year List	Month List Start	Month List End
2	2021	January 1, 2021	January 31, 2021	0
3	2020	February 1, 2021	February 28, 2021	1
4	2019	March 1, 2021	March 31, 2021	2
5	2018	April 1, 2021	April 30, 2021	3
6	2017	May 1, 2021	May 31, 2021	4
7		June 1, 2021	June 30, 2021	5
8		July 1, 2021	July 31, 2021	6
9		August 1, 2021	August 31, 2021	7
10		September 1, 2021	September 30, 2021	8
11		October 1, 2021	October 31, 2021	9
12		November 1, 2021	November 30, 2021	10
13		December 1, 2021	December 31, 2021	11
List

Cell Formulas
Range	Formula
D2	D2	=DATE(('Graph Data'!$B$2),1,1)
E2:E13	E2	=EOMONTH(D2,0)
D3:D13	D3	=EDATE($D$2,F3)
C2	C2	=YEAR(TODAY())
C3:C6	C3	=C2-1

Named Ranges
Name	Refers To	Cells
Month_List_Start	=List!$D$2:$D$13	E2, D3:D13
Year_List	=List!$C$2:$C$6	C3

Graph Data:

Customer Part Analysis Dashboard.xlsm
	A	B	C
2	Year Selected	1900
3	Input Date Range	January 1, 1900	January 3, 1900
Graph Data

I hope someone can help. My whole spreadsheet relies on these communicating properly.

 

[willow1985]

I figured it out. I switched to a combo box (active X).
I changed where the linked cells for the combo boxes were from B3 & C3 on Graph data to B4 and C4.
I put in the below VBA code for each combo box:

Private Sub Date_End_Click()
ActiveSheet.Date_End.Value = Format(ActiveSheet.Date_End.Value, "mmmm d,yyyy")
Worksheets("Graph Data").Range("c3") = CDate(Me.Date_End)
End Sub

Private Sub Date_Start_Click()
ActiveSheet.Date_Start.Value = Format(ActiveSheet.Date_Start.Value, "mmmm d,yyyy")
Worksheets("Graph Data").Range("b3") = CDate(Me.Date_Start)
End Sub

In cells B3 and C3 I put in a formula that references B4 (=B4) and C4 (=C4)

This way the first line of the VBA code changes the value to Month day year and the second line formats the referenced cell because if you formatted the linked cell it would just feed back to the combo box and you would end up with numbers instead of the date format.

Hope this makes sense to anyone who encounters this problem in the future. Good luck!

Customer Part Analysis Dashboard.xlsm
	A	B	C
1	Todays date	2021-03-18
2	Year Selected	2021
3	Converted date range:	January 1, 2021	March 31, 2021
4	Combo box input:	January 1,2021	March 31,2021
Graph Data

Cell Formulas
Range	Formula
B1	B1	=TODAY()

However, this line on the VBA doesnt always work, sometimes has no effect, not sure why. If anyone has an idea as to why it doesnt work sometimes I would appreciate the input.

ActiveSheet.Date_End.Value = Format(ActiveSheet.Date_End.Value, "mmmm d,yyyy")

Take care

 

[Tupe77]

I may be wrong, but I can't find a point where the ComboBox index is converted to a cell value?
ComboBox returns the index number of the selection made from the list, not the selected value.
The "Graph Data:" shown in Figure 1.1.1900 is ComboBox's first option "2021" with index number 1.

I'm sorry if I misunderstood problem, and this was obvious.