Excel VBA Code For Excel UserForms & Controls

< Back to Search results

 Category: [Excel]  Demo Available 

Excel VBA Code For Excel UserForms & Controls


Got any Excel/VBA Questions? Excel Help

Add a unique and sorted list to a ListBox:

Sub SortAndRemoveDupes()

	Dim rListSort As Range, rOldList As Range

	Dim strRowSource As String

	'Clear Hidden sheet Column A ready for list

	Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp)).Clear



	'Set range variable to list we want

	Set rOldList = Sheet2.Range("A1", Sheet2.Range("A65536").End(xlUp))

	'Use AdvancedFilter to copy the list to Column A _

	of the hidden sheet and remove all dupes

	rOldList.AdvancedFilter Action:=xlFilterCopy, _

		CopyToRange:=Sheet1.Cells(1, 1), Unique:=True


	'Set range variable to the new non dupe list

	Set rListSort = Sheet1.Range("A1", Sheet1.Range("A65536").End(xlUp))


	'Sort the new non dupe list

    With rListSort

		.Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _

		OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    End With


	'Parse the address of the sorted unique items

	strRowSource = Sheet1.Name & "!" & Sheet1.Range _

	("A2", Sheet1.Range("A65536").End(xlUp)).Address


	Sheet1.Range("A1") = "New Sorted Unique List"

		With UserForm1.ListBox1

			'Clear old ListBox RowSource

			.RowSource = vbNullString

			'Parse new one

			.RowSource = strRowSource

  		End With


End Sub

Download an example of the above code!.

Prevent a User From Closing A UserForm via the top X

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

	If CloseMode = 0 Then Cancel = True

End Sub

Use The Enter Key To Add Text From a TextBox

This code will keep adding text from a TextBox on a UserForm to the cell below the last entry in Column A each time the Enter Key is Pressed.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _

                                     ByVal Shift As Integer)

	If KeyCode = 13 Then

		Sheet1.Range("A65536").End(xlUp).Offset(1, 0) = TextBox1

		TextBox1 = vbNullString

    End If

End Sub

Validate a TextBox For Numbers Only Entry

Private Sub TextBox1_Change()

	If TextBox1 = vbNullString Then Exit Sub

    	If Not IsNumeric(TextBox1) Then

 			MsgBox "Sorry, numbers only"

     		TextBox1 = vbNullString

    	End If

End Sub

Validate a TextBox For Text Only Entry

Note we cannot use the Change Event for text as they may enter something like 123Area, this would mean the Change Event would fire as soon as they typed a number.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

	If TextBox1 = vbNullString Then Exit Sub

    	If IsNumeric(TextBox1) Then

     		MsgBox "Sorry, text only"

     		TextBox1 = vbNullString

     		Cancel = True 'Stops them leaving with numbers in the TextBox

    	End If

End Sub

Add a Minimize/Maximize Button to a UserForm

Here is a nice simple method you can use for a Minimize/Maximize Button on a UserForm. Just add a ToggleButton to the top of the UserForm

Dim dWidth As Double

Private Sub ToggleButton1_Click()

	If ToggleButton1.Value = True Then

		Me.Height = Me.Height * 0.25


       	Me.Height = dWidth

    End If

End Sub

Private Sub UserForm_Initialize()

    dWidth = Me.Height

End Sub


See also:

Index to Excel VBA Code
Transpose Rows Into Columns
Excel Lookup nth Occurrence/Instance
Add Excel UDF/Custom Function to a Category & Add a Description
The Ultimate Excel Lookup Function
Update Links in Excel
Stop UserForm From Closing via X


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)