drop down list for comment

timlh42

Board Regular
Joined
Sep 27, 2017
Messages
76
is there anyway to have a selection list to choose from instead of writing a comment in the comment box?

Here is what I would like to do:

If any cell in range H7:H37 , J7:J37, L7:L37 (which are populated by a validation list) is less than 5, then a comment box would pop up offering the following choices:

Too much time
Too late
Not enough teamwork
etc
etc
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Create a userform with the following:

f4b8c226e17b63569ed0b2cdfe82a00b.jpg



Put the following code in the userform

Code:
Public cel As String    'At the beginning of all the code


Private Sub CommandButton1_Click()
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox "Select a choice"
    Exit Sub
  End If
  With Range(cel)
    If .Comment Is Nothing Then .AddComment
    .Comment.Visible = True
    .Comment.Text Text:=ComboBox1.Value
    .Comment.Shape.TextFrame.AutoSize = True
  End With
  Unload Me
End Sub


Private Sub CommandButton2_Click()
  Unload Me
End Sub

Put the following code in the events on the sheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("H7:H37, J7:J37, L7:L37")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.Value < 5 Then
      With UserForm1
        .cel = Target.Address
        .Show
      End With
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
 
Upvote 0
I'm not sure what went wrong but I get this message

5q0wcAAAAAwMJZFEk0AAAAAAD1bK6TaJ7ODQAAAACARyTRAAAAAAB4RBINAAAAAIBHJNEAAAAAAHgi8v8HQPgi9QW1Vc0AAAAASUVORK5CYII=





Create a userform with the following:

f4b8c226e17b63569ed0b2cdfe82a00b.jpg



Put the following code in the userform

Code:
Public cel As String    'At the beginning of all the code


Private Sub CommandButton1_Click()
  If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox "Select a choice"
    Exit Sub
  End If
  With Range(cel)
    If .Comment Is Nothing Then .AddComment
    .Comment.Visible = True
    .Comment.Text Text:=ComboBox1.Value
    .Comment.Shape.TextFrame.AutoSize = True
  End With
  Unload Me
End Sub


Private Sub CommandButton2_Click()
  Unload Me
End Sub

Put the following code in the events on the sheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("H7:H37, J7:J37, L7:L37")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    If Target.Value < 5 Then
      With UserForm1
        .cel = Target.Address
        .Show
      End With
    End If
  End If
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
 
Upvote 0
Okay. I did get that resolved. I had 2 userforms created.

But now, I keep getting an object required error code

Any ideas?
 
Upvote 0
I think it might be looking for the list but there isn't one in the code. How would I add that?
 
Upvote 0
You can put the complete error message and on which line of the macro it stops.
 
Upvote 0
Within the userform, you must create a ComboBox with the name of ComboBox1.
You must also create in the userform 2 buttons, commandbutton1 and commandbutton2
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top