Add comment from User Form.

[julhs]

Is it possible to add a command button on my data input User Form that will enable me to add a comment in the cell that is going to be populated by the User Form?

 

[Logit]

Option Explicit

Private Sub CommandButton1_Click()
 On Error Resume Next
 Dim com As String
 com = TextBox1.Text

  With ActiveCell
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:=com
  End With
On Error GoTo 0
End Sub

 

[julhs]

That works in itself as a standalone procedure.
But now realise that’s not going to work appropriately for me, so need a slightly different approach.

2 things;
As it stands it means I have to have a TextBox and CommandButton on the UserForm.
UserForm is pretty congested already, so lam wondering if it’s possible to call a new textbox or show a hidden one by use of the CommandButton
2nd that using ActiveCell means comment is being added to an inappropriate cell and not one that the UserForm is actualy going to populate.
Also relevant is that the UserForm is using “LastRow” method to place the entry

So procedure would be;
CommandButton to first call (new or hidden) text box
Enter text into “Comments” text box
Don’t add “Comments” text to the sheet at this point
Set focus back to the UserForm and complete data input on it.
Then when I add the data entry the text in “Comments” text box is added as a “Comment” to the appropriate cell.

 

[Logit]

You mentioned your UserForm is already quite congested, yet you are seeking another CommandButton to initiate the process.
If there were another CommandButton, where would you place it ?

Also, if not the "Active Cell", what cell would you place the 'Comment' ?

 

[julhs]

Hi Logit
Yes there is room for one.
See below is where I have managed to get to so far.
Progress to date!
Have dealt with “Commets TextBox” remaining hidden until cmdAddCommentsTextBox is clicked, by setting “Comments TextBox” visible properties to “False”
And by assigning this to the call “Add Comments command button”

Private Sub cmAddCommentsTextBox_Click()
txtAddCommentsTextBox.Visible = True
txtAddCommentsTextBox.SetFocus
End Sub

In a nut shell, “Comments TextBox” remains hidden until cmdAddCommentsTextBox button is clicked.

So far so good, next step
Now need to take the text value of the “Comments textbox” and add it as a comment into appropriate cell decided by the “UserForm” entry criteria.
This is going to involve a little tweaking of the main “Add Entry” sub.
Key part of which is (bear in mind this IS NOT the whole Sub);

If LR1 = LastRow = 0 Then '(this needs to be 0 otherwise this doesn't work)
.Range("N" & LR1 + 1 & ":AJ" & LR1 + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("N" & LR1 + 1 & ":AJ" & LR1 + 1).FillUp
LastRow = LR1 + 1
End If
.Cells(LastRow, "O") = Format(Me.Calendar1.Value, "mmm/dd")
.Cells(LastRow, "P") = Me.txtInvNo
.Cells(LastRow, "Q") = Me.cmbPaymentMethod
.Cells(LastRow, "S") = Me.txtItemValue.Value

Then after
.Cells(LastRow, "S") = Me.txtItemValue.Value

Then this is when/where need to add the "Comments TextBox” value as a “Comment”

In some ways this is a case of reverse engineering, as the cell to place/add the comment is as yet undefined and won’t be until the full data entry is made.
Hope that makes sense?

PS. Sorry forgot to mention "Comment" will be going into .Cells(LastRow, "S")

 

[Logit]

Admittedly, this is simply a 'stab in the dark' ...

If LR1 = LastRow = 0 Then '(this needs to be 0 otherwise this doesn't work)
.Range("N" & LR1 + 1 & ":AJ" & LR1 + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("N" & LR1 + 1 & ":AJ" & LR1 + 1).FillUp
LastRow = LR1 + 1
End If
.Cells(LastRow, "O") = Format(Me.Calendar1.Value, "mmm/dd")
.Cells(LastRow, "P") = Me.txtInvNo
.Cells(LastRow, "Q") = Me.cmbPaymentMethod
.Cells(LastRow, "S") = Me.txtItemValue.Value

On Error Resume Next
 Dim com As String
 com = TextBox1.Text

  With .Cells(LastRow, "S")
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:=com
  End With
On Error GoTo 0

Having a copy of your workbook here, to experiment with would be a great help.

 

[julhs]

Well Logit this is what I came up with, similar/same as you suggest.
Believe it’s now sorted!

As this has taken me all day to do, I thought I’d post my solution.
Bear in mind the below is applicable to when using “LastRow” function to add data from the User Form, because the cell populated by the “UserForm” will NOT
necessarily be the “Active cell”

So, showing the hidden “AddComments textbox” from a Command Button is covered above.

Now with the “AddComments TextBox” on the UserForm = “Bla Bla Bla”.
Adding the below code to the end of the main “Add Entry” sub routine

.Cells(LastRow, "s") .NoteText Text:=UserForm2.txtComment.Value

the contents of “AddCommets TextBox” “Bla Bla Bla” is added to the appropriate cell that the UserForm is adding data to (in this case its LastRow ,column “S”)

Summarised
With the below clicking the CommandButton on the UserForm the hidden “Comments TextBox” is shown.

Private Sub cmAddCommentsTextBox_Click()
txtAddCommentsTextBox.Visible = True
txtAddCommentsTextBox.SetFocus

Then, by adding the below code to the end of my main “Add entry” sub routine

.Cells(LastRow, "s") .NoteText Text:=UserForm2.txtComment.Value

the contents of “AddCommets TextBox” is added as a comment into the appropriate cell, as determined by the UserForm data entry criteria.

If this is only help to one person in future I will be chuffed!!!!

 

[Logit]

Good for you ! Glad you have an answer and provided same for others.

Cheers.

 

[julhs]

Thank you Logit, your help it's much appreciated.

I never thought of trying to incorporate what you gave me originally into the main "Add Entry" procedure.
But that's why the likes of me are asking the questions and you are giving solutions!!
As a fuller picture this is what I came up with for the final edit of the end of "Main UserForm" data entry sub code

If LR1 = LastRow = 0 Then      '(this needs to be 0 otherwise this doesn't work)
            .Range("N" & LR1 + 1 & ":AJ" & LR1 + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            .Range("N" & LR1 + 1 & ":AJ" & LR1 + 1).FillUp
            LastRow = LR1 + 1
    End If  
      .Cells(LastRow, "O") = Format(Me.Calendar1.Value, "mmm/dd")
      .Cells(LastRow, "P") = Me.txtInvNo
      .Cells(LastRow, "Q") = Me.cmbPaymentMethod
      .Cells(LastRow, "S") = Me.txtItemValue.Value
      .Cells(LastRow, "s").NoteText Text:=UserForm2.txtComment.Value