Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Run a macro based on text in a cell?

  1. #1
    New Member
    Join Date
    Feb 2012
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Run a macro based on text in a cell?

    Hello

    Im new to this and was hoping for some help/advice. What I would like to do it run a macro when specific text is entered into a specific cell. This is for a template I am making.

    For example when a user entered the words "None" or "Zero" into cell D4 - the macro is run automatically. If any other value is entered, do nothing

    Any help would be much appreciated

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    16,961
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Run a macro based on text in a cell?

    Paste this into the Sheet module of your workbook
    Adjust the "Do Stuff" line to suit your actual needs

    Code:
    Sub worksheet_change(ByVal target As Range)
    Set target = Range("D4")
    If target.Value = "Zero" Or target.Value = "None" Then
        'Do Stuff
    End If
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    New Member
    Join Date
    May 2013
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run a macro based on text in a cell?

    Hi,

    Ive been doing this but no joy: A specific text is selected from a drop down list to trigger the macro
    Code:
    Sub worksheet_change(ByVal target As Range)
    Set target = Range("c8")
    If target.Value = "Long PA Professional with SPQ" Or target.Value = "Long PA Professional" Then
    Sub Merge()
    '
    ' Merge Macro
    '
    
    
    '
        Range("D16").Select
        ActiveCell.FormulaR1C1 = "Occupied By Long PA"
        Range("D16:I16").Select
        With Selection.Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        With Selection.Font
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = 0
        End With
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 1
        Range("C16").Select
    End Sub
    Last edited by RoryA; May 12th, 2015 at 01:18 PM.

  4. #4
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run a macro based on text in a cell?

    I am very very new to this and I'm struggling even after doing a lot of reading. I am trying to do what seems to be pretty simple. I want to run a simple macro that unhides certain rows when certain text is in a cell. I recorded a macro that unhides the cells but I can't get it to run when that text is in the cell. Basically what I want is if cell B3=Tower then unhide rows 51-55. Can someone help me? Thank you in advance.
    Last edited by RoryA; Apr 23rd, 2015 at 08:55 AM.

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,210
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Run a macro based on text in a cell?

    The basic code would be:
    Code:
    If Range("B3").value = "Tower" then range("51:55").Entirerow.hidden = False
    How do you want it triggered?

  6. #6
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run a macro based on text in a cell?

    Cell B3 is a drop down so if I select Tower from the drop down that's how I would like it triggered. Is that possible?

    Quote Originally Posted by RoryA View Post
    The basic code would be:
    Code:
    If Range("B3").value = "Tower" then range("51:55").Entirerow.hidden = False
    How do you want it triggered?

  7. #7
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,210
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Run a macro based on text in a cell?

    Right-click the worksheet tab, choose View Code and paste this in:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        If Range("B3").Value = "Tower" Then Range("51:55").EntireRow.Hidden = False
    End If
    End Sub

  8. #8
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run a macro based on text in a cell?

    Thanks so much! I hate to be a pain but is there a way to then re-hide those cells if I change what I pick in the drop down? That is, I pick Tower so the rows unhide but then I change it to something else so they hide again?

    Quote Originally Posted by RoryA View Post
    Right-click the worksheet tab, choose View Code and paste this in:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        If Range("B3").Value = "Tower" Then Range("51:55").EntireRow.Hidden = False
    End If
    End Sub

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,210
    Post Thanks / Like
    Mentioned
    40 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Run a macro based on text in a cell?

    Sure, just change it to:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        Range("51:55").EntireRow.Hidden = (Range("B3").Value = "Tower")
    End If
    End Sub

  10. #10
    New Member
    Join Date
    Apr 2015
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Run a macro based on text in a cell?

    When I use that command it unhides the rows no matter what I choose from the dropdown menu. I'm hoping I can set it up so that when I chose "Tower" it unhides those rows and even after I have chosen "Tower" and then change it to something else, it would hide those rows.

    Quote Originally Posted by RoryA View Post
    Sure, just change it to:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        Range("51:55").EntireRow.Hidden = (Range("B3").Value = "Tower")
    End If
    End Sub

Some videos you may like

User Tag List

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
  •