DropDown Box set to default Now() not .ListItem

[ianb]

Hi,


Can any one get this working for me please. I can set Listitems yet woud like to set the dropdown to now not a value so the program does not need updating each month.


Thanks.


Sub test1 ()


Sheets("Sheet1").Select

Sheets("Sheet1").DropDowns("Drop Down 1927").ListItem = 26

'(Format(Now, "mmm-yy"))


End Sub

 

[keymaster]

If you just want NOW value to be selected all the time:


Then why use drop down box? Just put now in a cell and you are done!


If you want NOW to be one of the values for the drop down

Then in your source range, replace any one value with =NOW() formula.

 

[ianb]

Thanks for the advise. it is a little more complex than this. The drop down looks up data on another worksheet and displays the data in charts. the cahrts are controled by the dropdown list. e.g. data for Dec-12 is shown in the cells and also the chart updates to the same month.


=INDEX('Statistics Month'!$I$2:$I$49,$D$5)


'Statistics Month'!$A$2:$A$49

Cell Link $M$2


Sheets("Dashboard").Select

Sheets("Dashboard").DropDowns("Drop Down 3213").ListIndex = 24


I want to run a macro that changes all my dropdown lists to this month as they are all linked to charts. I can not find a way of replacing .ListIndex with what is contained in the drodownbxo e.g. Dec-12 which is the same as Now, "mmm-yy


Would look something like this :


Sheets("Dashboard").DropDowns("Drop Down 3213").<DropDownListName> = (Format(Now, "mmm-yy"))


What is the correct macro line to achieve this. I have done this for pivot tables yet for the drop downs I can not find a solution.


I could use ListIndex yet I would have to change the .ListIndex = 24 each month.


Thanks.. Ian.

 

[Colin Legg]

Ian,

As a first effort, you can loop through the sheet's dropdowns collection and set each dropdown's listindex to 24, like so:

[pre]

Sub foo()

Dim drp As DropDown

For Each drp In Sheets("Dashboard").DropDowns
drp.ListIndex = 24
Next drp

End Sub
Then, it turns out that you can set all of ListIndex properties directly from the DropDowns collection itself, so you can remove the VBA loop by doing it like this instead:

Sub foo2()

With Sheets("Dashboard").DropDowns
If .Count > 0 Then .ListIndex = 24
End With

End Sub

[/pre]

 

[ianb]

Thansk Colin it is coming closer to my solution.


I have some drop down boxes that have mmm-yy and some have dd-mm-yy on the same sheets.


Also I would like to reference on want is the value of the .listindex e,g, .listindex 24 = Dec-12


Is this possiable. Then each drop down can be set to Now() by running a macro.


This is the last part of my Auotmated Dashboard. I have learnt so much and also I an thankful for the programs and assistance I have been given by this web site.


If any one would like assistance with a dashboard caluclating from .csv files. access DB and also Pivot tables, Charts, Drop Down boxes I have my dashboard fully automated excpet for this final part. Drop Down boxes Automatically setting to Now()


Many Thanks again for the time and energy in assisting me. Ian.

 

[Colin Legg]

Hi Ian, I don't quite follow what you've said there. My understanding from your earlier post is that the dropdowns already have today's date in their lists and that by setting the ListIndex to 24, today's date will be shown in the dropdown.

 

[ianb]

Hi Colin


The drop down boxes all have links to this spreasheet wherer the data is housed.


'Statistics Month'!$A$2:$A$49 A2-A49 has months and years.... some have Year Total eg. Year 2012


Each drop down then has the data set with the index being D5


=INDEX('Statistics Month'!$I$2:$I$49,$D$5) (B to K)


Cell Link $M$2 - All the cell inks are differnet on different spreadsheets


I have for each drop down box what is listed below as an example.


ListIndex is Dec-12 therefore next month will be Jan-12 I will want to run a macro to change all the drop down boxes to Jan-13 and also to Year 2013. I can run this macro every 1st of the month to change all the drop down boxes. What I have at present is .listindex which I would have to change 24 to 25 for each section of the macro. I could do this manually by the time I have updated the macro for each list index. I want to be able to change the value if this is the correct term from 24 being Dec-12 to asking the dropdown box to go to Dec-12 or Jan-12 which would be Now() Format mmm-yy or year 2013 which would be yyyy.


Two examples are listed below. I have a total of approx 16 dropdown boxes maninly mmm-yy


Sheets("Dashboard").Select

Sheets("Dashboard").DropDowns("Drop Down 3213").ListIndex = 24


Sheets("Dashboard2").Select

Sheets("Dashboard2").DropDowns("Drop Down 3214").ListIndex = 25


Also the crop downn box controls the data selected on each spreadheet which is a part edited copy of the data I store on the data sheet.


e.g. =INDEX('Statistics Month'!$B$2:$B$79,$D$5)


All the data shown for each month also has a chart linked to it. Hence when I run my dropdown macro it will show the correct month (this month) the data will also show for this month and the chart will show for this month. all the data collect from the Stats Data sheets which is hidden and updated each day.


I have searched for the answer and tried numerous ways and I can not find how to change .listindex to the display of Oct-12, Dec-12 or Jan-13


What Can I use instead of .listindex to reference the display of the index and also do I need to use : ' (Format(Now, "mmm-yy"))


What A final puxxle for me to have...Many thanks if you can solve it.


You have already helped in shorting my prgrma at present for the months listed above.

 

[ianb]

Still trying to find the answer to this. please see notes above.


.listindex = 24


.<What is this> = Dec-12


Thanks.

 

[ianb]

I have not been able to solve this. I will just set these manually each month.


Unless any one know how to do this. did try .value and that also is not the solution as it does not change the chart values. thanks for the advise and also the programming which I will find a use for in other locations.


Thanks all.

 

[ianb]

Thanks for all your help guys. Hui, Colin and JB to name the top 3. I have stopped working on the Dashboard. I will be working on my own projects from now on.


Thanks all for you assistance. It was a good learning experience.