Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
Hi, newbie post here - I've trawled the archives and can't seem to fin
an answer to my problem. I'm using Win2000/Excel 2000/VB6 Basically I'm trying to add drop down list style validation to a cell. I believe this should be possible using the validation parameter of range: Range("e5").Validation.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:=txt Where text is a comma separated string ("M,F" in this case) Every attempt to use validation in such a way gives the error "Run-Time error '1004': Application-defined or object-defined error". I've played with many variations on the above syntax, and the sam error message always results. Any ideas what I need to do to get this working? Might it be m references or something like that -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
You have to clear out the previous validation:
Range("E5").Validation.Delete -- Jim Rech Excel MVP "ZoomZoom " wrote in message ... | Hi, newbie post here - I've trawled the archives and can't seem to find | an answer to my problem. | I'm using Win2000/Excel 2000/VB6 | | Basically I'm trying to add drop down list style validation to a cell. | I believe this should be possible using the validation parameter of a | range: | | Range("e5").Validation.Add Type:=xlValidateList, _ | AlertStyle:=xlValidAlertStop, _ | Operator:=xlBetween, _ | Formula1:=txt | | Where text is a comma separated string ("M,F" in this case) | | Every attempt to use validation in such a way gives the error: | "Run-Time error '1004': Application-defined or object-defined error". | I've played with many variations on the above syntax, and the same | error message always results. | | Any ideas what I need to do to get this working? Might it be my | references or something like that? | | | --- | Message posted from http://www.ExcelForum.com/ | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
Thanks very much for the reply.
I have tried deleting the previous validation as you suggest. I receiv the same error. I've also tried modifying rather than adding validatio - same applies again. Cheers, Justi -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
Your code works for me in Excel 97 thru 2003:
Dim Txt As String Txt = "M,F" On Error Resume Next Range("E5").Validation.Delete On Error GoTo 0 Range("e5").Validation.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:=Txt If you are using a non-USA version of Excel, try changeing "M,F" to "M;F". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "ZoomZoom " wrote in message ... Thanks very much for the reply. I have tried deleting the previous validation as you suggest. I receive the same error. I've also tried modifying rather than adding validation - same applies again. Cheers, Justin --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
I've tried directly cutting and pasting the supplied code into a fresh
VBA Project. I'm using the automatically generated Worksheet_Activate callback in this case, I've also tried the same thing in a ComboBox Change procedure. No luck. I always get to the line: Range("e5").Validation.Add Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, _ Formula1:=Txt and the following error is thrown: "Run-Time error '1004': Application-defined or object-defined error". On debugging, the xl constants seem to be defined, as is the variable Txt. Semi-colon seperated text rather than Comma separated seems to make no difference. I've tried the whole thing with a variety of definitions of the range as well. Thanks for the help - it's making the frustration easier to deal with! Justin --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
I've attached a zipped up working example that uses Chip's code i
the Worksheet activate event. You couldn't send it by email could you? The board code has decided to display the zip rather than linking it. Thanks very much, Justi -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
Have you tried writing your validations in a range of cells, and then
programmatically setting your validation to that range? That's what I do. With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=$AD$" & StCol & ":$AD$" & EndCol ' StCol and EndCol are variables representing the row numbers that ' start and end my column of validations .IgnoreBlank = False .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With HTH Ed "ZoomZoom " wrote in message ... I've attached a zipped up working example that uses Chip's code in the Worksheet activate event. You couldn't send it by email could you? The board code has decided to display the zip rather than linking it. Thanks very much, Justin --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
Have you tried writing your validations in a range of cells, and then
programmatically setting your validation to that range? That's what do. Tried it... Arrggh - exactly the same error: "Run-Time error '1004': Application-defined or object-defined error" I'm almost at the giving up stage. Anybody know if there are any contraindicators for what I'm trying t do? Any service packs, prerequisite software or references needed? Pure and simple - adding validation does not work on any of th computers I have here (I've tried it on a couple now). Thanks everyone fo contributing Justi -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
Can you do it manually using DataValidation? If that works, then step
through it with the macro recorder and go from there. If you can't set it using the menus, then you do have other problems. Ed "ZoomZoom " wrote in message ... Have you tried writing your validations in a range of cells, and then programmatically setting your validation to that range? That's what I do. Tried it... Arrggh - exactly the same error: "Run-Time error '1004': Application-defined or object-defined error" I'm almost at the giving up stage. Anybody know if there are any contraindicators for what I'm trying to do? Any service packs, prerequisite software or references needed? Pure and simple - adding validation does not work on any of the computers I have here (I've tried it on a couple now). Thanks everyone fo contributing Justin --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
If that works, then step
through it with the macro recorder and go from there. That's it! I didn't realise you could do that, how bloomin' useful! Right, the problem is entirely because I'm trying to do things on sheet other than Sheet1. As soon as I do it all entirely on sheet1 I' flying - it appears the validation has to be added with the cells o Sheet1 selected, otherwise it doesn't work. Another problem was obscuring this one though: My columns are numbere rather than lettered, and I can't refer to cells as A1:B4, I have t use R1C1:R4C2. What on earth is going on in this case? Thanks very much for your help here. Justi -- Message posted from http://www.ExcelForum.com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA programmatic validation problem
I know I've seen that before in the NG. This is what I use to do searches
the http://www.rondebruin.nl/Google.htm. Try it out. With "columns numbered" as search criteria, I got this one that might be useful to you: http://groups.google.com/groups?hl=e...as_uauthors%3D (All of that is supposed to be one line of URL - I don't know how to make them smaller. You might have to take out some line breaks to make it work.) HTH Ed "ZoomZoom " wrote in message ... If that works, then step through it with the macro recorder and go from there. That's it! I didn't realise you could do that, how bloomin' useful! Right, the problem is entirely because I'm trying to do things on a sheet other than Sheet1. As soon as I do it all entirely on sheet1 I'm flying - it appears the validation has to be added with the cells on Sheet1 selected, otherwise it doesn't work. Another problem was obscuring this one though: My columns are numbered rather than lettered, and I can't refer to cells as A1:B4, I have to use R1C1:R4C2. What on earth is going on in this case? Thanks very much for your help here. Justin --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
export filter - programmatic access to | Charts and Charting in Excel | |||
Programmatic Hiding | Excel Discussion (Misc queries) | |||
'Programmatic access to Visual Basic project is not trusted.' message | Excel Programming | |||
Excel Validation Problem in VB6 | Excel Programming | |||
"Programmatic Access to be Denied" displays when running macro on excel in XP | Excel Programming |