Data Validation is a nice feature for restricting cell inputs, but sometimes you want the user to be able to add an item to the DV list. With the Worksheet_Change event and a dynamic range name, you can allow the user to do just that.
First, set up a dynamic named range for your list
Note the first item in the list is "(new entry)". This will be used to trigger the macro that allows the user to add to the list. Next, create the data validation
Finally, create the code in the Change event to catch when the user selects (new entry).
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim vResp As Variant
Dim sTestValid As String
'Make sure the cell has validation
On Error Resume Next
sTestValid = Target.Validation.Formula1
On Error GoTo 0
'If the validation refers to our list and the user
'selected New entry
If sTestValid = " = ValList" Then
If Target.Value = "(new entry)" Then
'Get the new value from the user
vResp = InputBox("Enter new item", "New Entry")
'If the user didn’t click cancel
If Len(vResp)> 0 Then
'add the new entry to just below ValList
With Me.Range("ValList")
.Cells(.Cells.Count + 1).Value = vResp
End With
'Set the cell to the new entry
Target.Value = vResp
Else
'If the user cancelled, clear the cell
Target.ClearContents
End If
End If
End If
Application.EnableEvents = True
End Sub
You can beef up the code to make sure the user is entering something reasonable. The code can also be modified for use with a hard-coded list, as opposed to a range. But I have to leave something for you to experiment with.
This one's for Ron, who posted a comment yesterday. If you have names in various forms like
First Last
First M. Last
First Last, Jr.
and you need to split them apart, then you need some text manipulation functions. I can't explain it any better than Chip Pearson on his Extract First and Last Names page, so I refer you there.
When using a MultiSelect ListBox, there's no built-in way to preserve the user's selections from one instance of a Userform to the next. When I need to do that, I use a hidden worksheet and store the selections in a range. Then I use the Initialize and QueryClose events to restore the selections.
Private Sub UserForm_Initialize()
Dim Rng As Range
Dim i As Long
'set up the listbox
Me.ListBox1.RowSource = "Sheet1!A1:A10"
Me.ListBox1.MultiSelect = fmMultiSelectMulti
'define the range where the selections are stored
Set Rng = ThisWorkbook.Sheets("lbSelections").Range("lbStored")
'Re-select the stored selections
For i = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(i) = Rng.Cells(i + 1)
Next i
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim i As Long
Dim Rng As Range
'define the range where the selections are stored
Set Rng = ThisWorkbook.Sheets("lbSelections").Range("lbStored")
'clear previously stored selections
Rng.Parent.UsedRange.ClearContents
'store the current selections
For i = 0 To Me.ListBox1.ListCount - 1
Rng.Cells(i + 1).Value = Me.ListBox1.Selected(i)
Next i
'redefine the named range just in case the number of items have changed
Rng.Parent.Names.Add "lbStored", _
Rng.Parent.Range("A1").Resize(Me.ListBox1.ListCount)
End Sub
The hidden worksheet will show a bunch of TRUE and FALSE entries that correspond to what was selected. These values are then used to set the Selected property.
The SQRT() function can be used to return the square root of a number. To find a different root, use the exponentiation operator (^ - the caret above the 6) and the inverse of the root. For instance:
=27^(1/3) returns the cube root of 27, or 3.
=16^(1/4) returns the 4th root of 16, or 2.
Put the "inverse" calculation in parentheses because exponentiation takes precedent over division, and you want the division to happen first.
Excel doesn't provide MAXIF and MINIF functions, but you can roll your own with array functions.
=MAX((B1:B9="Tom")*(A1:A9)) array entered will return 7, the max for Tom
=MIN(IF(B1:B9="Tom",A1:A9,"")) array entered will return 1, the minimum for Tom.
You'll note that the MIN function is a little less straightforward. The problem with MIN and array formulas is that it almost always returns zero. For instance, if the MIN was written like the MAX, then any rows with 'Harry' will return zero and that will be the minimum value in the array. To get around that, an IF function is used to return the values for the name we want and an empty string for any other names. The empty string (like any string) is ignored by the MIN function.
Update: Another application - John comments
I have a column of numbers (16520, 16345, 19456, 16123, 16201, 19325, 19113, 16350, 16787, 16675) and want to use a function to find the highest number in the 16000 group. I think I need to use the MAX and IF Worksheet Functions, but I can't get it. Any help?
Try this formula
VBA includes a built-in Timer function. Timer returns the number of seconds since 12:00 AM. Windows users get some decimals, but Mac users have to settle for the nearest second. I've never been all that convinced of Timer's accuracy anyway. If a fraction of a second is important, I'd be testing Timer before I used it to test my application.
Inspired by a previous comment, here's an example of how you might use Timer
Sub test()
Dim stime As Single
Dim i As Long
stime = Timer
For i = 1 To 1000
Range("a1").Formula = "1234"
Next i
Debug.Print "Formula", Timer - stime
stime = Timer
For i = 1 To 1000
Range("a1").FormulaR1C1 = "1234"
Next i
Debug.Print "FormulaR1C1", Timer - stime
stime = Timer
For i = 1 To 1000
Range("a1").Value = "1234"
Next i
Debug.Print "Value", Timer - stime
stime = Timer
For i = 1 To 1000
Range("a1").Value2 = "1234"
Next i
Debug.Print "Value2", Timer - stime
End Sub
And the results
My analysis of this is that it doesn't matter which one you use. For me, I use the Value property for values and the Formula property for formulas. Call me old fashioned.