How to make a macro run each time a specific cell value changes.

[ARC32]

I'm very new to vba so I'm probably making hard work of this, but I've tried to write a macro that will hide certain rows based on the value of other cells. Good news is that when I play the macro it appears to work.

Bad news is that I want to make the macro run every time the value of Cells(180,2), Cells(5,2) or Cells(27,3) changes (regardless of whether it's changed manually by the user, by formula or by another macro).

I've found similar threads elsewhere on here, but like I said, been using vba for a couple of days and afraid I just haven't followed them.

Can anyone help? - My code is as follows:

Sub mcrHideRows()

Dim intNumRows As Integer
Dim intRowCount As Integer

Application.ScreenUpdating = False

intNumRows = 221

If Cells(180, 2) <> "Used" Then Rows(180).Hidden = True
If Cells(180, 2) <> "Used" Then Rows(181).Hidden = True

If Cells(180, 2) = "Used" Then Rows(180).Hidden = False
If Cells(180, 2) = "Used" Then Rows(181).Hidden = False

For intRowCount = 1 To intNumRows

If Cells(5, 2) <> "Other" And Cells(introwcount, 16).Value = "x" Then Rows(introwcount).Hidden = True

If Cells(5, 2) = "Other" And Cells(27, 3) = "Limited" And Cells (introwcount, 14).Value = "x" Then Rows(introwcount).Hidden = True

If Cells(5, 2) = "Other" And Cells(27, 3) = "Non-Limited" And Cells(introwcount, 15).Value = "x" Then Rows(introwcount).Hidden = True

If Cells(5, 2) <> "Other" And Cells(introwcount, 16).Value <> "x" Then Rows(introwcount).Hidden = False

If Cells(5, 2) = "Other" And Cells(27, 3) = "Limited" And Cells(introwcount, 14).Value <> "x" Then Rows(introwcount).Hidden = False

If Cells(5, 2) = "Other" And Cells(27, 3) = "Non-Limited" And Cells(introwcount, 15).Value <> "x" Then Rows(introwcount).Hidden = False

Next introwcount

End Sub

 

[craig.penny]

ARC32

Open the code module behind the sheet with the cells that should activate your code. Post in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target = Cells(180, 2) Or Target = Cells(5, 2) Or Target = Cells(27, 3) Then
  Application.Run "mcrHideRows"
End If
 
End Sub

Your code mcrHideRows needs to be in a standard module. At least it did for me - when I had it in the workbook module it caused an error.

Let me know if it worked for you.

Good luck!

 

[ARC32]

Thanks, but no joy. When I enter:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Cells(5, 2) Or Target = Cells(27, 2) Or Target = Cells(149, 2) Or Target = Cells(151, 2) Or Target = Cells(153, 2) Or Target = Cells(173, 2) Then Application.Run "mcrHideRows"
End If
End Sub

I get an error message reading "Compile Error End if without block if"

When I enter the following I seem to get the results I want, though I don't understand what the Not intersect piece is doing.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("B5,B27,B149,B151,B153,B173")) Is Nothing Then mcrHideRows
End Sub

 

[craig.penny]

Was each line of code on its own line? It works if you have it formatted correctly.

Private Sub Worksheet_Change(ByVal Target As Range)

'space

If Target = Cells(180, 2) Or Target = Cells(5, 2) Or Target = Cells(27, 3)Then 'that then is on the same line as what's above it. It's all one line.

'space

Application.Run "mcrHideRows"

'space
End If

'space
End Sub


Anyways, what you used is saying to check the intersection of the two ranges target, and the various cells. If the intersection is non empty, then the opposite of that something is nothing. So not intersection = nothing when the target is one of your cells, and then the statement evaluates to true and it calls mcrHideRows.

 

[Yabiman]

I'm tagging on here because I have a similar problem, so I don't want to start another thread. I'm using Windows 7 and Excel 2007.

I had a problem with getting a macro execute upon the change of a cell in a worksheet. The workbook I am developing has one sheet (sheet A) that displays a list of events taken from another sheet (sheet B), based on the date. Cell B10 in sheet A allows the user to enter a date. Formulas in sheet A match the date to entries in sheet B and list all the events that match the date. For lines where the date does not match, the formula displays blank. Worksheet B also sorts these events by family group, based on a sort key field.

Worksheet B lists appointments for children with dates and times. Some of the children are in the same family and would be seen together. Worksheet A lists all of the appointments on a given day. I want to have the list that appears in worksheet A separated by family groups, with a blank line in between. The result should be a page that lists a series of appointments, with the family groups single spaced, and a blank line between each family group.

To accomplish this, I included a CHAR(10) character in my formula when the family identifier of the line does not match the identifier for the line above. I turned word on so that the CHAR(10) will force an extra line in the cell. The formula for that cell is:

=IF(A16=A15,"",IF(AND(INDEX(Data,$K$10+K15,1)=$B$10,INDEX(Data,$K$10+K15,3)>0.5),""&CHAR(10)&A16&".","")). The last part of the formula inserts a null, a line feed and an item number followed by a period.

I am trying to get the sheet to an auto row height so that the row height will change for those rows with the extra line, but so far I haven't been able to get it to work. I have scoured various forums to find techniques. To trigger the automatic action, I use the following VBA code in worksheet A's module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" Then

'What do I put here?

End If
End Sub

When the user changes the date in B10 to get a new list for a new day, the lines which will have the extra space in them will change. The code above should trigger a routine that re-aligns the line height to match what is in them.

I tried calling a macro:

Sub Row_Height()
' Adjust Row Height Macro

Rows("15:43").Select
Selection.Rows.AutoFit

End Sub

but it doesn't work. I also tried putting the above code in the Worksheet_Change VBA code itself, but that didn't work either.

Based on the answer to the thread above, I tried:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" Then

Application.Run "Row_Height"

Else: MsgBox ("oops") {this is to show me if the macro fails to
execute}

End If
End Sub

Now it appears that the macro is executing, because the "oops" message box does not appear, but the result is that all of the rows are double-height, instead of just the ones containing the CHAR(10).

 

[craig.penny]

Well welcome to the boards! Sorry your welcome party is scatter brained at the moment...

I'm trying to understand what you're saying but I might be missing something. Let me see if I'm close at least.

When the user updates B10, it causes the rest of that sheet to update according to your formulas. After that happens there's a problem with getting the rows to adjust their height so everything is visible. Rows.autofit doesn't help. Is that right so far?

One thing (maybe) to try is to be more specific with the object as in, instead of

Rows("15:43").Select 
Selection.Rows.AutoFit

try

Activeworkbook.Sheets(yoursheetnamehere with quotes).rows(15:43).autofit

let me know if that makes any difference.

Also, can you post the code for your Row_Height sub?

 

[Yabiman]

Thanks for the reply. You restated the problem correctly. It took me a couple of days before I had time to try it out, but the line you gave me returned an error: "Compile Error: Expected list separator or )" The cursor was placed on the colon between 15:43.

The Row_Height sub is included in my original message, but I don't know how to properly post code in my posts.

 

[Atroxell]

If you used the second line of code suggested, make sure to put your worksheet name in Quotes and to enclose the row numbers in quotes. Should look like:

Activeworkbook.Sheets("yoursheetnamehere").Rows("15:43").AutoFit

 

[Yabiman]

I thought I had been pretty careful to check the syntax last time, but this time I did not get the error message. However, the line did not work either. I tried it both in the Row_Height macro called from the Sheet A's code and I tried inserting it directly in Sheet A.

By the way, I have discovered that selecting all the lines that I have in the command and manually clicking on Autofit Rows in the format menu does not work either. However, if I select one row and click on Autofit Rows in the format menu, it properly sets the row height. So I tried substituting "19:19" in place of 15:43" in my code. Row 19 is a row that I knew for a specified date should go to a single line height. The macro worked for a single line. So my problem may be that something is preventing Autofit from working in a range. I may have to try a For...Next loop to go line by line, although it would be much slower.

 

[mikerickson]

To trigger a macro, even when your cells of interest contain formulas, you could use this code in the Change Event

Dim keyCells as Range

Set KeyCells = Application.Union(Cells(180,2), Cells(5,2), Cells(27,3))
With KeyCells
    OnError Resume Next
    Set KeyCells = Application.Union(.Cells, .Precedents)
    On Error Goto 0
End With

If Not (Application.Intersect(Target, keyCells) Is Nothing) Then
    Rem code
End IF