PDA

View Full Version : Solved: But it was working 5 minutes ago....



k13r4n
03-04-2009, 05:56 PM
Ok this one has got me stumped

Im trying to set data validation for a cell and yesterday i had it all working fine, then, for aparently no reason, it started throwing an error and i dont know why, as the code hasnt changed and it was working before.


With Worksheets(1)
.Range("D" & SummaryRow + 9).Validation.Delete
.Range("D" & SummaryRow + 9).Validation.Add Type:=xlValidateList, Operator:=xlBetween, Formula1:="=$AG$1:$AG$32"
End with


and the error is the attached image

Edit: Forgot to say the error springs up on the Validation.Add line

the range specifed in the formula1 is correct and i can set the validation manully without any problems but the VBA has just started generating the error

any ideas??

:beerchug:

kieran

Kenneth Hobs
03-04-2009, 06:32 PM
Not sure how you set SummaryRow. Step through the code with F8 to see where it errors. I could see a runtime error if SummaryRow was defined as an Integer and the value was not an integer.

k13r4n
03-04-2009, 06:54 PM
thanks for the reply, SummaryRow is all good i checked that.

cheers


kieran

Bob Phillips
03-05-2009, 01:44 AM
Has the worksheet become protected?

k13r4n
03-05-2009, 11:21 AM
just checked an no its not protected, this one has really got me! if i comment the Validation.Add line out then the subs runs fine (there are about 100 lines of vba before and after that line), so i dont think there is anything wrong with the sheet or the rest of the macro

ive even tried running it on a different computer and i still get the error.

I had a look online at a few bits and bobs related to the error but i dont really understand what there going on about, something to do with loosing the assosiation with the object to wich the code refers to or something :dunno

the bit thats really anoying me is that it DID work before....:banghead: :banghead: :banghead:

Bob Phillips
03-05-2009, 12:43 PM
The only thing I could tthink of was protection, and that does throw that error at that point.

k13r4n
03-05-2009, 02:02 PM
checked and double check, the sheets is not protected.

i even tried writing a quick macro on a completely new workbook that just sets cell A1's data validation to a small list in C1:C10 and i get the same error :banghead:

Bizzare


Kieran

k13r4n
03-05-2009, 02:05 PM
maybe excel has packed a sad :dunno

k13r4n
03-05-2009, 03:30 PM
So after 2 days of this >:dau:
and me thinking about this > :bat2:
I fixed it :thumb

I started out by recording a macro to set the data validation and looking at the code it came up with to see if i was doing something wrong and the recoded macro worked every time.


With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$AG$1:$AG$32"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


So i copied it into my Sub and modifed it to the range refrence rather than Selection and ran it.....


With Worksheets(1)
With .Range("D" & SummaryRow + 9).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$AG$1:$AG$32"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End With


it errored :banghead:

But determined not to give up i decided to try this


Worksheets(1).Activate
Range("D" & SummaryRow + 9).Activate
With ActiveCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$AG$1:$AG$32"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With


And Bingo, All working :cool:

Got no idea why but this way works and im happy :rofl:

:beerchug:

Edit: Forgot to say thanks to those who replyed and tried to help:thumb If it wasnt for your help over the last 6 months i wouldnt be were i am with my VBA and probably wouldnt have had the know how to find a work-around

:beerchug: