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:


    Code
    sub backgroundfill()
    ColourIndex = 35 
     
    Range("B15").Activate 
    If ActiveCell.Value = "" Then 
        ActiveCell.Interior.Color = xlNone 
    Else 
        ActiveCell.Interior.ColorIndex = ColourIndex 
    End If 
end sub


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


    Then all you should have to do is:


    Code
    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.:


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

    etc.

    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


    Quote

    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:

    Code
    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!

Register Yourself Login