In an extraordinarily weird week with VB, this is the weirdest of all. I have :
Dim Shp As Shape

and futher on:

Range("title.1:clean.end.personal").ClearComments
'
'validation settings for cells are still there

On Error Resume Next
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next

''validation settings for cells are gone

I have isolated the code to the area between the two comments (which say
what is happening). How can this possibly be? Earlier in the macro I have run
the identical code in a different sheet and validation settings are
unchanged. EH?
Regards, Brett

Sadly, the Data Validation drop-down is consider to be a Shape. If you
insert Data Validation on two cells, one with only a message-on-click and the
other with a drop-down and then put some other Shape (say something from the
Drawing Toolbar) on the sheet as well and run:

Sub dural()
Dim s As Shape
For Each s In ActiveSheet.Shapes
MsgBox (s.Name)
s.Delete
Next
End Sub

You will see that both the "real" shape and the drop down are called out and
deleted. The Validated cell that only displays a message when clicked will
not be disturbed.
--
Gary''s Student - gsnu200840


"Brettjg" wrote:

> In an extraordinarily weird week with VB, this is the weirdest of all. I have :
> Dim Shp As Shape
>
> and futher on:
>
> Range("title.1:clean.end.personal").ClearComments
> '
> 'validation settings for cells are still there
>
> On Error Resume Next
> For Each Shp In ActiveSheet.Shapes
> Shp.Delete
> Next
>
> ''validation settings for cells are gone
>
> I have isolated the code to the area between the two comments (which say
> what is happening). How can this possibly be? Earlier in the macro I have run
> the identical code in a different sheet and validation settings are
> unchanged. EH?
> Regards, Brett

You could include any of these checks before deleting the DV dropdown

If TypeName(sh.DrawingObject) <> "DropDown" Then
If sh.Type <> msoFormControl Then ' any type of Form control
If left$(sh.Name,9) <> "Drop Down" Then

Regards,
Peter T



"Brettjg" <(E-Mail Removed)> wrote in message
newsABE376E-6517-4217-8B7A-(E-Mail Removed)...
> In an extraordinarily weird week with VB, this is the weirdest of all. I
> have :
> Dim Shp As Shape
>
> and futher on:
>
> Range("title.1:clean.end.personal").ClearComments
> '
> 'validation settings for cells are still there
>
> On Error Resume Next
> For Each Shp In ActiveSheet.Shapes
> Shp.Delete
> Next
>
> ''validation settings for cells are gone
>
> I have isolated the code to the area between the two comments (which say
> what is happening). How can this possibly be? Earlier in the macro I have
> run
> the identical code in a different sheet and validation settings are
> unchanged. EH?
> Regards, Brett

See this page
http://www.rondebruin.nl/controlsobjectsworksheet.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Brettjg" <(E-Mail Removed)> wrote in message newsABE376E-6517-4217-8B7A-(E-Mail Removed)...
> In an extraordinarily weird week with VB, this is the weirdest of all. I have :
> Dim Shp As Shape
>
> and futher on:
>
> Range("title.1:clean.end.personal").ClearComments
> '
> 'validation settings for cells are still there
>
> On Error Resume Next
> For Each Shp In ActiveSheet.Shapes
> Shp.Delete
> Next
>
> ''validation settings for cells are gone
>
> I have isolated the code to the area between the two comments (which say
> what is happening). How can this possibly be? Earlier in the macro I have run
> the identical code in a different sheet and validation settings are
> unchanged. EH?
> Regards, Brett
>
> __________ Information from ESET Smart Security, version of virus signature database 3950 (20090320) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 3952 (20090320) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hi GS, well I'll be damned! Peter T has a nice little line that should solve
the problem, but to be frank, it's a ridiculous little bug in Excel. Who
would ever want to delete the dropdowns and retain the validation?

Anyway, on another matter, who the hell is Gary? Regards, Brett


"Gary''s Student" wrote:

