Solved: But it was working 5 minutes ago....

[k13r4n]

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.

[vba]
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
[/vba]

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??



kieran

[Kenneth Hobs]

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]

thanks for the reply, SummaryRow is all good i checked that.

cheers


kieran

[Bob Phillips]

Has the worksheet become protected?

[k13r4n]

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

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

[Bob Phillips]

The only thing I could tthink of was protection, and that does throw that error at that point.

[k13r4n]

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

Bizzare


Kieran

[k13r4n]

maybe excel has packed a sad

[k13r4n]

So after 2 days of this >
and me thinking about this >
I fixed it

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.

[vba]
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
[/vba]

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

[vba]
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
[/vba]

it errored

But determined not to give up i decided to try this

[vba]
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
[/vba]

And Bingo, All working

Got no idea why but this way works and im happy



Edit: Forgot to say thanks to those who replyed and tried to help 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