<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Excel VBA Code For Excel UserForms & Controls

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

Back to: Excel Custom Function/Formulas . 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

LisboxSort.zip
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 = TrueEnd 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 IfEnd 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 IfEnd 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 IfEnd 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 thetop of the UserForm

Dim dWidth As DoublePrivate Sub ToggleButton1_Click()	If ToggleButton1.Value = True Then		Me.Height = Me.Height * 0.25    Else       	Me.Height = dWidth    End IfEnd SubPrivate Sub UserForm_Initialize()    dWidth = Me.HeightEnd Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft