Results 1 to 6 of 6

Thread: Macro to Auto Run When Excel Cell Data Changes

  1. #1
    Junior Member
    Join Date
    Nov 2014
    Posts
    3
    Rep Power
    0

    Macro to Auto Run When Excel Cell Data Changes

    Hi,

    I would like the below macro to automatically run when the cell data in "M5" (on the OSAR tab in Excel) changes. "M5" is linked to another tab and when data in that tab is entered, then this cell will change values from zero to actual words. Below is my if statement the deletes 4 rows if the cell data reads "HOTL_MOTL". Can you please help me make this macro auto run when this data is changed in the cell?

    Code:
    Sub sbVBS_To_Delete_EntireRow()
     [vb]
     If Range("M5").Value <> "HOTL_MOTL" And Range("M5") <> "" Then
     Rows(22).EntireRow.Delete
     Rows(22).EntireRow.Delete
     Rows(22).EntireRow.Delete
     Rows(22).EntireRow.Delete
     End If
     [/vb]
     End Sub
    Thanks!

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Paste this within the worksheet code module

    Code:
    Private Sub Worksheet_Calculate()
        
        If Range("M5").Value <> "HOTL_MOTL" And Range("M5") <> "" Then
            Application.EnableEvents = False
            Range("22:25").EntireRow.Delete
            Application.EnableEvents = False
        End If
        
    End Sub
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
    https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
    https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
    https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378
    https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305
    https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
    https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 03-01-2024 at 02:17 PM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Nov 2014
    Posts
    3
    Rep Power
    0
    That works except for it still deletes the rows if "HOTL_MOTL" is entered into that field...

    Thanks for the response!

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    If that's your only criteria, then why don't you remove the other criteria? Why do you need the check for blank value in M5?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Nov 2014
    Posts
    3
    Rep Power
    0
    Hmm.. I think I need help understanding your statement above.. What I want this macro to do (I believe I probably misstated this in the original post), is when values are entered into that field, I want the four rows to be deleted ONLY IF that cells does not read HOTL_MOTL. I guess I could go the other way and insert rows if it reads HOTL_MOTL.. I need some help with that part.

    Thanks!

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    try this in that case

    Code:
    Private Sub Worksheet_Calculate()
        
        Application.EnableEvents = False
        If Range("M5").Value <> "HOTL_MOTL" Then
            Range("22:25").EntireRow.Delete
        Else
            Range("22:25").EntireRow.Insert
        End If
        Application.EnableEvents = False
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 6
    Last Post: 09-03-2019, 10:26 AM
  2. Replies: 2
    Last Post: 05-30-2013, 07:28 PM
  3. Selecting workbook to run macro
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 08-24-2012, 08:21 PM
  4. VB code to Run formula untill blank cell
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 8
    Last Post: 05-20-2012, 11:08 AM
  5. Excel Macro Functions (GET.CELL)
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 05-17-2011, 08:56 AM

Tags for this Thread

Posting Permissions

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