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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 03:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com