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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
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:
1616082157296.png


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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 26, 2020
Messages
96
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.:rolleyes:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,387
Messages
5,636,010
Members
416,892
Latest member
Bensch

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top