Resetting Data Validation with VBA

[Mifty]

Hi everyone,

I'm trying to clear data validation from a range of cells in all the sheets
of a workbook.

I've found various bits of code that are doing the same sort of thing as I
need to do and Bob Phillips also helped
http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

I should have perservered at the time Bob Phillips was helping. I was taking
too long with a project so to get it finished I just recorded the whole lot,
selecting each cell and replacing contents with the ="". Of course it took
ages but seemed to work then it wouldn't. So I still haven't finished the
project and look silly because I said it was nearly there!

So I've played around and come up with this but it's not detecting the data
validation cells I get the msgbox saying No Data Validation Cells.

If anyone could help I would be more than grateful!

Option Explicit
Sub Datavalreset()
Dim rng2 As Range ' specifies range in wksh
Dim rng As Range 'specifies type of cell i.e. special cells
Dim ws As Worksheet
Dim cell As Range

For Each ws In ActiveWorkbook.Worksheets

On Error Resume Next
Set rng = Range("A1:T45")
Set rng2 = rng.Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0
If Not rng2 Is Nothing Then

For Each cell In rng2

If cell.Validation.Type = xlValidateList Then
cell.Value = ""
End If
Next
Else
MsgBox "No Data Validation Cells"
End If
Next ws
End Sub

Many thanks

 

[Mifty]

Hi Shasur,

Thanks for responding but I'm relatively new to this and your hint has
totally confused me

I'm not Ashish btw - if your message was in response to Ashish I would fell
better about not understanding any of it

Cheers

 

[Mifty]

Hi,

I've reposted because I think the routine may have stopped working when I've
added sheet protection it's protect user interface only.

 

[Shasur]

Hi Mifty

Apologies for confusing you. My post is reply to another post

Sorry for the trouble

Cheers
Shasur

 

[Mifty]

No problem Shasur,

I'm glad I'm not as stupid as I thought I was

Cheers

 

[Barb Reinhardt]

It worked here. I had to set the data validation to be a list. You may
want to put in a debug.print statement like this inside the For each cell
section

Debug.Print cell.Address, cell.Validation.Type

xlValidateList is 3 here.

 

[Mifty]

Hi Barb,

How do I set a list? - sorry if this is a really basic question

Thanks for helping

 

[Mifty]

Sorry Barb,

Having another Blonde moment!

it is set as list so I'll just try the debug print statement and get back to
you
Cheers

 

[Mifty]

Hi Barb,

I'm not geting anything in the immediate window but rng2 and cell are
nothing in the watch window if that helps at all.

Could you explain what you mean by "xlValidateList is 3 here.". I'm
understanding you to mean that you were expecting the debug print statement
to return a value relating to the Xl validate list.

Thanks for your help

 

[Mifty]

Hi Barb,

Could my problem be that there is no data validation on the first 3 sheets?

There are approx 30 sheets and about 6 without data val.

I've found from googling that you could specify an array of sheets you want
to use or specify which ones not to use.

What do you think?