Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically change cell comments
Hi,
I have a range of cells where the user will be able to select an option code from a data validation list. I would like to be able to automatically update cell comments to provide a usefull description depending on which option code has been selected from the validation list. I have the following code which works for a single cell and updates the referenced comment for that cell. How can I change this so it will work for any cell within a specified range and update the comment for that cell ? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F2")) Is Nothing Then If Target.Value = "RM4" Then Comments(1).Text Target & " " & Sheet4.Range("C4") ElseIf Target.Value = "RM5" Then Comments(1).Text Target & " " & Sheet4.Range("C6") ElseIf Target.Value = "" Then Comments(1).Text "Selection Required" End If End If end sub thanks, Marcus |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically change cell comments
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("F2:F10")) Is Nothing Then If Target.Value = "RM4" Then Target.Comment.Text Target & " " & Sheet4.Range("C4") ElseIf Target.Value = "RM5" Then Target.Comment.Text Target & " " & Sheet4.Range("C6") ElseIf Target.Value = "" Then Target.Comment.Text "Selection Required" End If End If End Sub Works, but assumes the comment for the cell already exists. -- Regards, Tom Ogilvy "Marcus B" wrote: Hi, I have a range of cells where the user will be able to select an option code from a data validation list. I would like to be able to automatically update cell comments to provide a usefull description depending on which option code has been selected from the validation list. I have the following code which works for a single cell and updates the referenced comment for that cell. How can I change this so it will work for any cell within a specified range and update the comment for that cell ? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F2")) Is Nothing Then If Target.Value = "RM4" Then Comments(1).Text Target & " " & Sheet4.Range("C4") ElseIf Target.Value = "RM5" Then Comments(1).Text Target & " " & Sheet4.Range("C6") ElseIf Target.Value = "" Then Comments(1).Text "Selection Required" End If End If end sub thanks, Marcus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically change cell comments
Worked a charm
thanks, Marcus "Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 Then Exit Sub If Not Intersect(Target, Range("F2:F10")) Is Nothing Then If Target.Value = "RM4" Then Target.Comment.Text Target & " " & Sheet4.Range("C4") ElseIf Target.Value = "RM5" Then Target.Comment.Text Target & " " & Sheet4.Range("C6") ElseIf Target.Value = "" Then Target.Comment.Text "Selection Required" End If End If End Sub Works, but assumes the comment for the cell already exists. -- Regards, Tom Ogilvy "Marcus B" wrote: Hi, I have a range of cells where the user will be able to select an option code from a data validation list. I would like to be able to automatically update cell comments to provide a usefull description depending on which option code has been selected from the validation list. I have the following code which works for a single cell and updates the referenced comment for that cell. How can I change this so it will work for any cell within a specified range and update the comment for that cell ? Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("F2")) Is Nothing Then If Target.Value = "RM4" Then Comments(1).Text Target & " " & Sheet4.Range("C4") ElseIf Target.Value = "RM5" Then Comments(1).Text Target & " " & Sheet4.Range("C6") ElseIf Target.Value = "" Then Comments(1).Text "Selection Required" End If End If end sub thanks, Marcus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically change cell colors based on date in cell | Excel Discussion (Misc queries) | |||
How to change shapes for Cell Comments | Excel Discussion (Misc queries) | |||
Automatically create comments based upon cell value and related datain database | Excel Discussion (Misc queries) | |||
Can Comments be automatically converted to text cell values? | Excel Discussion (Misc queries) | |||
change automatically a value when another cell value changes | Excel Worksheet Functions |