• Hi all,

    I'm trying to find an elegant way of changing cells in a spreadsheet as a consequence of changing a specific cell. Because of formatting issues I'd like to do this using an Excel Event. Specifically I would like a macro to fire when I change the value of a particular cell based on a list choice through Data/Valadation/List. I thought that creating a subroutine using: "Private Sub Worksheet_Change(ByVal Target As Excel.Range)" would be a good idea but I can't figure out the syntax. Any thoughts on this?

  • Target will be a range reference to the cell that changed so you need something in the macro like:
    if Target.address=mycell.address then
    where mycell is the designation of your special cell. Does this help?

  • Hi.
    I tried the if/then statement but had no joy. I've copied a simple example for testing below to show what I hope would happen. Perhaps I just misunderstand what the purpose of this event is. Any other thoughts?

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Target.Address = Range("$L$52")Then
    If Range("$Q$52").Value = 1 Then

    Range("$k$56").Value = "Elastic"
    Range("$k$56").Value = "Plastic"

    End If

    End If

    End Sub

  • Well that gives me a reasonable explanation and a starting point. I'm using Excel 97.

    Now what if I try to have events happen based on a cell changing that is itself changed by the Data Validation cell?

  • The only problem with using a Worksheet_Calculate event is that I want to insert quite a lot of code in place of the "simple test code" that I showed. I don't want this to be firing every time that the sheet recalculates.

    When I try this again it seems that the event fires whenever ANY CELL on the worksheet is changed (other than the validation cell). Do you know if there is a way of limiting the target range so that it is only a specific cell that causes the event to fire?

Participate now!

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