IF Cell Equals then.....

mcslemon

New Member
Joined
Feb 24, 2003
Messages
14
Hi, :cool:

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. :)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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...

Code:
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
 
Upvote 0
Cheers for the response.

Got a problem though.....

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

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

it then gives:

Run Time Error: 424. Object Required

Any ideas on this?
 
Upvote 0
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
 
Upvote 0
Thanks for the help guys,

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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top