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

#### willow1985

##### Well-known Member
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:

Cell Formulas
RangeFormula
D2D2=DATE(('Graph Data'!\$B\$2),1,1)
E2:E13E2=EOMONTH(D2,0)
D3:D13D3=EDATE(\$D\$2,F3)
C2C2=YEAR(TODAY())
C3:C6C3=C2-1
Named Ranges
NameRefers ToCells
Month_List_Start=List!\$D\$2:\$D\$13E2, D3:D13
Year_List=List!\$C\$2:\$C\$6C3

Graph Data:

Customer Part Analysis Dashboard.xlsm
ABC
2Year Selected1900
3Input Date RangeJanuary 1, 1900January 3, 1900
Graph Data

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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### willow1985

##### Well-known Member
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:

VBA Code:
``````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
ABC
1Todays date2021-03-18
2Year Selected2021
3Converted date range:January 1, 2021March 31, 2021
4Combo box input:January 1,2021March 31,2021
Graph Data
Cell Formulas
RangeFormula
B1B1=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.

VBA Code:
``ActiveSheet.Date_End.Value = Format(ActiveSheet.Date_End.Value, "mmmm d,yyyy")``

Take care

#### Tupe77

##### Board Regular
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.

Replies
24
Views
169
Replies
5
Views
358
Replies
3
Views
244
Replies
1
Views
198
Replies
0
Views
115

1,129,587
Messages
5,637,250
Members
416,963
Latest member
samfuge

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back