Hi all,

I have embedded two buttons from the control toolbox onto my spreadsheet and
assigned macros to them. One counts and lists the number and serial of
photos, the other removes the hyperlinks within them. I would like to write
code to remove the photos, but leave the buttons. How do I do this please?
This is what I have so far (which deletes everything!!):

Sub RemovePhotos()

Dim mySh As shape

For Each mySh In ActiveSheet.Shapes
If Not mySh.Name = "Count_and_List_Photos" Or mySh.Name =
"RemoveHyperlinks" Then
mySh.Delete
Else
'do nothing
End If

Next mySh

End Sub

Sub RemovePhotos()
Dim mySh As Shape

For Each mySh In ActiveSheet.Shapes
If mySh.Type <> msoOLEControlObject Then
If Not mySh.Name = "Count_and_List_Photos" Or mySh.Name =
"RemoveHyperlinks" Then
mySh.Delete
Else
'do nothing
End If
End If
Next mySh

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ewan7279" <(E-Mail Removed)> wrote in message
news:981EC62A-F450-49B3-8D1E-(E-Mail Removed)...
> Hi all,
>
> I have embedded two buttons from the control toolbox onto my spreadsheet
> and
> assigned macros to them. One counts and lists the number and serial of
> photos, the other removes the hyperlinks within them. I would like to
> write
> code to remove the photos, but leave the buttons. How do I do this
> please?
> This is what I have so far (which deletes everything!!):
>
> Sub RemovePhotos()
>
> Dim mySh As shape
>
> For Each mySh In ActiveSheet.Shapes
> If Not mySh.Name = "Count_and_List_Photos" Or mySh.Name =
> "RemoveHyperlinks" Then
> mySh.Delete
> Else
> 'do nothing
> End If
>
> Next mySh
>
> End Sub

Your logic is wrong. the not operator takes presedence over the and. You
really had:
(Not A) or B
You could have done
(Not A) and (Not B)
Applying DeMorgans
Not (A or B)

Sub RemovePhotos()

Dim mySh As shape

For Each mySh In ActiveSheet.Shapes
If (mySh.Name <> "Count_and_List_Photos") and (mySh.Name =
"RemoveHyperlinks") Then
mySh.Delete
Else
'do nothing
End If

Next mySh

End Sub


"ewan7279" wrote:

> Hi all,
>
> I have embedded two buttons from the control toolbox onto my spreadsheet and
> assigned macros to them. One counts and lists the number and serial of
> photos, the other removes the hyperlinks within them. I would like to write
> code to remove the photos, but leave the buttons. How do I do this please?
> This is what I have so far (which deletes everything!!):
>
> Sub RemovePhotos()
>
> Dim mySh As shape
>
> For Each mySh In ActiveSheet.Shapes
> If Not mySh.Name = "Count_and_List_Photos" Or mySh.Name =
> "RemoveHyperlinks" Then
> mySh.Delete
> Else
> 'do nothing
> End If
>
> Next mySh
>
> End Sub

Thanks Bob - you're a star!!

I'll have to read up on msoOLEControlObject...

"Bob Phillips" wrote:

> Sub RemovePhotos()
> Dim mySh As Shape
>
> For Each mySh In ActiveSheet.Shapes
> If mySh.Type <> msoOLEControlObject Then
> If Not mySh.Name = "Count_and_List_Photos" Or mySh.Name =
> "RemoveHyperlinks" Then
> mySh.Delete
> Else
> 'do nothing
> End If
> End If
> Next mySh
>
> End Sub
>
>
> --
> HTH
>
> Bob
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
> "ewan7279" <(E-Mail Removed)> wrote in message
> news:981EC62A-F450-49B3-8D1E-(E-Mail Removed)...
> > Hi all,
> >
> > I have embedded two buttons from the control toolbox onto my spreadsheet
> > and
> > assigned macros to them. One counts and lists the number and serial of
> > photos, the other removes the hyperlinks within them. I would like to
> > write
> > code to remove the photos, but leave the buttons. How do I do this
> > please?
> > This is what I have so far (which deletes everything!!):
> >
> > Sub RemovePhotos()
> >
> > Dim mySh As shape
> >
> > For Each mySh In ActiveSheet.Shapes
> > If Not mySh.Name = "Count_and_List_Photos" Or mySh.Name =
> > "RemoveHyperlinks" Then
> > mySh.Delete
> > Else
> > 'do nothing
> > End If
> >
> > Next mySh
> >
> > End Sub
>
>
>