How to highlight the text of a Userform Textbox at mouse click in EXCEL VBA

First create a userform with a text box (Pic 1, pic 2, pic 3)

In the code of the userform, write following code:

Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    With UserForm1.TextBox1

        .SelStart = 0

        .SelLength = Len(.Text)

        .ForeColor = &H80000001

    End With

End Sub

Now, whenever the user click on the textbox, it will highlight the text inside automatically

DoMyExcel.com | Excel & VBA Consulting | Customized Excel Solutions starting at 89 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

Excel Tip: SUM formula can be used to add Numbers in a Range containing both Numbers and Text.

If you want to add only the numbers in a range (e.g a column) you can simply use the SUM() formula for this range. The text will be ignored and the numbers will be added. 

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

Good practice when using EXCEL comparison function (VLOOKUP(), MATCH(), INDEX()) of values in text and number format

The "TRIM()" function in EXCEL helps to prevent error when comparing values in number format with value in text format. This "TRIM()" function will remove all the "space characters: in front of the value and will also convert any number format into a text format.
 



 

This function is particularly interesting when a large number of data need to be compared. It will ensure that you are comparing correctly text to text.

 

One of the good practice is therefore to add one more column to trim the keys of your database, and then to search the trimmed value into this column (works also with MATCH() function, INDEX() function, etc.)

 

 

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com

How to find a value in an Excel range and return its address in VBA

If you want to find the word "DoMyExcel" in a range of your spreadsheet and return its location / address, create a module with this code:

Sub Return_Search_Result_Adress()

    Dim My_Range As Range
    Set My_Range = Sheets("Sheet1").Range("A1:A10000").Find("DoMyExcel", lookat:=xlPart)
    If Not My_Range Is Nothing Then
           MsgBox "Found at " & My_Range.Address
    End If
End Sub

• xlPart = looks at the text in the cell for any match. If you search for any part of the text (such as "Excel"), it will return "Found at $A$6".
• xlWhole = looks at the entire/exact entry in the cell to see if it matches.

Created by DoMyExcel.com © | Excel & VBA Consulting | Customized Excel Solutions starting at 29 USD

Services provided to Individuals and Businesses | Free Quote: DoMyExcel@gmail.com