Delete shapes, not buttons
 

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

Delete shapes, not buttons
 

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" wrote in message
...
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

Delete shapes, not buttons
 

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

Delete shapes, not buttons
 

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" wrote in message
...
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