Select Case - Based on Date:

[m0atz]

Afternoon all,

Just require a wee bit of help with the following code. It sits in the "This Workbook" code window to run each time the wbook is opened. What I want it to do, is based on the date in Sheets("Control").Range("L3") select a particular cell in another sheet called "Risk Sum". I'm assuming this is easy to do, I've come up with the following, but it just doesnt want to work...!!

Private Sub Workbook_Open()
Dim mth
Dim control_date
control_date = Worksheets("Control").Range("L3")
Sheets("Risk Sum").Select
Select Case mth
Case control_date = "*/01/*": Range("risksum_jan").Activate
Case control_date = "*/02/*": Range("risksum_feb").Activate
Case control_date = "*/03/*": Range("risksum_mar").Activate
Case control_date = "*/04/*": Range("risksum_apr").Activate
Case control_date = "*/05/*": Range("risksum_may").Activate
Case control_date = "*/06/*": Range("risksum_jun").Activate
Case control_date = "*/07/*": Range("risksum_jul").Activate
Case control_date = "*/08/*": Range("risksum_aug").Activate
Case control_date = "*/09/*": Range("risksum_sep").Activate
Case control_date = "*/10/*": Range("risksum_oct").Activate
Case control_date = "*/11/*": Range("risksum_nov").Activate
Case control_date = "*/12/*": Range("risksum_dec").Activate
End Select
End Sub

Can someone suggest something a little tidier / more importantly something that works!!

Cheers

Colin

 

[Domski]

Hi,

Maybe:

Dim mth As Integer
With Sheets("Risk Sum")
Select Case Month(Worksheets("Control").Range("L3"))
Case 1: .Range("risksum_jan").Activate
Case 2: .Range("risksum_feb").Activate
Case 3: .Range("risksum_mar").Activate
Case 4: .Range("risksum_apr").Activate
Case 5: .Range("risksum_may").Activate
Case 6: .Range("risksum_jun").Activate
Case 7: .Range("risksum_jul").Activate
Case 8: .Range("risksum_aug").Activate
Case 9: .Range("risksum_sep").Activate
Case 10: .Range("risksum_oct").Activate
Case 11: .Range("risksum_nov").Activate
Case 12: .Range("risksum_dec").Activate
End Select
End With

Dom

 

[m0atz]

That seems to just bring up "Application defined or Object Defined Error"

??

 

[kgkev]

Private Sub Workbook_Open()

    Dim mth
    Dim control_date

    control_date = Month(Worksheets("Control").Range("L3").Value)
    Sheets("RiskSum").Select

    Select Case control_date
        Case 1
            Range("risksum_jan").Activate
        Case 2
            Range("risksum_feb").Activate
        Case 3
            Range("risksum_mar").Activate
        Case 4
            Range("risksum_apr").Activate
        Case 5
            Range("risksum_may").Activate
        Case 6
            Range("risksum_jun").Activate
        Case 7
            Range("risksum_jul").Activate
        Case 8
            Range("risksum_aug").Activate
        Case 9
            Range("risksum_sep").Activate
        Case 10
            Range("risksum_oct").Activate
        Case 11
            Range("risksum_nov").Activate
        Case 12
            Range("risksum_dec").Activate
        Case Else
            MsgBox ("Please Check Date")
    End Select
End Sub

 

[Domski]

Ooops!!!

Try:

Dim mth As Integer
Select Case Month(Worksheets("Control").Range("L3"))
Case 1: Range("risksum_jan").Activate
Case 2: Range("risksum_feb").Activate
Case 3: Range("risksum_mar").Activate
Case 4: Range("risksum_apr").Activate
Case 5: Range("risksum_may").Activate
Case 6: Range("risksum_jun").Activate
Case 7: Range("risksum_jul").Activate
Case 8: Range("risksum_aug").Activate
Case 9: Range("risksum_sep").Activate
Case 10: Range("risksum_oct").Activate
Case 11: Range("risksum_nov").Activate
Case 12: Range("risksum_dec").Activate
End Select

Dom

 

[m0atz]

It doesnt seem to be working, I'm getting the case else everytime. What is Case 1, Case 2 etc - are they defaults??

Cheers


Colin

 

[m0atz]

Domski - it works!!

Great stuff - many thanks..

Colin

 

[m0atz]

Is it also possible to do multiple things based on the Case, i.e. off the top of my head is the following correct:

Dim control_date
control_date = Worksheets("Control").Range("L3").Value
Sheets("Risk Sum").Select
Select Case month(Worksheets("Control").Range("L3"))
Case 1: Range("risksum_jan").Activate:call Q1Top10
Case 2: Range("risksum_feb").Activate
Case 3: Range("risksum_mar").Activate
Case 4: Range("risksum_apr").Activate:call Q2Top10
Case 5: Range("risksum_may").Activate
Case 6: Range("risksum_jun").Activate
Case 7: Range("risksum_jul").Activate:call Q3Top10
Case 8: Range("risksum_aug").Activate
Case 9: Range("risksum_sep").Activate
Case 10: Range("risksum_oct").Activate:Call Q4Top10
Case 11: Range("risksum_nov").Activate
Case 12: Range("risksum_dec").Activate
End Select
end sub

cheers

Col

 

[Domski]

Domski - it works!!

Great stuff - many thanks..

Colin

As an alternative to using the the select case you could use:

Private Sub Workbook_Open()
If IsDate(Worksheets("Control").Range("L3")) Then
Range("risksum_" & Format(Worksheets("Control").Range("L3"), "mmm")).Activate
End If
End Sub

Dom

 

[kgkev]

Month(Worksheets("Control").Range("L3")) this generates a number 1-12 based on the month on cell L3

The Case 1 refers to month 1

If you look at my example (which isn't as efficient as doms) it is a bit clearer how you can use the case




Case 1
code
code
code
code
code
Case 2
.
.
.
.
.