VLOOKUP/Validation box macro
Clr wrote:
Is it possible to create some sort of macro that will detect if a 1 is
entered in A1 and thereby cause A2 to have a VLOOKUP formula set in it, and
if a 2 is entered in A1 to have a Dropdown Validation box appear in A2
instead of the VLOOKUP formula, and back and forth......and if nothing, or
anything else is entered in A1, then A2 will remain blank..........
Yes.
In the module belonging to the worksheet something like this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Me.Range("A1"), Target) Is Nothing Then Exit Sub
Me.Range("A2").Clear
If TypeName(Me.Range("A1").Value)="Double" Then
Select Case Me.Range("A1")
Case 1:
Me.Range("A2").Formula = "=VLOOKUP(...)"
Case 2:
Me.Range("A2").Validation.Add xlValidateWholeNumber
End Select
End If
End Sub
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
|