I have a drop down list that has Late and Left early. I am trying to add
notes to these two drop down items. That is if a manager puts in left early
they get directed to a comments section so that they can add the reason, and
review it at a future date.

Hi Gary,

Paste this in your worksheet code module. Also I entered =TODAY() in cell
E1 and assumed F1 as having the drop down. Change to suit and adjust the
code to comply.

Gives you a list of "lates" or "earlys" in column A and a visible comment
with date and excuse.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Target <> Range("F1") Then Exit Sub
Range("A100").End(xlUp).Offset(1, 0).Select

With Selection
.AddComment
.Comment.Text Text:=Range("E1").Value & ":" _
& Chr(10) & Range("F1").Value
.Comment.Visible = True
.Value = Range("F1").Value
End With

Range("F1").ClearContents
Range("F1").Select
Application.EnableEvents = True
End Sub

HTH
Regards,
Howard

"Gary" <(E-Mail Removed)> wrote in message
news:634353FF-020C-4804-8C10-(E-Mail Removed)...
>I have a drop down list that has Late and Left early. I am trying to add
> notes to these two drop down items. That is if a manager puts in left
> early
> they get directed to a comments section so that they can add the reason,
> and
> review it at a future date.

Hi Gary,

The code I offered you seems to work fine until you clear the series of
comments compiled in cloumn A. Then it seems to "just not respond" to a
change in F1 as it did before. I save and close Excel and reopen and it
works fine.

I have post to the group looking for a solution.

Regards,
Howard

"Gary" <(E-Mail Removed)> wrote in message
news:634353FF-020C-4804-8C10-(E-Mail Removed)...
>I have a drop down list that has Late and Left early. I am trying to add
> notes to these two drop down items. That is if a manager puts in left
> early
> they get directed to a comments section so that they can add the reason,
> and
> review it at a future date.

"L. Howard Kittle" wrote:

> Hi Gary,
>
> The code I offered you seems to work fine until you clear the series of
> comments compiled in cloumn A. Then it seems to "just not respond" to a
> change in F1 as it did before. I save and close Excel and reopen and it
> works fine.
>
> I have post to the group looking for a solution.
>
> Regards,
> Howard
>
> "Gary" <(E-Mail Removed)> wrote in message
> news:634353FF-020C-4804-8C10-(E-Mail Removed)...
> >I have a drop down list that has Late and Left early. I am trying to add
> > notes to these two drop down items. That is if a manager puts in left
> > early
> > they get directed to a comments section so that they can add the reason,
> > and
> > review it at a future date.
>
>
> Thanks for the module. I tried putting it in but cant get it to work, I have never added a macro to excel before. so it is definately something i am doing wrong.
thanks for your help.

"L. Howard Kittle" wrote:

> Hi Gary,
>
> The code I offered you seems to work fine until you clear the series of
> comments compiled in cloumn A. Then it seems to "just not respond" to a
> change in F1 as it did before. I save and close Excel and reopen and it
> works fine.
>
> I have post to the group looking for a solution.
>
> Regards,
> Howard
>
> "Gary" <(E-Mail Removed)> wrote in message
> news:634353FF-020C-4804-8C10-(E-Mail Removed)...
> >I have a drop down list that has Late and Left early. I am trying to add
> > notes to these two drop down items. That is if a manager puts in left
> > early
> > they get directed to a comments section so that they can add the reason,
> > and
> > review it at a future date.
>
>
> Also the drop down box can be in almost any column, as i have the spreadsheet covering the whole year broken down by daysand at the top i do a formula that adds the number of occurrances for late left early sick, comp day vacation, etc. i tried doing a screen print and a paste but the editor screws up the paste.

Hi Gary,

I got some advice from Chip P, an MVP, on my code and it works for me on my
sheet. If the "Target "Range("F1")" moves about on your sheet, we have a
problem.

If you would like, send me a sample worksheet and advice on what you want to
happen an I will be glad to at least take a look at it.

Do the obvious here... lhkittle at dot comcast dot net

Regards,
Howard
lhkittle at dot comcast dot net


Thanks Chip, I changed...

If Target <> Range("F1") Then Exit sub

To:

If Target <> Range("F1") Then
Application.EnableEvents = True
Exit Sub
End If

