Linking a cell's comment to a vba form's textbox

pauly21 · Feb 6th 2020

Hi,

I've created a form to display multiple cells when selecting a date and am having difficulty in trying to figure out how (or if it's possible) to link a cell's comment to a textbox on the form.

Is it possible to modify this line of code to display the cell's comment.

('extrainfo' is the textbox. SLNo is an index number in column A)

Code

Me.extrainfo.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("data").Range("A2:AR1087"), 44, 0)

**Carim** · Feb 7th 2020

Hi and Welcome to the Forum

Quite a precise question ...

But rather risky to ask such a question ... without attaching your file ...

pauly21 · Feb 7th 2020

Thanks.

I didn't attach the file as it has a lot of other peoples private data in it.

Here's the code if that helps

Code

Private Sub ComboBox1_Change()
Dim SLNo As Integer

If Me.ComboBox1.Value = "" Then
MsgBox "SL No Can Not be Blank!!!", vbExclamation, "SL No"
Exit Sub
End If

SLNo = ComboBox1.Value
On Error Resume Next
Me.txtname.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("data").Range("A2:AR1087"), 40, 0)
Me.showaddress.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("data").Range("A2:AR1087"), 41, 0)
Me.txttelno.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("data").Range("A2:AR1087"), 42, 0)
Me.txtIbirthdate.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("data").Range("A2:AR1087"), 43, 0)

'This is the line i need to show a comment from which is in column AQ
Me.extrainfo.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("data").Range("A2:AR1087"), 44, 0)

End Sub

Display More

**royUK** · Feb 7th 2020

I'm not sure what you are doing, but I woulddn't use VLookup. I would use the VBA Find method.

The example file has a Function to test if a cell has a comment. The Button on the form tests for a comment using the Function, if the cell contains a comment it will enter it to the TextBox.

Attach a small dummy workbook and I will make a better example.

Note: I have added Code Tags to your post, please use them in future when posting code to the Forum. Watch this to see how

External Content youtu.be

Content embedded from external sources will not be displayed without your consent.

Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

pauly21 · Feb 7th 2020

I've stripped back the file and attached it.

The comment i need to display on the dynamic form is in column B

test2.xlsm

**royUK** · Feb 7th 2020

I can speed up and simplify the code for you. If I don't get back to you soon, I will tomorrow.

**royUK** · Feb 7th 2020

I think that I have the columns going to the correct textboxes. Check this example.

If it needs amending I'll check back tomorrow and add explanation.

pauly21 · Feb 7th 2020

Thanks, that works a lot better for me.

There is some code i stripped out of the file that would need updating now though which is code to update the various cells from the form.

Can you take a look at how that could be done please?

Here's the code:

Code

Private Sub cmdupdate_Click()

If Me.ComboBox1.Value = "" Then
MsgBox "SL No Can Not be Blank!!!", vbExclamation, "SL No"
Exit Sub
End If

SLNo = Me.ComboBox1.Value
Sheets("data").Select
Dim rowselect As Double
Dim msg As String
Dim ans As String

rowselect = Me.ComboBox1.Value
rowselect = rowselect + 1
Rows(rowselect).Select

Cells(rowselect, 2) = Me.txtmarket.Value
Cells(rowselect, 4) = Me.txtbNum.Value
Cells(rowselect, 5) = Me.txtcnum.Value
Cells(rowselect, 6) = Me.txtcity.Value
Cells(rowselect, 7) = Me.txtState.Value
Cells(rowselect, 8) = Me.txtpnum.Value
Cells(rowselect, 9) = Me.txtSdate.Value
Cells(rowselect, 35) = Me.sourcedby.Value
Cells(rowselect, 36) = Me.format.Value
Cells(rowselect, 38) = Me.filesize.Value
Cells(rowselect, 39) = Me.originalformat.Value
Cells(rowselect, 40) = Me.numbercopies.Value
Cells(rowselect, 41) = Me.lineup.Value


' how would this code be modified to update the comment?
'Cells(rowselect, ??) = Me.extrainfo.Value


