IF Cell Equals then.....

[mcslemon]

Hi,

I'm using date codes on my excel sheet, so 1 being Sunday and 7 being Saturday.

I want excel to open a particular sheet on a particular day. So basically have it watch for 1 or 7 and then load a sheet.

I've tried to modify

=if(E14=1,"Sunday")

and have it look something like

=if(E14=1, application.run("personal.xls!open_sheet"))

Any ideas?

Neil.

[Jay Petrulis]

Hi,

It is not possible to do what you wish using a worksheet formula.

What you can do is use an event macro to accomplish the same thing...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SettingsArray

With Application
    SettingsArray = Array(.Calculation, .EnableEvents, .ScreenUpdating)
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = True
End With

With Target
    If .Cells.Count = 1 And .Address(False, False) = "E14" Then
        If .Value = 1 Then
            Application.Run ("personal.xls!open_sheet")
        End If
    End If
End With


With Application
    .Calculation = SettingsArray(0)
    .EnableEvents = SettingsArray(1)
    .ScreenUpdating = SettingsArray(2)
    
End With


End Sub

[mcslemon]

Cheers for the response.

Got a problem though.....

I step into the macro, and all is ok until it gets to:

If .Cells.Count = 1 And .Address(False, False) = "C43" Then

it then gives:

Run Time Error: 424. Object Required

Any ideas on this?

[just_jon]

Jay would know this cold, but as he's not around I'll take a guess that you need --

If .Cells.Count = 1 And .Address(False, False) = .Range("C43") Then

[mcslemon]

Still doesn't work. Still says Object Required.

Any ideas?

[Andrew Poulsom]

I think you need to omit the Cells property:

If .Count = 1 And .Address(False, False) = "C43" Then

[mcslemon]

Thanks for the help guys,

but I took the principle from what was suggested and did this which is working how I hoped.

Sub Graph_check_two()

Workbooks("base.xls").Activate
select_and_value = Worksheets("Front_Page").Range("C43").Select
If select_and_value = 1 Then
Application.Run "personal.xls!chart_wend"
Else
If select_and_value = 7 Then
Application.Run "personal.xls!chart_wend"
Else
Application.Run "personal.xls!chart_week"
End If
End If

End Sub

Thanks for all the Input.

Neil.