Dynamically adding an in-cell drop-down list (i.e. Validation Object) to a cell?

[debartsa]

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

 

[pikus]

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

 

[Tom Ogilvy]

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.

 

[debartsa]

Thanks for your help folks! I'll give them both a try.

 

[wailoon]

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

 

[fnkleroi]

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

*Thanks for your help folks! I'll give them both a try.

-
fnklero