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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.