Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Report Body")
Select Case Target.Address(False, False)
Case "A1CommentsHide"
.Range("A1Comments").Rows.Hidden = Target.Value = "Hide"
Case "A1ImagesHide"
.Range("A1Images").Rows.Hidden = Target.Value = "Hide"
End Select
End With
End Sub
Hello!
I'm not good with VBA, at all, but I have put together the following code that I could really use your input on.
I am trying to use a value (hide or show) in a named range in my input sheet to hide rows in a different sheet. I will be adding rows to through its life and figure that hard coding is useless for this application?? The Cases below seem to work fine, but I can’t figure out how to get it to evaluate the range as the…whatever it is???
I have a workbook, in the workbook is an input sheet and a report sheet (and unrelated others).
I have built and formatted the sheets to streamline my report writing process. I has comment builders that essentially concatenates the sections comments, but sometimes there is no need for comments; sometimes I feel the need to insert images.
the report sheet is all formatted so that everything I need is ‘blocked-in’ and I have two drop down cells that contain hide or show (currently EZ22 and EZ23). (I’ll try to add an image for clarity).
(input1.png- I'll try add images separately)
I would like to name EZ22 “A1CommentsHide” and EZ23 “A1ImagesHide” and I would like to use that named range?? as part of the case function in the code. I found some example code from an old post and wiggled it a bit to figure it out / get it to work to what I think I need, but when I try to get it to look at “A1CommentsHide” … well, no dice. ( I want it to hide a full row named “A1Comments” on sheet Report Body and “A1ImagesHide” to hide two full rows named “A1Images” (they are technically rows 21 and rows 22,23 respectively) also on Report Body).
the rows/info contained in 22 and 23 will likely grow to a substantial number over time, so I figured that a range (which should move with the relative information as rows are added above) is the way to go.
(report1.png)
The problem seems to be when I switch the reference to "EZ22" to Range("A1Comments") where things go wrong???
Thanks!
Bookmarks