Results 1 to 7 of 7

Run a Macro By Clicking a Named Range

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-30-2014
    Location
    London, England
    MS-Off Ver
    365 & 2010
    Posts
    107

    Smile Run a Macro By Clicking a Named Range

    Hi,

    Me yet again.

    Ok... this may be pretty simple but I'd be damned if I can work it out.

    I have a macro that creates a named range at the bottom of a list and hides rows 13:64. (Code below)

    Sub PleaseClick_DynamicRange_1()
    
    Dim Bible As Worksheet
    Dim StartCell As Range
    
    
    Set Bible = ThisWorkbook.Worksheets("Bible")
    Set StartCell = Range("J" & Rows.Count).End(xlUp).Offset(0, 0)
    
    'IF NO PLEASECLICK FUNCTION THEN IGNORE THE DELETE FUNCTION AND RUN AFTER EXIT HERE
    On Error GoTo errHandler
    
    'DELETE ANY CURRENT PLEASECLICK NAMED RANGES
        Bible.Names("PLEASECLICK").Delete
    
    exitHere:
    
    'ADD PLEASECLICK NAMED RANGES
    
        Bible.Names.Add _
        Name:="PLEASECLICK", RefersTo:=StartCell
        
        Bible.Rows("13:64").EntireRow.Hidden = True
        StartCell.Value = "Please Click here to see more"
    
    errHandler:
        Resume Next
    
    End Sub
    I also have another macro that should when "Please Click To see more" is clicked unhides rows 13:64, and replaces "Please Click to see more" with "see less" (code below)

    Sub SeeLess_DynamicRange_1()
    
    
    Dim Bible As Worksheet
    Dim StartCell As Range
    
    
    Set Bible = ThisWorkbook.Worksheets("Bible")
    Set StartCell = Range("J" & Rows.Count).End(xlUp).Offset(0, 0)
    
    'IF NO SEELESS FUNCTION THEN IGNORE THE DELETE FUNCTION AND RUN AFTER EXIT HERE
    On Error GoTo errHandler
    
    'DELETE ANY CURRENT SEELESS NAMED RANGES
        Bible.Names("SEELESS").Delete
    
    exitHere:
    
    'ADD SEELESS NAMED RANGES
    
        Bible.Names.Add _
        Name:="SEELESS", RefersTo:=StartCell
        
        Bible.Rows("13:64").EntireRow.Hidden = False
         StartCell.Value = "See Less"
    
    errHandler:
        Resume Next
        
    
    End Sub
    The issue I am having is I would like the user to click the relevant named range (SEELESS) to hide rows 13:64 or (PLEASECLICK) to unhide rows 13:64 on the worksheet.

    Actually to add a spanner in the works the list above the named ranges can vary some could be 20 lines some could be 50 so ideally I just want to hide anything after line 13 and above (PLEASECLICK)

    I know this is a workbook change event and I've tried some bits of code but I can't seem to get it working, but it could be the Macro's contradicting each other?

    Any help would be greatly appreciated.
    Last edited by Ratso; 10-02-2018 at 10:57 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adjust existing macro to pick up specific named worksheet and copy to named range
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-08-2017, 12:03 PM
  2. Show column to left of named range when clicking hyperlink
    By Crispld in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-31-2014, 06:48 PM
  3. Macro to run one formula for range of cells by clicking on cell
    By christopheralan88 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2014, 02:50 PM
  4. excel macro range define to another sheet / named range
    By koi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 10:25 AM
  5. Replies: 0
    Last Post: 08-08-2012, 01:02 PM
  6. Replies: 3
    Last Post: 06-04-2011, 10:56 AM
  7. Macro code to change cell range to named range
    By Orlic in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2008, 11:51 AM

Tags for this Thread

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