Add a Second Event Handler

  • I would appreciate guidance with the following:

    I have a block of code that runs on activation of a particular worksheet. I also want to run the same block of code whenever the value in Range("J2") on that worksheet is changed. Range("J2") is the only cell that this is to apply to.

    How do I modify my code so that the code runs on either of these two events?

  • Re: Add a Second Event Handler

    Firstly, the Activecell line before the IF statement is redundant.

    I have no idea if there is a more elegant way of doing what you ask, but I would be tempted to try using the worksheet_change event on the appropriate sheet, and then test whether the activecell.address=("J2") and call your other subroutine if it is. Also better to put the code you have in worksheet_activate into a general sub in a module, say backgroundfill, something like:

    sub backgroundfill()
        ColourIndex = 35 
        If ActiveCell.Value = "" Then 
            ActiveCell.Interior.Color = xlNone 
            ActiveCell.Interior.ColorIndex = ColourIndex 
        End If 
    end sub

    private sub worksheet_change()
    If activecell.address=("J2") then call backgroundfill
    end sub

    Then all you should have to do is:

    private sub worksheet_activate()
    call backgroundfill
    end sub

    Health warning: I haven't tested this. but it should get you on the right track at least. :)

    P.S. To avoid too many convoluted subroutine calls, consider not moving activecell, e.g.:

    if range("b15").value=""


  • Re: Add a Second Event Handler

    After reading your post again, I don't understand the relationship between J2 & B15. You say "whenever the value in Range("J2") on that worksheet is changed" you check the value in B15. What I don't understand is If B15 does not refer to J2 by means of a formula why are you checking B15?

    I don't think you need code for this, Conditional Formatting in B15 using =B15<>"" as the fomula and the formatting set to highlight the cell with the appropriate colour (I've no idea which colour ColorIndex 35 is).

  • Re: Add a Second Event Handler

    It's a convoluted process but I'll do my best to explain it clearly.

    The worksheet has multiple cells containing vlookup formulae; B15 is one of those cells. J2 holds the vlookup_value and is manually input. B15 actually tests the contents of cell B4. If the value in B4 contains the word 'Application' then a concatenated string is entered into B15. If the value in B4 does not include the word 'Application' then B15 is left blank. In addition, if B15 contains text (which happens when B4 includes the word 'Application) then I also want to highlight cell B15. Conversely, if B15 is blank (because B4 doesn't include the word 'Application') then I want B15 to not be highlighted.

    Hope I haven't lost you!

    I've already tried conditional formatting but the fact that B15 contains a formula made it impossible for me to get conditional formatting to work. And in any case I'm trying to improve my understanding of VBA so that I can gradually solve more and more of my problems by myself, hence the wish for VBA guidance.

    I've added your coding into the spreadsheet and it's working far better than my attempts did, but there's a residual problem. Cell B15 is staying filled regardless of whether there's text in there or not, which means there's a problem with my initial code.

  • Re: Add a Second Event Handler

    Referring back to my original post, changing the value in J2 (the vlookup_value) changes many cells on the spreadsheet because, as per my last post, there are vlookup formulae all over the worksheet. Whenever I change the value in J2 I want the block of code to be triggered (Call backgroundfill). That seems to be happening OK with your code, it's just that cell B15 still retains the highlighting even though it doesn't contain a value (other than the underlying formula)

  • Re: Add a Second Event Handler


    the fact that B15 contains a formula made it impossible for me to get conditional formatting to work

    Disagree. There is absolutely no problem having Conditional Formatting in a cell that already contains a 'standard' formula.

  • Re: Add a Second Event Handler

    No - I'm not saying it's impossible at all, I'm saying it was impossible for me to figure it out. And my preference is for a VBA solution

  • Re: Add a Second Event Handler

    In general, native worksheet functions are preferable to VBA, if for no other reason than they are a lot faster. I don't believe you need VBA for this but if that's your preference, then that's your preference.

  • Re: Add a Second Event Handler

    I've found where it's going astray. I just ran the backgroundfill code on its own and that does clear the highlighting when there's no text. The issue is the first line of the code.

    I had to change the first line to "Private Sub Worksheet_Change(ByVal Target As Range)" because without that change I got a compile error:
    Procedure declaration does not match description of event or procedure having the same name.
    With the change in the first line, the fill colour of B15 remains static when the code is run; without the change I get a compile error.

  • Re: Add a Second Event Handler

    Aha! I've solved it!

    I now have:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("J2") Then
        Call backgroundfill
    End If
    End Sub

    So simple. Thanks guys for your interest and help

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!