WOW, seems to have solved the problem.
Have to admit, I do not understand why the "select rows > delete comments"
event would bust the code. Also I delete cell contents... but whatever???

Thanks a ton Chip, always a pleasure!
Regards,
Howard

New code...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False

If Target <> Range("F1") Then
Application.EnableEvents = True
Exit Sub
End If

Range("A100").End(xlUp).Offset(1, 0).Select

With Selection
.AddComment
.Comment.Text Text:=Range("E1").Value & ":" _
& Chr(10) & Range("F1").Value
.Comment.Visible = True
.Value = Range("F1").Value
End With

Range("F1").ClearContents
Range("F1").Select
Application.EnableEvents = True
End Sub
"Gary" <(E-Mail Removed)> wrote in message
news:634353FF-020C-4804-8C10-(E-Mail Removed)...
>I have a drop down list that has Late and Left early. I am trying to add
> notes to these two drop down items. That is if a manager puts in left
> early
> they get directed to a comments section so that they can add the reason,
> and
> review it at a future date.

"L. Howard Kittle" wrote:

> Hi Gary,
>
> I got some advice from Chip P, an MVP, on my code and it works for me on my
> sheet. If the "Target "Range("F1")" moves about on your sheet, we have a
> problem.
>
> If you would like, send me a sample worksheet and advice on what you want to
> happen an I will be glad to at least take a look at it.
>
> Do the obvious here... lhkittle at dot comcast dot net
>
> Regards,
> Howard
> lhkittle at dot comcast dot net
>
>
> Thanks Chip, I changed...
>
> If Target <> Range("F1") Then Exit sub
>
> To:
>
> If Target <> Range("F1") Then
> Application.EnableEvents = True
> Exit Sub
> End If
>
> WOW, seems to have solved the problem.
> Have to admit, I do not understand why the "select rows > delete comments"
> event would bust the code. Also I delete cell contents... but whatever???
>
> Thanks a ton Chip, always a pleasure!
> Regards,
> Howard
>
> New code...
>
> Option Explicit
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> On Error Resume Next
> Application.EnableEvents = False
>
> If Target <> Range("F1") Then
> Application.EnableEvents = True
> Exit Sub
> End If
>
> Range("A100").End(xlUp).Offset(1, 0).Select
>
> With Selection
> .AddComment
> .Comment.Text Text:=Range("E1").Value & ":" _
> & Chr(10) & Range("F1").Value
> .Comment.Visible = True
> .Value = Range("F1").Value
> End With
>
> Range("F1").ClearContents
> Range("F1").Select
> Application.EnableEvents = True
> End Sub
> "Gary" <(E-Mail Removed)> wrote in message
> news:634353FF-020C-4804-8C10-(E-Mail Removed)...
> >I have a drop down list that has Late and Left early. I am trying to add
> > notes to these two drop down items. That is if a manager puts in left
> > early
> > they get directed to a comments section so that they can add the reason,
> > and
> > review it at a future date.
>
>
> Hi Howard,

i sent the spreadsheet for your review.

thanks,

gary

Hi Gary,

Have not received it yet.

Maybe you got my e-mail wrong.

(E-Mail Removed)

Howard

"Gary" <(E-Mail Removed)> wrote in message
news:634353FF-020C-4804-8C10-(E-Mail Removed)...
>I have a drop down list that has Late and Left early. I am trying to add
> notes to these two drop down items. That is if a manager puts in left
> early
> they get directed to a comments section so that they can add the reason,
> and
> review it at a future date.

"L. Howard Kittle" wrote:

> Hi Gary,
>
> Have not received it yet.
>
> Maybe you got my e-mail wrong.
>
> (E-Mail Removed)
>
> Howard
>
> "Gary" <(E-Mail Removed)> wrote in message
> news:634353FF-020C-4804-8C10-(E-Mail Removed)...
> >I have a drop down list that has Late and Left early. I am trying to add
> > notes to these two drop down items. That is if a manager puts in left
> > early
> > they get directed to a comments section so that they can add the reason,
> > and
> > review it at a future date.
>
>
> Hi Howard,

i resent the spreadsheet. It keeps getting returned.

thanks,

gary