Combine Private Sub Worksheet_Change

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hello!

    I'm new to VBA. How can I combine 2 worksheet change commands? I tried to do it as such below but only the first portion works.

    Basically, what I want is that when user inputs "56765201 MEALS" or "56855753 ENTERTAINMENT EXPENSES" or "56855777 MEETINGS" under column D, a msgbox will pop up (the message would depend on the input.

    And then in Column F, if the date they input is more than 90 days from current date, then a MsgBox should appear.

    Hope somebody can help me out!

  • Try this, note your check for Column 5 will always stop the code because you have already determined that the cell must be in Column 4.

    Also, you have not declared all your variables.

  • Hello @royUK ! I applied your suggestion but when I was trying it, this message showed up. I then typed "End With" between the last 'End If' and the last 'End Sub', thinking it would prevent the message. However, when I try inputting the values under Column D or a date that is more than 90 days in Column F, nothing happens.

  • Code amended

  • Hi royUK! I tried your 2nd suggestion but it is still not according to what I need. I made some tweaks with your code and below is the final one I made. Thank you so much for your help!

  • I can't see how the last part of your code can work.

    At the start you check if the Column is 4, then later check if it is 6. Since the active cell does not appear to move this will never be true, unless the userform changes the active cell, which in my code would be unnecessary.

    You've completely messed up the With Statement.

    You've split the IF statement back to 3 statements and not bothered using CountLarge which prevents an overflow error occurring - see CountLarge

Participate now!

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