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.

Right click the "Bible" sheet, select "View Code" and paste the following:

Private StartCell As Range
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

If Target.SubAddress <> StartCell.Address Then Exit Sub

If Range("A13").EntireRow.Hidden Then
    Range("13:64").EntireRow.Hidden = False
    StartCell.Value = "See Less"
Else
    Range("13:64").EntireRow.Hidden = True
    StartCell.Value = "Please Click here to see more"
End If

End Sub
Public Sub HideRows()

With ThisWorkbook.Worksheets("Bible")
    Set StartCell = .Cells(.Rows.Count, "J").End(xlUp)
    StartCell.Value = "Please Click here to see more"
    .Range("13:64").EntireRow.Hidden = True
    .Hyperlinks.Add Anchor:=StartCell, Address:="", SubAddress:=StartCell.Address, TextToDisplay:=StartCell.Text, ScreenTip:="Click Here"
End With

End Sub

Call HideRows to set up the initial hiding and create a hyperlink on the cell. Then you should be able to toggle the visible rows by clicking the hyperlink. Does that achieve what you want?

WBD

OK. I saw your original had ".Offset(0,0)" which is superfluous but perhaps this:

Private StartCell As Range
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

If Target.SubAddress <> StartCell.Address Then Exit Sub

If Range("A13").EntireRow.Hidden Then
    Range("13:64").EntireRow.Hidden = False
    StartCell.Value = "See Less"
Else
    Range("13:64").EntireRow.Hidden = True
    StartCell.Value = "Please Click here to see more"
End If

End Sub
Public Sub HideRows()

With ThisWorkbook.Worksheets("Bible")
    Set StartCell = .Cells(.Rows.Count, "J").End(xlUp).Offset(1, 0)
    StartCell.Value = "Please Click here to see more"
    .Range("13:64").EntireRow.Hidden = True
    .Hyperlinks.Add Anchor:=StartCell, Address:="", SubAddress:=StartCell.Address, TextToDisplay:=StartCell.Text, ScreenTip:="Click Here"
End With

Application.EnableEvents = True

End Sub

Make sure this is in the "Bible" sheet code window and not a separate module.

WBD

You need to prefix with the code name of the sheet. For example:

Call Sheet1.HideRows

But replace Sheet1 with the code name of the sheet (taken from the tree view on the left). It's probably not "Bible" since that's the name of the sheet; not the code name. If in doubt, press Ctrl+G and type this in the immediate window and hit Enter:

? Sheets("Bible").CodeName

That will give you the right prefix to put in front of ".HideRows"

WBD