Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell?
Hi everybody,
I'm using Excel 97 with VBA to dynamically add a Validation object to cell("A7"). My goal is to have an in-Cell dropdown list display when a user selects the cell... With ThisWorkbook.ActiveSheet.Range("A7").Validation .Add Type:=xlValidateList, Formula1:="Red,Green,Blue" End With I get a Run-time error='1004' Application Defined or object-defined error? Can I dynamically add a in-cell drop down list to a cell? Thanks for any help! Sam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell?
I have a little something for you:
ActiveSheet.DropDowns.Add(0, 76.5, 96, 15.75).Select With Selection .Placement = xlMove .PrintObject = True .ListFillRange = "$A$1:$A$3" .LinkedCell = "$A$4" .DropDownLines = 3 End With Range("A1").Value = "Red" Range("A2").Value = "Blue" Range("A3").Value = "Green" End Sub When it comes to "Add(0, 76.5, 96, 15.75)" Starting from the top left corner of the page, "0" = how far over "76.5" = how far down "96" = Length "15.75" = Height You'll probably need to adjust these numbers to suit your worksheet. This isn't a very detailed explanation, but it should set you on th right track... - Piku -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell?
possibly try this (delete any existing validation)
Sub AAAtest() With ThisWorkbook.ActiveSheet.Range("A7").Validation .Delete .Add Type:=xlValidateList, Formula1:="Red,Green,Blue" End With End Sub Pikus is showing how to add a combobox from the forms menu. This doesn't have anything to do with data validation. -- Regards, Tom Ogilvy debartsa wrote in message ... Hi everybody, I'm using Excel 97 with VBA to dynamically add a Validation object to cell("A7"). My goal is to have an in-Cell dropdown list display when a user selects the cell... With ThisWorkbook.ActiveSheet.Range("A7").Validation .Add Type:=xlValidateList, Formula1:="Red,Green,Blue" End With I get a Run-time error='1004' Application Defined or object-defined error? Can I dynamically add a in-cell drop down list to a cell? Thanks for any help! Sam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell?
Thanks for your help folks! I'll give them both a try.
"debartsa" wrote in message ... Hi everybody, I'm using Excel 97 with VBA to dynamically add a Validation object to cell("A7"). My goal is to have an in-Cell dropdown list display when a user selects the cell... With ThisWorkbook.ActiveSheet.Range("A7").Validation .Add Type:=xlValidateList, Formula1:="Red,Green,Blue" End With I get a Run-time error='1004' Application Defined or object-defined error? Can I dynamically add a in-cell drop down list to a cell? Thanks for any help! Sam |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell?
Hi Tom Ogilvy, I am having the same problem as debartsa but I'm using Excel XP. I hav been searching for quite some time and try many options but still can' solve it, this is my code 1 With sheet1.Range("E3").Validation 2 .Delete 3 .Add Type:=xlValidateList, Formula1:="a,b,c,d" 4 .IgnoreBlank = True 5 .InCellDropdown = True 6 End With I keep getting "Run-time error='1004' Application Defined o object-defined error" on the line that adds type (line 3). However, i I move .IgnoreBlank (line 4)or .IncellDropDown (line 5) statement t above .add Type... (line 3) I get the same error on those statements. Appreciate if you could help fix this problem. Thanks wailoo ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message292105.htm |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell?
Alright it this seemed to work, thank you Tom. Range(strAccuracyLevelAdrs).Value = "" With Range(strAccuracyLevelAdrs).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ Formula1:=strEngineering1 & "," & strEngineering2 "," & strEngineering3 End With Range(strAccuracyLevelAdrs).Select Thank you. Fnkleroi debartsa wrote: *Thanks for your help folks! I'll give them both a try. "debartsa" wrote in message ... Hi everybody, I'm using Excel 97 with VBA to dynamically add a Validation objec to cell("A7"). My goal is to have an in-Cell dropdown list display when a use selects the cell... With ThisWorkbook.ActiveSheet.Range("A7").Validation .Add Type:=xlValidateList, Formula1:="Red,Green,Blue" End With I get a Run-time error='1004' Application Defined o object-defined error? Can I dynamically add a in-cell drop down list to a cell? Thanks for any help! Sam - fnklero ----------------------------------------------------------------------- Posted via http://www.mcse.m ----------------------------------------------------------------------- View this thread: http://www.mcse.ms/message292105.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permenent appearance of the drop down arrow when adding validation toa cell | Excel Worksheet Functions | |||
Validation cell entries (Drop-down list) | Excel Discussion (Misc queries) | |||
Default Cell Value with Validation Drop-Down List | Excel Discussion (Misc queries) | |||
How to dynamically select a validation list from data in a cell? | Excel Discussion (Misc queries) | |||
adding a list validation to a cell | Excel Programming |