Results 1 to 2 of 2

VBA using a named range in a 'case' arguement?

Threaded View

  1. #1
    Registered User
    Join Date
    02-26-2013
    Location
    plymouth, ma - usa
    MS-Off Ver
    excel
    Posts
    5

    VBA using a named range in a 'case' arguement?

    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!
    Last edited by franklinjim; 08-25-2014 at 08:59 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] determining if cell is part of named range and what that named range is
    By stnkynts in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2014, 07:56 PM
  2. Looping Mutliple Named Resized Ranges in next empty row below another named range
    By gingumdog in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2014, 08:15 PM
  3. Adding row to a named range-updating the named range address
    By kjsconv in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2013, 11:22 PM
  4. Replies: 1
    Last Post: 02-27-2013, 12:00 AM
  5. [SOLVED] Determine what Named Range the Target Address is and return Named Range Name
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2012, 10:49 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1