G
Geoff
I want to 'remind' users, including myself, not to edit
particular cells on Sheet1 and do this on Sheet2
instead. I did not want to use Protect and UnProtect
measures. The code below creates a Comment and changes
the cell font colour if an 'alert' cell is selected.
These changes are then reversed when the user makes
another selection. So I have achieved the objective of
providing a reminder. BUT, is it the best user interface?
I think that a long way back I saw some code using a
mouse move or mouse over event which changed the colour
of cells as it tracked. This would deliver a
similar 'message' but would be quite cool to do. Can
this be done?
Appreciate any help
Geoff
Code for Comment and colour change, aplogies for th
tabbing:
Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
'Because comments cannot be inserted on multiple
selections the procedure ignores multiple selections
selcount = 0
For Each p In Selection
selcount = selcount + 1
If selcount > 1 Then Exit Sub
Next
'Set alert targets as a range
Set dontchangethishere = Union( *** alert cell addresses
****)
Select Case Target.Column
Case 2 'Within column B
Select Case Target.Row 'Within target rows
Case ***** Rownumbers of alert cells***
If Target.Comment Is Nothing Then
Range(Target.Address).AddComment "To edit
please go to sheet 2"
With Range(Target.Address).Comment.Shape.TextFrame
.Characters.Font.Name = "Comic Sans MS"
.Characters.Font.FontStyle = "Bold"
.Characters.Font.Size = 10
.AutoSize = True
End With
Range(Target.Address).Font.Color = vbRed
Else
Range(Target.Address).Comment.Delete
Range(Target.Address).Font.ColorIndex =
xlColorIndexAutomatic
End If
Case Else ''Not in target rows
For Each c In dontchangethishere
If Not c.Comment Is Nothing Then
c.Comment.Delete
c.Font.ColorIndex = xlColorIndexAutomatic
End If
Next
End Select
Case Else ''Not in column B
For Each c In dontchangethishere
If Not c.Comment Is Nothing Then
c.Comment.Delete
c.Font.ColorIndex = xlColorIndexAutomatic
End If
Next
End Select
End Sub
particular cells on Sheet1 and do this on Sheet2
instead. I did not want to use Protect and UnProtect
measures. The code below creates a Comment and changes
the cell font colour if an 'alert' cell is selected.
These changes are then reversed when the user makes
another selection. So I have achieved the objective of
providing a reminder. BUT, is it the best user interface?
I think that a long way back I saw some code using a
mouse move or mouse over event which changed the colour
of cells as it tracked. This would deliver a
similar 'message' but would be quite cool to do. Can
this be done?
Appreciate any help
Geoff
Code for Comment and colour change, aplogies for th
tabbing:
Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
'Because comments cannot be inserted on multiple
selections the procedure ignores multiple selections
selcount = 0
For Each p In Selection
selcount = selcount + 1
If selcount > 1 Then Exit Sub
Next
'Set alert targets as a range
Set dontchangethishere = Union( *** alert cell addresses
****)
Select Case Target.Column
Case 2 'Within column B
Select Case Target.Row 'Within target rows
Case ***** Rownumbers of alert cells***
If Target.Comment Is Nothing Then
Range(Target.Address).AddComment "To edit
please go to sheet 2"
With Range(Target.Address).Comment.Shape.TextFrame
.Characters.Font.Name = "Comic Sans MS"
.Characters.Font.FontStyle = "Bold"
.Characters.Font.Size = 10
.AutoSize = True
End With
Range(Target.Address).Font.Color = vbRed
Else
Range(Target.Address).Comment.Delete
Range(Target.Address).Font.ColorIndex =
xlColorIndexAutomatic
End If
Case Else ''Not in target rows
For Each c In dontchangethishere
If Not c.Comment Is Nothing Then
c.Comment.Delete
c.Font.ColorIndex = xlColorIndexAutomatic
End If
Next
End Select
Case Else ''Not in column B
For Each c In dontchangethishere
If Not c.Comment Is Nothing Then
c.Comment.Delete
c.Font.ColorIndex = xlColorIndexAutomatic
End If
Next
End Select
End Sub