VLOOKUP/Validation box macro
 

Hi all......

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..........

TIA
Vaya con Dios,
Chuck, CABGx3

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

VLOOKUP/Validation box macro
 

Thanks Bill......

I'm by no means a programmer and was scared when I first saw your code, but
it was clear enough that I eventually was able to tune it to do exactly what
I wanted in my situation..........here's my result, based on your
response.....it works super.......

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(c1,mytable,2,false)"
Case 2:
'Me.Range ("A2")
Range("a2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=$K$1:$K$5"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'xlValidateWholeNumber
End Select
End If
End Sub

Thanks muchly again,.....just one more question, is there any way to modify
the code so the trigger entries in A1 could be some text instead of just a 1
and 2?

Vaya con Dios,
Chuck, CABGx3





"Bill Manville" wrote in message
...
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

VLOOKUP/Validation box macro
 

Thanks anyway, but I was able to finally figure it out myself as to how to
make "text" do the triggering.......

Vaya con Dios,
Chuck, CABGx3


"CLR" wrote in message
...
Thanks Bill......

I'm by no means a programmer and was scared when I first saw your code,
but
it was clear enough that I eventually was able to tune it to do exactly
what
I wanted in my situation..........here's my result, based on your
response.....it works super.......

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(c1,mytable,2,false)"
Case 2:
'Me.Range ("A2")
Range("a2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=$K$1:$K$5"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
'xlValidateWholeNumber
End Select
End If
End Sub

Thanks muchly again,.....just one more question, is there any way to
modify
the code so the trigger entries in A1 could be some text instead of just a
1
and 2?

Vaya con Dios,
Chuck, CABGx3





"Bill Manville" wrote in message
...
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