Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2013
    Posts
    1
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Trouble Running Macros During Cell Change

    VB.JPG

    I am by no means an expert on visual back I am simply trying to run a macro when I change the value of a specific cell. The macros have been previously recorded and have no problems I was hoping somebody may be able to help me with my issue. I attached a .jpeg of my code and macros.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi

    In your code sub Pass(), change..

    ActiveCell.FormulaR1C1 = "PASSED"

    to..
    [m2626] = "PASSED"

    and in sub Fail()
    change..
    ActiveCell.FormulaR1C1 = "FAILED"
    to..
    [m2626] = "FAILED"

    (where the blob is a colon and letter o)

    However, you can achieve the same results just by using excel formulas and conditional formatting i.e. without VBA

    zeddy
    Last edited by zeddy; 2013-01-23 at 11:31.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    RDean2,

    Welcome to the lounge as a new poster.

    You can use the following code to limit the WorkSheet_Change event to checking a single cell or adapt it to check more than one cell.
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       
       Set isect = Application.Intersect(Range("A1"), Target)  '** Change A1 to your cell address **
       If isect Is Nothing Then
         MsgBox "Ranges do not intersect"
       Else
         MsgBox "A1 Changed", vbOKOnly + vbInformation, "Cell Changed"  '** Replace MsgBox with your code to execute **
       End If
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    rdean2 (2013-01-23)

Posting Permissions

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