drop down list for comment

[timlh42]

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

 

[DanteAmor]

Create a userform with the following:

Put the following code in the userform

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.

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.

 

[timlh42]

I'm not sure what went wrong but I get this message

Create a userform with the following:

Put the following code in the userform

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.

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.

 

[timlh42]

Sorry about that. I keep getting a compile error. Method or data member not found

 

[timlh42]

Okay. I did get that resolved. I had 2 userforms created.

But now, I keep getting an object required error code

Any ideas?

 

[timlh42]

I think it might be looking for the list but there isn't one in the code. How would I add that?

 

[DanteAmor]

You can put the complete error message and on which line of the macro it stops.

 

[timlh42]

You can put the complete error message and on which line of the macro it stops.

It has a run time error 424, Object Required

It stops at

If ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then

 

[DanteAmor]

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

 

[DanteAmor]

Try my test file

https://www.dropbox.com/s/72aa3woxplvwswb/userform comment.xlsm?dl=0