jshaner92
12-13-2019, 11:37 AM
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
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