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.
Bookmarks