CODEWITHSUNDEEP
excelvba
vash2695
vash2695

Reputation: 3

Attempting to run a specific macro based on the current value of a cell

I'm attempting to set up a workbook that allows me to log data in cells on one sheet to other sheets based on the day of the week. Basically it's a weekly tracker with a separate sheet for each day of the week. I have separate macros to move the desired data from the "current" sheet to any other day of the week, and each of those work as desired.

Edit: I'm binding this macro to a button on the primary sheet, rather than using a hotkey or list.

Sub Lookup()
    Dim day As String

    day = ThisWorkbook.Sheets("Data").Cells(2, "x").Value2

    If day = "Mon" Then
        Call Mon

    ElseIf day = "Tue" Then
        Call Tue

    ElseIf day = "Wed" Then
        Call Wed

    ElseIf day = "Thu" Then
        Call Thu

    ElseIf day = "Fri" Then
        Call Fri
    End If
End Sub

I'm trying to make a separate macro that looks at the data in a specific cell in a specific sheet (using the "Now()" function in a specific cell, formatted to show the day of the week only) and just want it to look at that data and run the corresponding macro. As it is now, it stops on the first step with the error

"Compile error: Expected function or variable"

Edit: I realized that if I have it reference a cell where I've manually typed in any given day, it works. So it looks like the macro is only looking at the formula rather than the result. Changing the formula in the cell to 

=TEXT(NOW(),"ddd")

resolved the issue. Thank you for all of your help!

Upvotes: 0

Views: 141

Answers (2)

tigeravatar
tigeravatar

Reputation: 26670

My guess is that you need to format the output you're comparing against in order to run the macro. You state you have =NOW() in the cell, and that will not return Mon for example. So you can use the Format method to get the output you're looking for.

Here's a way to validate that what's in cell X2 is actually a date, and that the date falls on a Mon-Fri (exclude weekends), so that you can call your Mon-Fri macros by passing it to Application.Run:

Sub Lookup()

    Dim lWeekday As Long

    With ThisWorkbook.Worksheets("Data").Range("X2")
        On Error Resume Next
        lWeekday = WorksheetFunction.Weekday(.Value, 16)
        On Error GoTo 0
        Select Case (IsDate(.Value)) And (lWeekday > 2) 'Verify date and that it is Mon-Fri
            Case True:  Application.Run Format(.Value, "ddd")
            Case Else:  MsgBox "Invalid date: [" & .Value & "]", , "Error"
        End Select
    End With

End Sub

Upvotes: 1

Tom
Tom

Reputation: 9898

You can pass a String value to Application.Run so you could shorten your code to

Sub Lookup()
    Dim day As String

    day = ThisWorkbook.Sheets(8).Cells(2, "x").Value

    Application.Run day
End Sub

and then with your Sheets(8).cells(2,"x") add data validation to the cell with a list value of Mon, Tue, Wed, Thu, Fri or whatever your macro names are so that the user can only select from a defined list of choices.

Also, you need to explicitly reference your sheet instead of referencing its position.

i.e.

day = ThisWorkbook.Sheets(8).Cells(2, "x").Value

becomes

day = ThisWorkbook.Sheets("YourSheetNameHere").Cells(2, "x").Value2

or handle it in your macro

Sub Lookup()
    Dim day As String
    Dim ValidSubs As Variant
    Dim RunSub As Boolean: RunSub = False
    Dim c

    ValidSubs = Array("Mon", "Tue", "Wed", "Thu", "Fri")

    day = ThisWorkbook.Sheets(8).Cells(2, "x").Value

    For Each c In ValidSubs
        If c = day Then
            RunSub = True
            Exit For
        End If
    Next c

    If RunSub Then
        Application.Run day
    Else
        MsgBox "You have selected an invalid choice - only the following are allowed:" & vbNewLine & Join(ValidSubs, vbNewLine)
    End If
End Sub

Upvotes: 0

Related Questions