Hyperlinks on Protected Sheets

[Rich]

Hi,

I have a protected sheet with some hyperlinks on it. However I am unable to
click on any of the hyperlinks.

The only protection option with anything to do with hyperlinks is the Allow
Insert Hyperlinks option, which has no effect.

My goal is to have a protected sheet with formulas hidden, yet the user can
click on hyperlinks.

BTW the Hyperlinks are inserted as worksheet formulas if that matters...

Thanks
Richard

 

[keepITcool]

when protecting the sheet:
turn ON :
allow users to select locked cells
allow users to select unlocked cells

then it works for me in xlXP and xl2003
in xl97 i also didnt have any problems.

cell:locked ON /hidden ON formula =hyperlink("http://microsoft.com")






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rich wrote :

 

[Rich]

Thanks for the tip.

Works for me.

I was hoping to be able to hide the cursor, the sheet is a kind of
dashboard, but being able to click links is more important!

 

[keepITcool]

hmm...

Let's get creative!

since a dashboard should fit on 1 screen,
there should be no need to scroll...
and IF you dont want to see that graveyard box...
try following (quick test, seems to work)


in thisworkbook
Private Sub Workbook_Open()
Sheet1.ScrollArea = "A1:H24"
Sheet2.Activate
Sheet1.Activate
End Sub


in sheet1 code module

Private Sub Worksheet_Activate()
Worksheet_SelectionChange ([a1])
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(Me.ScrollArea)) Is Nothing Then
Me.Cells(1, 256).Activate
End If
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rich wrote :

 

[Rich]

Thans for the tip, but I should have made my requirements fully known!!!

The sheet is in a "data file" and I don't want it to have any VBA code, so
people don;t have to get the warning every time if they choose not to trust
me. All code will be in the add-in that will be signed and approved once
only.

I therefore assume that I cannot handle events on that sheet.

Thanks for the tip anyway.
Rich

hmm...

Let's get creative!

since a dashboard should fit on 1 screen,
there should be no need to scroll...
and IF you dont want to see that graveyard box...
try following (quick test, seems to work)


in thisworkbook
Private Sub Workbook_Open()
Sheet1.ScrollArea = "A1:H24"
Sheet2.Activate
Sheet1.Activate
End Sub


in sheet1 code module

Private Sub Worksheet_Activate()
Worksheet_SelectionChange ([a1])
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range(Me.ScrollArea)) Is Nothing Then
Me.Cells(1, 256).Activate
End If
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Rich wrote :

Thanks for the tip.

Works for me.

I was hoping to be able to hide the cursor, the sheet is a kind of
dashboard, but being able to click links is more important!

 

[keepITcool]

Rich wrote :

I therefore assume that I cannot handle events on that sheet.

I assumed... famous last words

You can set up an application level event handler in the addin..

in thisworkbook of the addin...
Option Explicit

Dim WithEvents appXL As Application

Private Sub appXL_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Debug.Print Target.Address(external:=True)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set appXL = Nothing
End Sub

Private Sub Workbook_Open()
Set appXL = Application
End Sub

now you'll see the address of ANY cell that is changed...

see the dropdowns in the codepane...
select appXL on the left..
check the available events on the right.