Activating a macro when a cell value goes to 1

[frankday]

I want to run a specific macro "Line129cal" when 1 is placed in cell "H127". I also want to run macro"Line129cal" when the bellow macro is run. "Toggle_601"

Sub Toggle_601()
If Rows("35:39").EntireRow.Hidden = True Then
Call Expand_601
Else
Call Callapes_601
End If
End Sub

 

[Joe4]

I want to run a specific macro "Line129cal" when 1 is placed in cell "H127".

You would use a Worksheet_Change event procedure, which is VBA code that runs automatically upon some cell being updated.
This code must be placed in the proper sheet module in order for it to run automatically.
Simply go to the sheet with the cell H127 that you are updating, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window.

Private Sub Worksheet_Change(ByVal Target As Range)
'   Check to see if cell H127 is being updated
    If Target.Address(0, 0) = "H127" Then
'       Check to see if the value is being set to 1
        If Target = 1 Then
'           Call/run your macro
            Call Line129cal
        End If
    End If
End Sub

I also want to run macro"Line129cal" when the bellow macro is run. "Toggle_601"

It looks like you already know how to call other macros, so just go ahead and add another call statement to your current code:

[COLOR=#333333]Sub Toggle_601()
[/COLOR]   [COLOR=#ff0000] Call Line129cal  [/COLOR]
[COLOR=#333333]    If Rows("35:39").EntireRow.Hidden = True Then[/COLOR]
[COLOR=#333333]        Call Expand_601[/COLOR]
[COLOR=#333333]    Else[/COLOR]
[COLOR=#333333]        Call Callapes_601[/COLOR]
[COLOR=#333333]    End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[frankday]

That worked great. How I do the opposite. If I put a 0 in cell H127 I want it to unhide and a 1 would cause that row to hide.

I also want to do this with multiple lines across the worksheets but they have to be toggled separately. I have the code already to make them Hidden. I just don't know how to make multiple change macros on one sheet. "Line129cal" is the hide macro. "Line129ext" is the unhide macro.

 

[Joe4]

I also want to do this with multiple lines across the worksheets but they have to be toggled separately. I have the code already to make them Hidden. I just don't know how to make multiple change macros on one sheet. "Line129cal" is the hide macro. "Line129ext" is the unhide macro.

If they all do the same thing, you do not need multiple change macros. You just need to clearly define what ranges they should be applied to.
If you provide us with the required detail, we can probably help you with that.

 

[frankday]

Thanks for the help.

I have sheets labeled for each month of the year. January, February, March...... and another sheet labeled "Chart of Accounts" I have a list of accounts that show up on each month and the Chart of Accounts. On the Chart of accounts sheet next to each account I want to be able to place a 1 for Hide that row across all months and 0 for unhide that row across all months. So if I want to hide 3 different accounts I have to put a 1 in each cell next to that account.

Lets say rows A1-A20 have the accounts listed across all sheets.
If I want to hide row 3 I would place a 1 in "Chart of accounts B3" To unhide the row I would place a 0 in "B3"
At the same time I want to hide row 10. I would put a 1 in "Chart of accounts B10 To unhide the same row I would put a 0 in "B3"
And so on. I can hide 10 accounts or 1 account it doesn't matter.

Does that help

 

[Joe4]

OK, try putting these VBA procedures in the "Chart of Accounts" module in VBA. I think it should do what you want across all sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
'   This checks for changes to column B on the "Chart of Accounts" sheet

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
    Dim hide As Boolean
    
'   Check to see if any values updated in column B
    Set rng = Intersect(Target, Range("B:B"))
    
'   If no updates in column B, exit
    If rng Is Nothing Then Exit Sub
    
'   Loop through all updated cells in column B
    For Each cell In rng
'       Get row number of updated value
        r = cell.Row
        Select Case cell.Value
'           If set to 0, set hide to false and call macro
            Case 0
                hide = False
                Call MyHideRows(r, hide)
'           If set to 1, set hide to true and call macro
            Case 1
                hide = True
                Call MyHideRows(r, hide)
        End Select
    Next cell
    
    Sheets("Chart of Accounts").Activate

End Sub


Sub MyHideRows(rw As Long, hd As Boolean)
'   rw = row number to apply to
'   hd = boolean value that indicates whether to hide row or not

    Dim sht As Worksheet

    Application.ScreenUpdating = False

'   Loop through all sheets
    For Each sht In Worksheets
'       Only apply if sheet name not "Chart of Accounts"
        If sht.Name <> "Chart of Accounts" Then
            sht.Activate
'           Hide row
            sht.Rows(rw).EntireRow.Hidden = hd
        End If
    Next sht
    
    Application.ScreenUpdating = True
    
End Sub

 

[frankday]

That code worked great. The only issue I have is that affects all sheets except "Chart of Accounts". I have many sheets in this workbook. I want this macro to affect only sheets "January, February, March, ....." All 12 months. Any suggestions?

 

[Joe4]

You can create an array of sheet names that you want to apply this to, and iterate through the array, like this:

Sub MyHideRows(rw As Long, hd As Boolean)
'   rw = row number to apply to
'   hd = boolean value that indicates whether to hide row or not

    Dim mnths
    Dim i As Long
    
    Application.ScreenUpdating = False
    
'   Set array of months to loop through
    mnths = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

'   Loop through all month sheets
    For i = LBound(mnths) To UBound(mnths)
        Sheets(mnths(i)).Activate
'       Hide row
        Sheets(mnths(i)).Rows(rw).EntireRow.Hidden = hd
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

(the Worksheet_Change procedure is fine and does not require any changes)

 

[frankday]

Ok, you are awesome. One more thing. I am trying to create a button that will place the 1 or the 0. I recorded this macro and it does what I asked of it. But it doesn't act like a key stroke. If I type the 1 or 0 it does what I asked. Using the macro it doesn't recognize the change. Any suggestions?

Sub Macro1()
'
' Macro1 Macro
'


'
Range("B11").Select
ActiveCell.FormulaR1C1 = "1"
Range("A11").Select
End Sub

 

[Joe4]

It should work. Make sure you are on the correct sheet when running that code.
Because there is so sheet reference, it will put 1 in cell B11 on whatever sheet is active when the code is being run.

By the, way you can simplify that code to:

Sub Macro1()
    Sheets("Chart of Accounts").Activate
    Range("B11") = 1
End Sub

and it should work.

If it does not, then it sounds like you may have either:
- Changed the name of the "Worksheet_Change" procedure. Note that this only works automatically if it has that EXACT name - you are not allowed to change it
- disabled your events (see this question I just helped someone with: https://www.mrexcel.com/forum/excel-questions/1072837-help-code-behind-tab-not-executing.html)