Stop user from Deleting hyperlinks on sheet

[sean1541]

How can i stop other users from deleting hyperlinks from the sheet.
When i protect the sheet, they can't use the hyperlink
any help would be great thanks

 

[Joe4]

Are the hyperlinks in a specific part of the sheet?
If so, can you tell us what that is?

I am thinking that you may be able to use VBA in order to immediately undo any deletions to specific ranges on your sheet.

 

[sean1541]

Are the hyperlinks in a specific part of the sheet?
If so, can you tell us what that is?

I am thinking that you may be able to use VBA in order to immediately undo any deletions to specific ranges on your sheet.

all in column D

 

[Joe4]

Something like this would prevent a user from deleting single cells in column D.
(Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up):

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.Count > 1 Then Exit Sub
   
'   Exit if update not to column D
    If Target.Column <> 4 Then Exit Sub
   
'   Exit if if cell not cleared out
    If Target.Value <> "" Then Exit Sub
   
'   Undo deletion
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
   
End Sub

 

[Joe4]

Note: It looks like this may be unnecessary. It appears if you select the right protection options, you should be able to protect the cells from being deleted, but the hyperlinks should still work.
See here: Redirecting

 

[sean1541]

Something like this would prevent a user from deleting single cells in column D.
(Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up):

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.Count > 1 Then Exit Sub
  
'   Exit if update not to column D
    If Target.Column <> 4 Then Exit Sub
  
'   Exit if if cell not cleared out
    If Target.Value <> "" Then Exit Sub
  
'   Undo deletion
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
  
End Sub

Works great thanks

 

[Joe4]

You are welcome.
Also note the follow-up post I made, which shows it may not even be necessary.

 

[sean1541]

You are welcome.
Also note the follow-up post I made, which shows it may not even be necessary.

I have tried that when I protect sheet I cannot use a hyperlink.

 

[Joe4]

I have tried that when I protect sheet I cannot use a hyperlink.

That is weird, it works just fine for me.
Are you sure that you have the "Select locked cells" box checked?

If so, maybe it is something that behaves differently on a Mac (I use Windows).