rowselect = rowselect - 1
msg = "Sl No " & rowselect & "  Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
UserForm1.Show
Else
Sheets("Data").Select
End If
End Sub

Display More

**royUK** · Feb 8th 2020

I'm busy this morning but will take a look this afternoon

**royUK** · Feb 8th 2020

This should do what you want

Code

Private Sub cmdupdate_Click()
Dim lRw As Long

If Me.ComboBox1.Value = "" Then
MsgBox "SL No Can Not be Blank!!!", vbExclamation, "SL No"
Exit Sub
End If

lRw = Me.ComboBox1.ListIndex + 2

With Sheet1
.Cells(lRw, 2) = Me.txtmarket.Value
.Cells(lRw, 4) = Me.txtbNum.Value
.Cells(lRw, 5) = Me.txtcnum.Value
.Cells(lRw, 6) = Me.txtcity.Value
.Cells(lRw, 7) = Me.txtState.Value
.Cells(lRw, 8) = Me.txtpnum.Value
.Cells(lRw, 9) = Me.txtSdate.Value
.Cells(lRw, 35) = Me.sourcedby.Value
.Cells(lRw, 36) = Me.format.Value
.Cells(lRw, 38) = Me.filesize.Value
.Cells(lRw, 39) = Me.originalformat.Value
.Cells(lRw, 40) = Me.numbercopies.Value
.Cells(lRw, 41) = Me.lineup.Value
''/// add the text from TextBox to the Comment
On Error Resume Next
.Cells(lRw, 2).Comment.Text Text:=Me.extrainfo.Value
End With
On Error GoTo 0
lRw = lRw - 1
msg = "Sl No " & lRw & " Successfully Updated...Continue?"
Unload Me
ans = MsgBox(msg, vbYesNo, "Update")
If ans = vbYes Then
UserForm1.Show
End If
End Sub

Display More

**royUK** · Feb 8th 2020

I have added a Procedure to load the ComboBox called RefreshCombo so that it can refreshed when needed. This procedure is called from the Initialize event of the UserForm to load the data. It is also used in the update button code to upload the changed data

pauly21 · Feb 8th 2020

That's exactly what i was trying to do. Thanks very much Roy.

**royUK** · Feb 8th 2020

Pleased to help.

pauly21 · Feb 8th 2020

There is a problem with the updating code.

example:

If you edit the comment linked (from cell B3) textbox on the form, it overwrites the text in cell B3 with the value from cell C3

can you take a look please?

pauly21 · Feb 8th 2020

It's ok, i've sorted it now. It was targeting the wrong cell references when updating.

Thanks

pauly21 · Feb 12th 2020

Is it possible to change the linked comment boxes to maintain the formatted text in them when on the form and back again when updating them.

Such as:

THIS IS THE FIRST LINE OF TEXT IN THE COMMENT

This is the second line

This is the third line

**royUK** · Feb 12th 2020

The problem is the line breaks, etc don't display properly when imported to the UserForm, which is why I used Clean & Trim. You can try with those Functions.

pauly21 · Feb 12th 2020

Thanks Roy, it's not what i meant though.

Here's a better example

Here is an example comment on the sheet:

"THIS IS THE FIRST LINE OF TEXT IN THE COMMENT

This is the second line"

This is what it turns into on the form:

"THIS IS THE FIRST LINE OF TEXT IN THE COMMENT

This is the second line"

I would like to retain the text in italics and the difference in bold & normal text when on the form's textbox if possible?

**royUK** · Feb 13th 2020

I'm not sure if you can. I'll look into this.

pauly21 · Apr 14th 2020

I've exhausted all options on rich text on the form, it's not possible at present.

Another tweak i'm trying to achieve is selecting a cell (A450 for example) and when opening the form have that record show first instead of having to scroll through all the records to get to it on the 'index number' drop menu.

Is that possible with the example i posted above?

thanks

Paul

Linking a cell's comment to a vba form's textbox

Participate now!

Share