using cell comments or notes with userforms.

unluckyuser

New Member
Joined
Jan 12, 2025
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
Using 2019 Excel, doesn't seem to differentiate notes vs comments.

What I'm trying to do is use a row as a record for a patient. People need to write comments about patients, and I'd like to store those in cells as comments/notes. When a user views a note, I need to do it through a userform and place the contents of the note into a textbox, allow for editing, and store the updated note back into the worksheet cell it is attached to when a command button is clicked.

Thanks guys, this is the most amazing community out there :).
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Hello @unluckyuser.
Enter the following code into the worksheet module where you want to enter/edit comments in patient cells.
VBA Code:
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    frmComment.ShowComment Target
End Sub
Add a new UserForm (name it frmComment).
Add to the form:
TextBox (txtComment) - to enter a comment.
CommandButton (btnSave) - to save.
CommandButton (btnCancel) - to close without saving.
Label Comment is optional.
using cell comments or notes with userforms.png

Open frmComment and paste this code:
VBA Code:
Option Explicit
Dim targetCell      As Range

Public Sub ShowComment(cell As Range)
    Set targetCell = cell

    If Not targetCell.Comment Is Nothing Then
        txtComment.Text = targetCell.Comment.Text
    Else
        txtComment.Text = ""
    End If

    Me.Show
End Sub

Private Sub btnSave_Click()
  
    If Not targetCell Is Nothing Then
        If Not targetCell.Comment Is Nothing Then targetCell.Comment.Delete
        targetCell.AddComment txtComment.Text
    End If
  
    Unload Me
End Sub

Private Sub btnCancel_Click()
    Unload Me
End Sub
Now when the user (you) double clicks on a cell (any on the worksheet), the frmComment UserForm will open where he (you) can edit or add a comment.
using cell comments or notes with userforms_v1.png
using cell comments or notes with userforms_v2.png

I hope I understood you correctly and was able to help you with my solution. Good luck.
 
Upvote 0
You've certainly given me some ideas. The user never knows they are using Excel. They work entirely in userforms. They will select a patient for editing from a listbox. when they do, the line in the data worksheet containing the patient's record will be located, another userform will open containing the patient info, which vba reads from the worksheet line into form controls. It will read the existing note from the worksheet into a textbox, the user will edit and click save, where the record will be updated, including saving any changes to the comment as the worksheet is saved.
'This seems to be a task that Excel is not well suited for.
 
Upvote 0
If only we could see the code of that form of yours or an example file from you. It would be easier to start from something.
 
Upvote 0
I figured it out. Or rather I fumbled my way through it.

Private Sub CmdBttn_Edit_Comment_Click()
TextBox1.Value = Sheet1.Range("A1").Comment.Text
End Sub


Private Sub CmdBttn_Save_Comment_Click()
Range("A1").Comment.Text Text:=TextBox1.Value

End Sub

Trying to keep it as simple as possible and not using any addons like KuTools and a minimum of the default toolbox controls they give you in VBA.

I haven't done a thing with Excel/Vba in about 4years. I'm extremely rusty, not to mention I wasn't that good in the first place. So I wasted several hours on what is essentially two lines of code.

With this, a user can see prior notes and change them. I've taken the tab stops off all the command buttons. It's a bit confusing for people to hit enter and it doesn't insert a line return, but instead you have to hold the shift down and press enter. Otherwise, I'm happy with it and I can adapt this to do what's needed. I'm going to allow the user to iterate through all patients and their daily notes.

Again, thanks for your help. My end users will have no idea they are running Excel and the data file will be kept in a hidden directory. I should be using Access for the data file but this is the hand I'm dealt in the place I'm working. The front end excel files containing the vba code are read only.
 
Upvote 0

Forum statistics

Threads
1,226,264
Messages
6,189,928
Members
453,582
Latest member
Browny2821

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