• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Combo Box

ianb

ianb

Member
Sheet 2 has the data as an example :


Jan-12

Feb-12

Mar-12

etc


Combo Box has reference to these cells and the list index is as follows


Jan-12 is 1

Feb-12 is 2

Mar-12 is 3


I can run a vba program to set to the .listindex = 1 as it is Jan-12 at present


Sheets("Dashboard").DropDowns("Drop Down 1").ListIndex = 1


Input Range 'Month'!$A$2:$A$79


A2 = Jan-12

A3 = Feb-12

etc


Linked Cell = $A$6


=INDEX('Month'!$b$2:$b$37,$A$6)


The Forumla will let me produce a chart that when the combo box is changed manaully that will show that months chart. I need to have this set to this month and the vba program will update on a schedule to keep all combo boxes set to this month !!!

If changed I run and will set to this month.


When it is Feb I have to change my program to .ListIndex = 2


Would like this automated via a vba script with I have and not have to chnage each month before running.


How can I set all the combo boxes to the month at present.


I have the same for pivot tables which I have solved


CurrentPage = Format(Now, "mmm-yy")


If any one can solve this it will make me one happy bunny !!!
 
Hi, ianb!

Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!
 
First time on Drop Box


Is this right :


https://www.dropbox.com/s/hamzvn6d8le9bzc/Book1.xlsm?m
 
Excellent.


This is a puzzle for me.

Sample of the real program is listed below.


23 = Nov-12

24 = Dec-12

25 = Jan-12


60 = Year 2013 Q1


80 - Year 2013


It is the Month Ones I would like to change to this month at a click withour editing the vba program.. Q1 and year I can set each Q and Year if needed unless a solution is found for all.


Sheets("Dashboard (Overview)").Select

Sheets("Dashboard (Overview)").DropDowns("Drop Down 1927").ListIndex = 25

Sheets("Dashboard (Overview)").DropDowns("Drop Down 3126").ListIndex = 60

Sheets("Dashboard (Overview)").DropDowns("Drop Down 3209").ListIndex = 80

Application.GoTo Range("a1")


Sheets("Dashboard (Detailed)").Select

Sheets("Dashboard (Detailed)").DropDowns("Drop Down 10507").ListIndex = 25

Sheets("Dashboard (Detailed)").DropDowns("Drop Down 15455").ListIndex = 25


Idea.


Format Now() and then show as mmm-yy then find location on sheet and listindex to the left, then display... hmmm!!!
 
Hui,


Do you need further information as I have sent the example to drop box.


It is the select of Feb-12 when it is Febuarary that I require.
 
Hi, ianb!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Combo%20Box%20-%20Book1%20%28for%20ianb%20at%20chandoo.org%29.xlsm


I updated the code as follows, still I don't know if that's what you're looking for:

-----

[pre]
Code: 
Option Explicit

Sub DropDownUpdateMonth()

With Sheets("Sheet2")
.Select
' Would like this to be NOW () This month
With .DropDowns("Drop Down 1")
Do
If .ListIndex < .ListCount Then
.ListIndex = .ListIndex + 1
Else
.ListIndex = 1
End If
Debug.Print .ListIndex, .ListCount, .List(.ListIndex)
Loop While .List(.ListIndex) = ""
End With
End With

Application.GoTo Range("a1")

End Sub

'I have multi drop downs and need them to be the value and not the list index as each month
' I change the index  e.g. feb will be = 14 and also some are quarter and year.

'Here you have a sample of cycling thru clicking on Button.
'Is that what you wanted?
'Because getting to the actual month (January 2013) with your data isn't very practical.
[/pre]
-----


Just advise if any issue.


Regards!
 
Hi JB that is amazing thats. I can learn from this.


If I run your program as a schedule each month it will move to the next month, next quarter and also next year as I have multi combo boxes.


I think this is the best solution as if you can confirm please. it is not possiable if I set this to Feb-12 that I can run a program which moves it to the present month based upon now() or today()


If it is not possiable then this is the solution from you and will lead to more creativity in my programming.
 
JB,


Example : Date is set to Mar-12 and I click or run the program and it sets to Jan-13 (present month and year) also I can run your program on a loop for an inactive chart display so I am very happy with what I have just want to make sure that the above is not possiable before I incorporate more programming into my dashboard. your program will definitely be in there. Many thanks for your time.


Ian,
 
Hi, ianb!


Please download again the updated file from same previous link.


This code is executed at open time, placed in ThisWorkbook section:

-----

[pre]
Code: 
Option Explicit

