Results 1 to 7 of 7

IF Cell Equals then.....

This is a discussion on IF Cell Equals then..... within the Excel Questions forums, part of the Question Forums category; Hi, I'm using date codes on my excel sheet, so 1 being Sunday and 7 being Saturday. I want excel ...

  1. #1
    New Member
    Join Date
    Feb 2003
    Posts
    14

    Default IF Cell Equals then.....

    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.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default Re: IF Cell Equals then.....

    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
    Bye,
    Jay

  3. #3
    New Member
    Join Date
    Feb 2003
    Posts
    14

    Default Re: IF Cell Equals then.....

    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?

  4. #4
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: IF Cell Equals then.....

    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
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  5. #5
    New Member
    Join Date
    Feb 2003
    Posts
    14

    Default Re: IF Cell Equals then.....

    Still doesn't work. Still says Object Required.

    Any ideas?

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,371

    Default Re: IF Cell Equals then.....

    I think you need to omit the Cells property:

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

  7. #7
    New Member
    Join Date
    Feb 2003
    Posts
    14

    Default Re: IF Cell Equals then.....

    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.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com