Excel VBA Userform Textbox - Highlight changes made in different font colour

Question

I have several texboxes on my userform which have there values auto-populated by a search macro. Once the textbox has been auto-populated, the user then has the option to edit the textbox value. I would like to highlight ANY changes made by the user in a different font colour to distinguish between the auto-populated macro value and the user's.

This will be a way to try and 'track changes' much like in Microsoft Word where all changes are flagged and recorded.

I have already contemplated the idea of having two separate textboxes, one containing the auto-populated value, the other allowing user input in a different colour and then performing a merge macro to join the two values at the end. But, this was not a viable user interface solution as the user needs to be able to track live changes on the fly.

I have designed the user interface to consist of a Search, Edit, Save and Bin method.

• The user first searches for a product
• The user then has the option to enable editing mode (unlock textbox)
• Save any changes they have made (lock text box)
• Bin unsaved changes (re-perform initial search to return default value)

Below are the three sub structures:

Private Sub CopyEditimg_Click()
If Menu.CopyValuetxt.Locked = True Then
Menu.CopyValuetxt.Locked = False
Menu.CopyValuetxt.SetFocus
With Menu.CopyValuetxt
    .CurLine = 0
End With
Menu.CopyEditimg.Visible = False
Menu.CopySaveimg.Visible = True
Menu.CopyBinimg.Visible = True

Menu.CopyValuetxt.BackStyle = fmBackStyleOpaque

Menu.InfoEditimg.Enabled = False
Menu.CopyEditimg.Enabled = False
Menu.Feature1Editimg.Enabled = False
Menu.Feature2Editimg.Enabled = False
Menu.Feature3Editimg.Enabled = False
Menu.Feature4Editimg.Enabled = False

End If
End Sub

-

Private Sub CopySaveimg_Click()
If Menu.CopyValuetxt.Locked = False Then
Menu.CopyValuetxt.Locked = True
Menu.CopyValuetxt.SetFocus
With Menu.CopyValuetxt
    .CurLine = 0
End With
Menu.SearchBox.SetFocus
Menu.CopyEditimg.Visible = True
Menu.CopySaveimg.Visible = False
Menu.CopyBinimg.Visible = False

Menu.CopyValuetxt.BackStyle = fmBackStyleTransparent

SaveChangesMacro

Menu.InfoEditimg.Enabled = True
Menu.CopyEditimg.Enabled = True
Menu.Feature1Editimg.Enabled = True
Menu.Feature2Editimg.Enabled = True
Menu.Feature3Editimg.Enabled = True
Menu.Feature4Editimg.Enabled = True

End If
End Sub

-

Private Sub CopyBinimg_Click()
SetCopy

Menu.CopyValuetxt.Locked = True
Menu.CopyValuetxt.SetFocus
With Menu.CopyValuetxt
    .CurLine = 0
End With
Menu.SearchBox.SetFocus
Menu.CopyEditimg.Visible = True
Menu.CopySaveimg.Visible = False
Menu.CopyBinimg.Visible = False

Menu.CopyValuetxt.BackStyle = fmBackStyleTransparent

SaveChangesMacro

Menu.InfoEditimg.Enabled = True
Menu.CopyEditimg.Enabled = True
Menu.Feature1Editimg.Enabled = True
Menu.Feature2Editimg.Enabled = True
Menu.Feature3Editimg.Enabled = True
Menu.Feature4Editimg.Enabled = True

End Sub

-

I think a possible solution will consist of using the .SelStart and .SelLength snippets.

However the user will not simply add text to the end of the auto-populated value. They may choose to make multiple changes to the existing value in multiple places, so I don't know how you could use .SelStart conditionally for each different location chosen to make changes.

I have highlighted the text that the user has entered. This would be considered a 'change' and should be highlighted in a different colour.

Is there track changes feature within VBA that I have overlooked? Or can this simply not be achieved?

Appreciate your time,

Jonathan.

---

Answer 1

The only way I can see would be to make the textbox transparent, then use the textbox_change event to detect changes to the string and dynamically generate labels of different colours behind the textbox to "highlight" text that has changed versus that which was originally present.

It seems feasible, but I've personally never tried something like this.

Answer 2

This is a great question Mr. J.

However, I don't think it is possible to alter partial contents of a TextBox in VBA. For this you would need a RichTextBox, but alas, VBA does not have these either. So it looks like you will need an alternative strategy.

It would be fairly straightforward to detect whether a text box had been edited (text added or removed). In this case you could have a little indicator to alert this (or change the whole text colour?) and then use a Comment box or similar to pop-out to show the original autopopulated text. This could be triggered perhaps with the MouseOver event?

Maybe not ideal but it would satisfy your criteria of alerting an edit, allowing the user to examine what had changed and allow a log of the changes to be compiled.