Excel VBA Userform Textbox - highlighting changes made in different font colors
I have several texboxes in my custom form that have values auto-populated with a search macro. After the text box has been auto-filled, the user has the option to edit the value of the text box. I would like to highlight ANY changes made by the user in a different font color to differentiate between macro fields and user macros.
It will be a way to try and "track changes" like in Microsoft Word, where all changes are noted and recorded.
I've already considered the idea of having two separate text boxes, one containing an auto-filled value, and the other allowing the user to enter a different color, and then doing a macro merge to combine the two values at the end. But this was not a viable solution for the UI as the user needs to track changes in real time on the fly.
I have developed a user interface that consists of a search, edit, save and bin method.
- First, the user searches for a product
- The user then has the option to enable edit mode (unlock the text box)
- Save the changes they made (lock the text box)
- Save unsaved changes (rerun initial search to revert to default)
Three substructures are presented below:
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 would be to use the .SelStart and .SelLength snippets.
However, the user won't just add text to the end of the auto-filled value. They can select multiple changes to an existing value in multiple locations, so I don't know how you could use .SelStart conditionally for every other location selected to make changes.
I have highlighted the text entered by the user. This will be considered a "change" and should be highlighted in a different color.
Is there a change tracking feature in VBA that I forgot? Or it just cannot be achieved?
Rate your time
Jonathan.
source to share
That's a big question, Mr. J.
However, I don't think it is possible to change the partial content of the TextBox in VBA. You need a RichTextBox to do this, but alas, VBA doesn't have one either. It looks like you need an alternative strategy.
It would be quite easy to determine if the textbox has been edited (text added or removed). In this case, you can have a small indicator to warn about it (or change the entire text color?) And then use a comment box or similar popup to show the autopopulated source text. This could be triggered perhaps by the MouseOver event?
Maybe not perfect, but it will meet your edit warning criteria, allowing the user to examine what has changed and allow the changelog to be compiled.
source to share
The only way I can see is to make the textbox transparent and then use the textbox_change event to detect changes in the line and dynamically generate different color labels behind the textbox to "highlight" text that has changed from what it was originally is present.
It seems doable, but I personally have never tried something like this.
source to share