Run a Macro By Clicking a Named Range

**WideBoyDixon** · 10-02-2018, 11:56 AM

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

**Ratso** · 10-02-2018, 12:25 PM

Works Perfectly to an extent.

How do I add a lastrow to a private function as I have always used Option Explicit as this range changes and how do I call it from a Module?

I have this so far in a User Module

'<----COUNT VALUES IN COLUMN J AND IF GREATER THAN 10
    If Range("J:J").SpecialCells(xlCellTypeConstants, xlTextValues).Cells.Count > 10 Then
    Call HideRows
    Else
    Bible.Rows("13:64").EntireRow.Hidden = False
    End If

but I'm getting a sub or function error not defined.

I moved it to a Module and the Hyperlink then doesn't work. But you did say not to put in a separate module so I never expected any different.

Sorry

Run a Macro By Clicking a Named Range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Run a Macro By Clicking a Named Range

Re: Run a Macro By Clicking a Named Range

Thread Information

Users Browsing this Thread

Similar Threads

Adjust existing macro to pick up specific named worksheet and copy to named range

Show column to left of named range when clicking hyperlink

Macro to run one formula for range of cells by clicking on cell

excel macro range define to another sheet / named range

Accessing Various Cells in a Named Range -Clicking/Dragging to Populate.

Macro to copy named range "box" values to named range "box2". Both are size of 5x26

Macro code to change cell range to named range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions