Autocomplete when typing in Excel drop down list

How to autocomplete when typing in Excel drop down list?

ActiveX Controls Combo Box VBA

Click here to read the article that I found online

Macro to add hyperlink for a list (Excel VBA)

We can use the below Excel Macro to add hyperlinks for a list. For example, we have a list of text to display and in next column a list of websites. This Macro will add the respective website as hyperlink.

So in our active sheet, in column 'A', we have some text which we want to be hyperlinked to website available in next column. Here in this Macro the list length is 6, which we can change as per our requirement.

Column A                            Column B

Tutorial on Excel	www.excel.com
Tutorial on SQL	www.sql.com
Tutorial on VBA	www.vba.com
Tutorial on Java	www.java.com
Tutorial on HTML	www.html.com
Tutorial on REGEX	www.regex.com

The data will look like this after running the Macro; the Column A is hyperlinked now.

Column A                            Column B

Tutorial on Excel	www.excel.com
Tutorial on SQL	www.sql.com
Tutorial on VBA	www.vba.com
Tutorial on Java	www.java.com
Tutorial on HTML	www.html.com
Tutorial on REGEX	www.regex.com

And below is the VBA Code/Macro

Sub Amiq()

With ActiveSheet

Dim counter As Integer

counter = 1

Do While counter <= 6

.Hyperlinks.Add Anchor:=.Cells(counter, "A"), _

 Address:=Cells(counter, "B").Value, _

 ScreenTip:="Web Site", _

 TextToDisplay:=Cells(counter, "A").Value

counter = counter + 1

 Loop

 End With

End Sub

Excel MROUND Function

The Excel MROUND function is to round values to the nearest multiple. This function is available in Excel 2007 & above.

The syntax of this function is MROUND(number, multiple). Here the number is the cell or value and multiple is what you want your value to round to.

We can round the time as well. For example we have start times and want to round it to nearest 15 minutes.

When we want to round time, the multiple is written is double quotes same as time format e.g. =MROUND(A2,”0:15”).

And for normal numbers the multiple is entered without double quotes.

And when we round negative numbers, the multiple is entered in same sign, otherwise we will get error.

And another point to note that this function will round up or down to the nearest multiple and not in a fixed direction.

For fixed direction, we can use ROUNDUP or ROUNDDOWN functions.

Examples

Round some prices to nearest 5 cents
Price	Formula	Rounded Value
$7.99	MROUND(A3,0.05)	8
$7.45	MROUND(A4,0.05)	7.45
$7.49	MROUND(A5,0.05)	7.5
$7.44	MROUND(A6,0.05)	7.45
Round some start times to nearest 15 minutes
Start Time	Formula	Rounded Value
6:53 AM	MROUND(A10,"0:15")	7:00:00 AM
5:08 AM	MROUND(A11,"0:15")	5:15:00 AM
9:23 AM	MROUND(A12,"0:15")	9:30:00 AM
2:38 AM	MROUND(A13,"0:15")	2:45:00 AM
Round a negative value
Price	Formula	Rounded Value
($7.92)	MROUND(A18,-0.05)	-7.9

 

Check if list of Emails Valid or not (VBA Regex)

If we have a list of email addresses, like about 20,000 and we want to check if they are valid i.e. (valid format with an email id followed by ‘@’ and then domain name) then this macro can help us in doing this with a click.

Example sheet also available at end for Download

We will use VBA and the regular expression function (Regex).

To use Regex we need to do a few things beforehand.

Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

Ø For this select the “Developer tab”. If you don’t have it then (for Office 2010) go to File -> Options ->Customize Ribbons ->Popular Commands in Choose commands from -> Click on Developer tab box in customize the ribbon box.

Ø Now you can see the Developer tab on top, click on it and then on Visual Basic.

Ø Go to Tools -> References and select Microsoft VBScript Regular Expressions 5.5.




Now you are set to run the Regex Macros.

Below is the VB Code of Macro.

Sub amiq()

Dim strPattern As String: strPattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
Dim regEx As New RegExp
Dim strInput As String
Dim r As Integer

For r = 2 To Selection.Rows.Count
If strPattern <> "" Then
strInput = Cells(r, 1).Value
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With

If regEx.Test(strInput) Then

Cells(r, 2).Value = "Valid Email Address"
Else
Cells(r, 2).Value = "Not Valid"
End If
End If
Next r
End Sub


I have found this Regex pattern to check mail address validity from totorialspoint.

Please download the example Macro enabled workbook to see how it works. You just need to paste the list of mails in the first column then select and press the button. The second column will show their validity status.


Click Here to Download the Example File

Similarly if we want to identify the cases where text field have consecutive duplicate words and store the duplicate word in results, then we can use the below regex pattern.

Sub amiq()

Dim strPattern As String: strPattern = "\b(\w+)\b\s+\1\b"

Dim regEx As New RegExp

Dim strInput As String

Dim r As Integer

For r = 2 To Selection.Rows.Count

If strPattern <> "" Then

strInput = Cells(r, 6).Value

With regEx

.Global = True

.MultiLine = True

.IgnoreCase = False

.Pattern = strPattern

Set allMatches = regEx.Execute(strInput)

End With

If allMatches.Count <> 0 Then

Cells(r, 7).Value = allMatches.Item(0)

Else

Cells(r, 7).Value = "Valid"

End If

End If

Next r

End Sub

One Drop down List Dependent on Other (Excel 2010)

One Drop down List Dependent on Other

For example in first drop down, we select the type of food, i.e. American or Pakistani. And the second drop down shows us a list of respective food items.

To do this, we need to do the following easy steps.

On the second sheet of our workbook, create named ranges of following.

American	Pakistani	Types
Pizza	Chargha	American
Pasta	Chicken Karahi	Pakistani
Sausage	Naan Cholay

Creating a named range is very easy, for this go to formula tab and click on Define name. If you cursor is in the header row of column the box will pick it. In the “refers to”, add the range excluding the header row.

This way we will create three named ranges.

Now go back to sheet1 and in Column A1 & A2, add labels/heads like Type of Food and Food Item.

Have your cursor in row A2 and go to Data tab and click on Data Validation.

In the Allow box, select list. And in source box refer the named range of Type, like the below snapshot.

Now go to row B2 and, like we did earlier, go to Data tab and click on Data Validation.

In the Allow box, select list. And in source box enter this formula.

=INDIRECT($A$2)

And we are done. Now, the drop down values of second column, are dependent on first one.

Click here to download the example dependent drop down sheet