Macro to clear hidden contents based off other cell values

[jshaner92]

Hello,

I am working on a product configurator in excel. I have macros created (and successfully work) that will hide and unhide rows based off of cell values. Below is the portion of the macro i have written for hiding and unhiding rows. The one section (columns CG: DN) i am trying to clear contents based off of the values of cells telling it to clear or keep it. These cells aren't always necessary so I want to delete them to reduce the file size. This section is also hidden. I can't seem to get it to successfully unhide the columns, clear the cells, and continue working like nothing changed. Sorry if not enough information is in this. It is my first post to the forum and I am still self learning (thanks to these forums and google) how to do macros in Excel. So they might not be pretty, but I can get most of them to work!

The specific part of the macro i am having issues with is highlighted below in green. This macro is created on a template tab that is copied to create new tabs. So they same macro can be used on multiple tabs within the same work book, with each sheet having a different result.


***The error i keep getting is a "Run-time error '28': out of stack space" when trying to call the ClearInactiveKval sub***

Sub LiteFrameKitOnlyHide()
ActiveSheet.Unprotect
Rows("19:102").EntireRow.Hidden = True
ActiveSheet.Protect
End Sub
Sub LiteFrameKitOnlyUnHide()
ActiveSheet.Unprotect
Rows("19:102").EntireRow.Hidden = False
ActiveSheet.Protect
End Sub
Sub LiteKitSelectionHide()
ActiveSheet.Unprotect
Rows("103:127").EntireRow.Hidden = True
ActiveSheet.Protect
End Sub
Sub LiteKitSelectionUnHide()
ActiveSheet.Unprotect
Rows("103:127").EntireRow.Hidden = False
ActiveSheet.Protect
End Sub
Sub OneStrikeHide()
ActiveSheet.Unprotect
Rows("55:63").EntireRow.Hidden = True
ActiveSheet.Protect
End Sub
Sub OneStrikeUnHide()
ActiveSheet.Unprotect
Rows("55:63").EntireRow.Hidden = False
ActiveSheet.Protect
End Sub
Sub TwoStrikeHide()
ActiveSheet.Unprotect
Rows("64:72").EntireRow.Hidden = True
ActiveSheet.Protect
End Sub
Sub TwoStrikeUnHide()
ActiveSheet.Unprotect
Rows("64:72").EntireRow.Hidden = False
ActiveSheet.Protect
End Sub
Sub MiscHardwareHide()
ActiveSheet.Unprotect
Rows("73:102").EntireRow.Hidden = True
ActiveSheet.Protect
End Sub
Sub MiscHardwareUnHide()
ActiveSheet.Unprotect
Rows("55:102").EntireRow.Hidden = False
ActiveSheet.Protect
End Sub
Sub ClearActiveKval()
ActiveSheet.Unprotect
Range("AZ1:CF800").Clear
ActiveSheet.Protect
End Sub


Sub ClearInactiveKval()
ActiveSheet.Unprotect
Range("CG1: DN800").Clear (*note the space between : and D is only for this post. The actual macro doesn't have a space)
ActiveSheet.Protect
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
Application.ScreenUpdating = False


If Range("c5") = "Clear" Then
Call ClearInactiveKval
End If


If Range("c1") = "Hide" Then
Call LiteFrameKitOnlyHide
Else
Call LiteFrameKitOnlyUnHide
End If


If Range("c14") = "Hide" Then
Call LiteKitSelectionHide
Else
Call LiteKitSelectionUnHide
End If


If Range("c40") = "Hide" Then
Call OneStrikeHide
Else
Call OneStrikeUnHide
End If


If Range("c41") = "Hide" Then
Call TwoStrikeHide
Else
Call TwoStrikeUnHide
End If


If Range("c42") = "Hide" Then
Call MiscHardwareHide
Else
Call MiscHardwareUnHide
End If


End Sub

[Paul_Hossler]

Welcome to the forum - please take a minute and read the FAQs at the link in my sig

Didn't test, but I made some unsolicited suggestions.

I 'think' the naked Range one was the issue

Option Explicit


'example of passing a parameter to the sub so
'that you don't need both a Hide and Unhide version
Sub LiteFrameKitOnly(Hide As Boolean)
    With ActiveSheet
        .Unprotect
        .Rows("19:102").EntireRow.Hidden = Hide
        .Protect
    End With
End Sub




'Using naked Range (i.e. no dot or no specific worksheet will default to what ever the active worksheet is,
'which may not be what you want
'I added the dot and surrounded it with a With / End With so that all 3 statements 'belong' to
the With Activesheet
Sub ClearActiveKval()
    With ActiveSheet
        .Unprotect
        .Range("AZ1:CF800").Clear
        .Protect
    End With
End Sub




Sub ClearInactiveKval()
    With ActiveSheet
        .Unprotect
        .Range("CG1:DN800").Clear
        .Protect
    End With
End Sub




'this was very inefficient since it would check everything every time any cell changed
'I think you only need it to check when c5, c1, ... changed
'
'also good idea to disable events just in case the event handler changes something and it calls itself
'
'Select Case will 'switch' using the first cell of the change and if there' no match
'it won't do anything. Cleaner than a lot of If/Thens
'
'the (r.value = "Hide") returns a boolean (True / False) which is passed (see the first suggestion above)
'to the more general purpose subs to hide or unhide the rows


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    
    ActiveSheet.Activate    ' activesheet is active :-)


    Set r = Target.Cells(1, 1)
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Select Case r.Address(False, False)
        Case "c5"
            If r.Value = "Clear" Then Call ClearInactiveKval


        Case "c1"
            Call LiteFrameKitOnlyHide(r.Value = "Hide")


        Case "c14"
            Call LiteKitSelection(r.Value = "Hide")


        Case "c40"
            Call OneStrike(r.Value = "Hide")


        Case "c41"
            Call TwoStrike(r.Value = "Hide")


        Case "c42"
            Call MiscHardware(r.Value = "Hide")
    End Select


    Application.EnableEvents = True
    Application.ScreenUpdating = True


End Sub

Edit -- Now that you added the error message (Out of Stack Space), I think it was your event handler calling itself over and over.

The Application.DisableEvents should fix that

[jshaner92]

While I tried to put in the code you had provided, I wasn't able to get any of the subs to run at all. But, I did put in the Application.DisableEvents with the code I already had and that worked perfectly for what I needed. I can't believe it was 1 line of code that was messing up the whole thing!

[jshaner92]

Your solution of adding the Application.DisableEvents fixed my issues I was having on this. Thank you!

I am, however, interested in knowing how to write the code so that the cells are only hidden or unhidden when the specific cell changes, not when any cell is changed.

[p45cal]

re: "'Using naked Range (i.e. no dot or no specific worksheet will default to what ever the active worksheet is, which may not be what you want"

There is an exception to this, if the code is in a sheet's code-module, then unqualified (naked) range references refer to cells on that sheet, regardless of which sheet is the active sheet.
So if code somewhere changes a value on that sheet while another sheet is active:
using ActiveSheet.Rows… will effect changes on the active sheet's rows
using Rows(… will effect changes on the rows of the sheet whose code-module has the code.

So it might just be safer to use unqualified range references here! This assumes that all the code supplied by the OP is in a sheet's code-module, which it looks like it might be, and that the sheet he wants to work on is that sheet with the code in, which looks very likely.