VBA Userform to "Update" Existing Cell & automatically reference

[CalumJames]

Ok, firstly Hi to all... I'm Calum & I'm working on a project to "refresh" a project spreadsheet which is where we keep a high level overview of how things are moving etc.

Unfortunately I'm not that knowledgeable in VBA and have two questions I hope someone maybe able to help with:

1. At this moment in time I'm only interested in cells within the one column being updated separately via a user form. Basically a new project is listed on each row and the column.."J" for example is headed "Update". For each Row, on column J I need a code where the user can select the hyperlink in that box to call the user form. The user form will allow the user to input an update (i.e. project on hold because of cost issues) & select submit. I need this submission to only UPDATE the existing cell at the top and not overwrite the comments already there and also prefix with a date the addition was made.

The userform will need to automatically know which cell it is being called from and ONLY update that one (as each project is different).

I've searched around but all I can find is either overwriting the current cell or adding a new cell... neither are what I need.

Thank you.

 

[AlphaFrog]

• Right-click on the sheet tab that has your data you want to update
• Select View Code from the pop-up menu
• Paste the code below in the VBA edit window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' If any cell in column J is selected then show UserForm1
    If Target.Column = 10 Then [I]UserForm1[/I].Show
End Sub

The above code will show Userform1 any time the user selects a cell in column J

Put the code below in the UserForm module. It will add the current date and text from TextBox1 to the beginning of the selected J cell. Any previous text will be included as well.

Private Sub CommandButton1_Click()
    ' "Submit" command button
    ActiveCell.Value = Format(Date, "m/d/yy: ") & TextBox1.Text & vbLf & ActiveCell.Text
    UserForm1.Hide
End Sub

 

[CalumJames]

Many thanks Alpha... The updates work in the correct order & the existing text stays... so many thanks

Once thing though (not that I have got this far yet) but I'm going to be looking at somehow linking the box via url within the cell top (possibly) or a float text show box rather than a click - in your opinion would this cause major problems with the way in this currently works?

Cheers

 

[AlphaFrog]

You're welcome. I'm glad it worked.

Once thing though (not that I have got this far yet) but I'm going to be looking at somehow linking the box via url within the cell top (possibly) or a float text show box rather than a click - in your opinion would this cause major problems with the way in this currently works?

Sorry, I don't understand what this means. Linking what box? What's a "float text show box"? Is that suppose to be one of the yellow pop-up Comment boxes.

 

[CalumJames]

Yeah so far so good - thank you!!

Oh no sorry, I don't think was very clear. From a project perspective the Column J will need to be updated constantly, but there will also be times where we will need to review all the updates within the cell - however clicking the cell would prompt the form to display (which wouldn't be needed) plus we'd have to expand the cell anyhow as I'm not keen on autoheight. So, to work around this I was thinking of implementing VBA to show the entire content for any cell within column J when hovered upon. And instead my plan is to have a manual link here that the person could select to call the userform when they did want to update.

Hope I explained myself better this time

Ps. I assume I'd have to make a new module for the date prefix if I wished to have only this bold? I've had a butchers and it seems this is the only way within within a textbox....

Thanks again

 

[CalumJames]

Ok - an update... the float box can't be done because there isnt an event handler for this from what I know.. so ignore me. I'm going to amend your active cell click to point to a pop up box instead and then from here have the button to update the cell using the user form.

Still not sure on how to get the blue date font though.. so would appreciate anyones advise on this.

 

[AlphaFrog]

This puts the date and the most recent text in the selected J cell. It keeps all the past dates and text in the cell's comment box which pops-up when you hover over the cell.

Private Sub CommandButton1_Click()
' "Submit" command button

    Dim ComTxt As String, LinesCount As Integer, pos As Long

    Application.DisplayCommentIndicator = xlCommentIndicatorOnly
    
    With ActiveCell
        .Value = Format(Date, "mm/dd/yy: ") & TextBox1.Text
        ComTxt = .Value
        If Not .Comment Is Nothing Then
            ComTxt = ComTxt & Chr(10) & .Comment.Text
            .Comment.Delete
        End If
        .AddComment ComTxt
        .Comment.Shape.Width = 200
        LinesCount = Len(ComTxt) - Len(Replace(ComTxt, Chr(10), "")) + 1
        .Comment.Shape.Height = LinesCount * 12
        With .Comment.Shape.TextFrame ' Blue comment dates
            Do
                .Characters(pos + 1, 10).Font.ColorIndex = 5
                pos = InStr(pos + 1, ComTxt, Chr(10), 1)
            Loop Until pos = 0
        End With
    End With
    
    UserForm1.Hide
    
End Sub

You may have to adjust the .Comment.Shape.Width to greater than 200 depending on the length of text you are typing in the userform. The comment box height is automatically adjusted to one line for each date-comment.

Example code: Excel Comments VBA