Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Excel Macro: Run Macro Based on a cell value

  1. #11
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,415
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    Do you know why S_Wish code would provide an error using the Case statement?
    Yes.
    It needs to be
    Code:
    Case Else
    not
    Code:
    Else
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  2. #12
    New Member
    Join Date
    Jul 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    I'm going to be a pain on this forum I think, but looking to learn VBA and improve.

    I've just changed something that means a value of the cell is as a result of a formula. However the macro doesn't run from the result of a formula. However if a type in the cell the result it does work. How can i change it so it runs from a result of a formula?

  3. #13
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,415
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    Worksheet_Change procedures fire on the manual change of a cell. If the change is the result of a formula change, it will not fire a Worksheet_Change procedure, based on that cell.
    There is a Worksheet_Calculate procedure which fires when a formula calculation occurs, but you cannot limit it to a particular cell, meaning it fires whenever any calculation on the sheet is updated (often overkill).

    What I often like to do, if possible, is look at the formula in B1, and have the Worksheet_Change procedure fire based on changes to the cells that the formula calls.
    So what is the formula?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #14
    New Member
    Join Date
    Jul 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    Hi Joe,

    Formula is =IF(COUNTIF(C8:C12,"Yes")>=1,"Yes","False")

    So want B1 to run the Macro if the result is Yes and unhide the rows if anything else/no.

  5. #15
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,415
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    Try this:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("C8:C12"), Target) Is Nothing Then
            If Range("B1") = "Yes" Then
                Rows("3:4").EntireRow.Hidden = True
            Else
                Rows("3:4").EntireRow.Hidden = False
            End If
        End If
    End Sub
    The key line is this here:
    Code:
    If Not Intersect(Range("C8:C12"), Target) Is Nothing Then
    What it essentially says is "Is the cell being updated in the range C8:C12"?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #16
    New Member
    Join Date
    Jul 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    You're a genius

  7. #17
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    40,415
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    You're a genius
    I don't know if I would go that far! There are users here who blow me away with some of the stuff they do.
    Let's just say I am "very experienced"!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #18
    New Member
    Join Date
    Jul 2017
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    does anybody know how i would run two macros within one worksheet change. My code ive tried so far hasn't worked for the second macro but is running the first one:

    Private Sub Worksheet_Change(ByVal Target As Range)
    My_Sub_A Target
    My_Sub_B Target


    End Sub


    Public Sub My_Sub_A(ByVal Target As Range)
    If Not Intersect(Range("C16:C21"), Target) Is Nothing Then
    If Range("C22") = "Yes" Then
    Rows("23:25").EntireRow.Hidden = True
    Else
    Rows("23:25").EntireRow.Hidden = False
    End If
    End If
    End Sub

    Public Sub My_Sub_B(ByVal Target As Range)
    If Not Intersect(Range("F2:F3"), Target) Is Nothing Then
    If Range("F4") = "Yes" Then
    Rows("27:28").EntireRow.Hidden = True
    Else
    Rows("27:28").EntireRow.Hidden = False
    End If
    End If
    End Sub

  9. #19
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,842
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    You would do it like this:
    But you had:
    C16:C21
    But then referred to C22 which is outside the range
    You did this in both codes. I changed them both.


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C16:C22"), Target) Is Nothing Then
    If Range("C22") = "Yes" Then
    Rows("23:25").EntireRow.Hidden = True
    Else
    Rows("23:25").EntireRow.Hidden = False
    End If
    End If
    If Not Intersect(Range("F2:F4"), Target) Is Nothing Then
    If Range("F4") = "Yes" Then
    Rows("27:28").EntireRow.Hidden = True
    Else
    Rows("27:28").EntireRow.Hidden = False
    End If
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #20
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,842
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel Macro: Run Macro Based on a cell value

    I would think both codes should be like this:
    See code change marked in red.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C22"), Target) Is Nothing Then
    If Range("C22") = "Yes" Then
    Rows("23:25").EntireRow.Hidden = True
    Else
    Rows("23:25").EntireRow.Hidden = False
    End If
    End If
    If Not Intersect(Range("F4"), Target) Is Nothing Then
    If Range("F4") = "Yes" Then
    Rows("27:28").EntireRow.Hidden = True
    Else
    Rows("27:28").EntireRow.Hidden = False
    End If
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

User Tag List

Tags for this Thread

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