Automate Collapse/Expand Code?

[Boston]

Hi,

I'm trying to automate a micro to collapse/expand certain cells in a spreadsheet as data is put in (i.e. if Cell A>0, then Cells B-F will expand, if Cell A<0, then Cells B-F will collapse). Currently, the code WILL do that but only AFTER clicking the A column. Below is the code I use, any help would be greatly appreciated

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("$A12:$A100")) Is Nothing Then

If Range("C27").Value = 0 Then
Rows("28:30").EntireRow.Hidden = True
Else
Rows("28:30").EntireRow.Hidden = False
End If

If Range("C35") = 0 Then
Rows("36:38").EntireRow.Hidden = True
Else
Rows("36:38").EntireRow.Hidden = False
End If
End If

End Sub

 

[CalcSux78]

Try changing the name of your sub to:

Private Sub Worksheet_Change(ByVal Target As Range)

 

[Boston]

Try changing the name of your sub to:

Private Sub Worksheet_Change(ByVal Target As Range)

the command fails to work if I take out the 'Selection" part of the name, do you have any other ideas?

 

[CalcSux78]

I guess I'm confused on your question vs your sample code pasted. Looks like it's doing something totally different than what you're describing.

Your code in the first post hides rows based off of 2 different cell values but is triggered by selecting something in range A12:A100.

If you are wanting the change to happen when the value changes in your target cell, use this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Watch1 As Range
Dim Watch2 As Range
Set Watch1 = Intersect(Target, Range("C27"))
Set Watch2 = Intersect(Target, Range("C35"))
        'change on sheet must be in Watch1 range to trigger event
If Not Watch1 Is Nothing Then
    If Watch1.Value = 0 Then
        Rows("28:30").EntireRow.Hidden = True
    Else
        Rows("28:30").EntireRow.Hidden = False
    End If
End If
    
If Not Watch2 Is Nothing Then
    If Watch2.Value = 0 Then
        Rows("36:38").EntireRow.Hidden = True
    Else
        Rows("36:38").EntireRow.Hidden = False
    End If
End If
 
End Sub

If I'm way off, maybe post a sample of what the sheet has on it (confidential information omitted) or a bit more detail on what is needed.

 

[Boston]

I keep screwing up vba code

*I've lost my text book and i tried my best but this looks very wrong to me *

I need to have one sheet in a workbook to have certain rows collapse/expand when certain cells in the master sheet (dashboard) are imputed (>0 would expand, <0 would collapse). The bottom code is ' collapseexpand '

Sub CopyPaste()Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Dashboard" Then
ws.Select
ws.Application.Run "collapseexpand"
End If
Next
End Sub


Sub collapseexpand()
Range("C13").Select
Selection.Copy
Sheets("more extra").Select
ActiveSheet.Paste
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Watch1 As Range
Dim Watch2 As Range
Set Watch1 = Intersect(Target, Range("C27"))
Set Watch2 = Intersect(Target, Range("C35"))
        'change on sheet must be in Watch1 range to trigger event
If Not Watch1 Is Nothing Then
    If Watch1.Value = 0 Then
        Rows("28:30").EntireRow.Hidden = True
    Else
        Rows("28:30").EntireRow.Hidden = False
    End If
End If
    
If Not Watch2 Is Nothing Then
    If Watch2.Value = 0 Then
        Rows("36:38").EntireRow.Hidden = True
    Else
        Rows("36:38").EntireRow.Hidden = False
    End If
End If
 
End Sub

 

[Joe4]

Re: I keep screwing up vba code

I merged the two threads together, as they appear to be dealing with the same issue.

 

[Boston]

Re: I keep screwing up vba code

I merged the two threads together, as they appear to be dealing with the same issue.

oh, your right, they are, thanks!