Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Vlookup with validation | Excel Worksheet Functions | |||
Data Validation, VLookup or If ??????? | Excel Discussion (Misc queries) | |||
Data Validation-VLOOKUP | Excel Discussion (Misc queries) | |||
data validation using vlookup | Excel Worksheet Functions | |||
vlookup with validation table | Excel Discussion (Misc queries) |