> Sadly, the Data Validation drop-down is consider to be a Shape. If you
> insert Data Validation on two cells, one with only a message-on-click and the
> other with a drop-down and then put some other Shape (say something from the
> Drawing Toolbar) on the sheet as well and run:
>
> Sub dural()
> Dim s As Shape
> For Each s In ActiveSheet.Shapes
> MsgBox (s.Name)
> s.Delete
> Next
> End Sub
>
> You will see that both the "real" shape and the drop down are called out and
> deleted. The Validated cell that only displays a message when clicked will
> not be disturbed.
> --
> Gary''s Student - gsnu200840
>
>
> "Brettjg" wrote:
>
> > In an extraordinarily weird week with VB, this is the weirdest of all. I have :
> > Dim Shp As Shape
> >
> > and futher on:
> >
> > Range("title.1:clean.end.personal").ClearComments
> > '
> > 'validation settings for cells are still there
> >
> > On Error Resume Next
> > For Each Shp In ActiveSheet.Shapes
> > Shp.Delete
> > Next
> >
> > ''validation settings for cells are gone
> >
> > I have isolated the code to the area between the two comments (which say
> > what is happening). How can this possibly be? Earlier in the macro I have run
> > the identical code in a different sheet and validation settings are
> > unchanged. EH?
> > Regards, Brett

Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards, Brett

"Peter T" wrote:

> You could include any of these checks before deleting the DV dropdown
>
> If TypeName(sh.DrawingObject) <> "DropDown" Then
> If sh.Type <> msoFormControl Then ' any type of Form control
> If left$(sh.Name,9) <> "Drop Down" Then
>
> Regards,
> Peter T
>
>
>
> "Brettjg" <(E-Mail Removed)> wrote in message
> newsABE376E-6517-4217-8B7A-(E-Mail Removed)...
> > In an extraordinarily weird week with VB, this is the weirdest of all. I
> > have :
> > Dim Shp As Shape
> >
> > and futher on:
> >
> > Range("title.1:clean.end.personal").ClearComments
> > '
> > 'validation settings for cells are still there
> >
> > On Error Resume Next
> > For Each Shp In ActiveSheet.Shapes
> > Shp.Delete
> > Next
> >
> > ''validation settings for cells are gone
> >
> > I have isolated the code to the area between the two comments (which say
> > what is happening). How can this possibly be? Earlier in the macro I have
> > run
> > the identical code in a different sheet and validation settings are
> > unchanged. EH?
> > Regards, Brett
>
>
>

Thanks Ron, a very vague problem solved. Regards, Brett

"Ron de Bruin" wrote:

> See this page
> http://www.rondebruin.nl/controlsobjectsworksheet.htm
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "Brettjg" <(E-Mail Removed)> wrote in message newsABE376E-6517-4217-8B7A-(E-Mail Removed)...
> > In an extraordinarily weird week with VB, this is the weirdest of all. I have :
> > Dim Shp As Shape
> >
> > and futher on:
> >
> > Range("title.1:clean.end.personal").ClearComments
> > '
> > 'validation settings for cells are still there
> >
> > On Error Resume Next
> > For Each Shp In ActiveSheet.Shapes
> > Shp.Delete
> > Next
> >
> > ''validation settings for cells are gone
> >
> > I have isolated the code to the area between the two comments (which say
> > what is happening). How can this possibly be? Earlier in the macro I have run
> > the identical code in a different sheet and validation settings are
> > unchanged. EH?
> > Regards, Brett
> >
> > __________ Information from ESET Smart Security, version of virus signature database 3950 (20090320) __________
> >
> > The message was checked by ESET Smart Security.
> >
> > http://www.eset.com
> >
> >
> >
>
> __________ Information from ESET Smart Security, version of virus signature database 3952 (20090320) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
>

Gary taught me Excel and much else.
--
Gary''s Student - gsnu200840


"Brettjg" wrote:

> Hi GS, well I'll be damned! Peter T has a nice little line that should solve
> the problem, but to be frank, it's a ridiculous little bug in Excel. Who
> would ever want to delete the dropdowns and retain the validation?
>
> Anyway, on another matter, who the hell is Gary? Regards, Brett
>
>
> "Gary''s Student" wrote:
>
> > Sadly, the Data Validation drop-down is consider to be a Shape. If you
> > insert Data Validation on two cells, one with only a message-on-click and the
> > other with a drop-down and then put some other Shape (say something from the
> > Drawing Toolbar) on the sheet as well and run:
> >
> > Sub dural()
> > Dim s As Shape
> > For Each s In ActiveSheet.Shapes
> > MsgBox (s.Name)
> > s.Delete
> > Next
> > End Sub
> >
> > You will see that both the "real" shape and the drop down are called out and
> > deleted. The Validated cell that only displays a message when clicked will
> > not be disturbed.
> > --
> > Gary''s Student - gsnu200840
> >
> >
> > "Brettjg" wrote:
> >
> > > In an extraordinarily weird week with VB, this is the weirdest of all. I have :
> > > Dim Shp As Shape
> > >
> > > and futher on:
> > >
> > > Range("title.1:clean.end.personal").ClearComments
> > > '
> > > 'validation settings for cells are still there
> > >
> > > On Error Resume Next
> > > For Each Shp In ActiveSheet.Shapes
> > > Shp.Delete
> > > Next
> > >
> > > ''validation settings for cells are gone
> > >
> > > I have isolated the code to the area between the two comments (which say
> > > what is happening). How can this possibly be? Earlier in the macro I have run
> > > the identical code in a different sheet and validation settings are
> > > unchanged. EH?
> > > Regards, Brett