Private Sub Workbook_Open()
' constants
Const ksWS1 = "Sheet1"
Const ksRngTable = "DateTable"
Const ksWS2 = "Sheet2"
Const ksRngIndex = "DateIndex"
' declarations
Dim rngTable As Range, rngIndex As Range
Dim I As Integer, J As Integer
' start
Set rngTable = Worksheets(ksWS1).Range(ksRngTable)
Set rngIndex = Worksheets(ksWS2).Range(ksRngIndex)
' process
With rngTable
J = 0
For I = 1 To .Rows.Count
' exits at first blank
If .Cells(I, 1).Value = "" Then Exit For
' year&month match?
Debug.Print I, .Cells(I, 1).Value
If Year(.Cells(I, 1).Value) = Year(Now()) And _
Month(.Cells(I, 1).Value) = Month(Now()) Then
J = I
Exit For
End If
Next I
' initialize control if match
If J > 0 Then
rngIndex.Cells(1, 1).Value = J
End If
End With
' end
Set rngIndex = Nothing
Set rngTable = Nothing
End Sub
[/pre]
-----


If it finds a valid date (not text, date) in the list of worksheet Sheet1 that matches year and month with actual date, it sets the control to that entry. If it finds an empty entry (separation between types of aggregation) it quits.


Regards!


PS: There are a couple of named ranges just to make easy coding, keep them if you translate the solution to your actual file.
 
Now that is a genius at work. Many thanks. that is perfect for setting all the drop down boxes on opening. more than I expected. I do not even have to run a month end... Thanks.
 
Hi, ianb!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Hi SirJB


Can you assist further. I have been updating your program and I am unable to multicombo box updates. are you avalaible to updaet the program with the additonal lines required.


Here is how far I am at present.


https://www.dropbox.com/s/e76m7eiqrfot4vm/Combo%20Box2%20.xls?m


Kind regards,


Ian.
 
I need to run this via a module from a windows schedule each month hence why I placed into a module not at start up.


Thanks.
 
Hi, ianb!


As far as I can see you're still writing more than reading...


This time I'll try to learn you fishing instead of giving you fishes.


Try doing this:

a) take the code that you commented, un-comment it, and move it to a new module under any procedure name

b) adjust it as receive as a parameter instead of a constant the ksWS2 and ksRngIndex constants

c) insert a command button on seldom worksheets Dashboard and Dashboard2 and assign in its code the call to the previous new procedure passing the 2 related parameters (worksheet name and date index cell name)

d) fix the duplicate names DateIndex as DateIndex1, 2, and so if needed (you now have a DateIndex with workbook scope and another with Dashboard2 scope, and you should have all of them with different names and workbook scope)


BTW, running the provided or the adjusted macros at open time wouldn't do any harm if they work upon the year and month value, so I don't see what would change in your data. But it's not important at all, just a commentary.


Hope it helps.


Regards!
 
HI, I will try this. thanks for the guidance....


Final Question if you have a minute.

I have move the program into my dashboard and pointed at both.


Why does this error. it looks right ? Any other spreadsheet it works fine ?


Const ksWS = "Statistics Month"

Const ksRngTable = "DateTable"

Const ksWS2 = "Dashboard (OPALIS)"

Const ksRngIndex = "DateIndex"


Application-defined or object-defined error


Set rngTable = Worksheets(ksWS).Range(ksRngTable)
 
Work in progress.......Think I need my ZZZZZZ!!! Refreshed mind tomorrow will help.... its been a long day.. thanks for your guidance... have many solution and heading in the right direction....


It shall be the weekend of triumph !!!


Regards, Ian.
 
Hi, ianb!

Check if you have defined the named ranges "DateTable" and "DateIndex" in seldom worksheets "Statistics Month" and "Dashboard (OPALIS)".

Regards!
 
Great. To the weekend I head. Think I just need to sleep on it. hope the magic starts tomorrow. !!! many thanks for all your time and energy assist me. ian.
 
Got It. To the Batmobile lets go.


Set rngTable = Sheets("Statistics Month").Range("A2:A27")

Set rngIndex = Sheets("Dashboard (OPALIS)").Range("F2")


Many thanks. Ian.


Next week I will be walking of a cliff !!!
 
Hi, ianb!

Yes, you got it working, but with fixed ranges and not with named ranges. If it's suitable for you then everything's fine. Still I suggest you to use named ranges.

Regards!
 
Hi, ianb!

Check this:

https://dl.dropbox.com/u/60558749/In%20memory%20of%20Afred.mp3

Regards!
 
Will look into it. thanks for program and the guidance.


My configuration of Charts, Colours and PIvot tables can have this program listed below.


Declare Function sndPlaySound32 Lib "winmm.dll" Alias _

"sndPlaySoundA" (ByVal lpszSoundName As String, _

ByVal uFlags As Long) As Long


Const SND_SYNC As Long = 0

Const SND_ASYNC As Long = 1


Sub SoundUpdate()


Dim strSoundFile As String

strSoundFile = "c:updatesound.wav"

Call sndPlaySound32(strSoundFile, SND_ASYNC)


End Sub


Just in case you need the .wav format


https://www.dropbox.com/s/tbcddbhszyzyeqt/Sound.wav?m


Can you beleive the config of the dashboard at work lasts just over 1 minute.
 
You must log in or register to reply here.
Back
Top