I am trying to add notes to a drop down list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
L. Howard Kittle said:
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




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.
Click to expand...
thanks for your help.
 
L. Howard Kittle said:
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




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.
Click to expand...
 
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
 
L. Howard Kittle said:
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



Hi Howard,
Click to expand...

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 address removed)

Howard
 
L. Howard Kittle said:
Hi Gary,

Have not received it yet.

Maybe you got my e-mail wrong.

(e-mail address removed)

Howard




Hi Howard,
Click to expand...

i resent the spreadsheet. It keeps getting returned.

thanks,

gary
 
Back
Top