Then he taught you well: you've a very great help to me on many occasions,
and my sincere thanks for all of that.

Was Gary into Grateful Dead as well? Any fan of Jerry Garcia has to be at
least a little bit bent. Garcia's head used to spin faster than Linda Blair's.

"Gary''s Student" wrote:

> Gary taught me Excel and much else.
> --
> Gary''s Student - gsnu200840
>
>
> "Brettjg" wrote:
>
> > Hi GS, well I'll be damned! Peter T has a nice little line that should solve
> > the problem, but to be frank, it's a ridiculous little bug in Excel. Who
> > would ever want to delete the dropdowns and retain the validation?
> >
> > Anyway, on another matter, who the hell is Gary? Regards, Brett
> >
> >
> > "Gary''s Student" wrote:
> >
> > > Sadly, the Data Validation drop-down is consider to be a Shape. If you
> > > insert Data Validation on two cells, one with only a message-on-click and the
> > > other with a drop-down and then put some other Shape (say something from the
> > > Drawing Toolbar) on the sheet as well and run:
> > >
> > > Sub dural()
> > > Dim s As Shape
> > > For Each s In ActiveSheet.Shapes
> > > MsgBox (s.Name)
> > > s.Delete
> > > Next
> > > End Sub
> > >
> > > You will see that both the "real" shape and the drop down are called out and
> > > deleted. The Validated cell that only displays a message when clicked will
> > > not be disturbed.
> > > --
> > > Gary''s Student - gsnu200840
> > >
> > >
> > > "Brettjg" wrote:
> > >
> > > > In an extraordinarily weird week with VB, this is the weirdest of all. I have :
> > > > Dim Shp As Shape
> > > >
> > > > and futher on:
> > > >
> > > > Range("title.1:clean.end.personal").ClearComments
> > > > '
> > > > 'validation settings for cells are still there
> > > >
> > > > On Error Resume Next
> > > > For Each Shp In ActiveSheet.Shapes
> > > > Shp.Delete
> > > > Next
> > > >
> > > > ''validation settings for cells are gone
> > > >
> > > > I have isolated the code to the area between the two comments (which say
> > > > what is happening). How can this possibly be? Earlier in the macro I have run
> > > > the identical code in a different sheet and validation settings are
> > > > unchanged. EH?
> > > > Regards, Brett

If you were wary, now, it wouldn't be a trap!

I never thought about this either. I wonder if it hoses the AutoFilter
dropdowns as well.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Brettjg" <(E-Mail Removed)> wrote in message
news48BEA53-E737-49CD-A520-(E-Mail Removed)...
> Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards,
> Brett
>
> "Peter T" wrote:
>
>> You could include any of these checks before deleting the DV dropdown
>>
>> If TypeName(sh.DrawingObject) <> "DropDown" Then
>> If sh.Type <> msoFormControl Then ' any type of Form control
>> If left$(sh.Name,9) <> "Drop Down" Then
>>
>> Regards,
>> Peter T
>>
>>
>>
>> "Brettjg" <(E-Mail Removed)> wrote in message
>> newsABE376E-6517-4217-8B7A-(E-Mail Removed)...
>> > In an extraordinarily weird week with VB, this is the weirdest of all.
>> > I
>> > have :
>> > Dim Shp As Shape
>> >
>> > and futher on:
>> >
>> > Range("title.1:clean.end.personal").ClearComments
>> > '
>> > 'validation settings for cells are still there
>> >
>> > On Error Resume Next
>> > For Each Shp In ActiveSheet.Shapes
>> > Shp.Delete
>> > Next
>> >
>> > ''validation settings for cells are gone
>> >
>> > I have isolated the code to the area between the two comments (which
>> > say
>> > what is happening). How can this possibly be? Earlier in the macro I
>> > have
>> > run
>> > the identical code in a different sheet and validation settings are
>> > unchanged. EH?
>> > Regards, Brett
>>
>>
>>