Macro to undo insert image

[dcoledc]

Hello, I am very new to this and to VBA writing. Up to this point I have been recording macros and then searching for code to change what I recorded to do what I want it to do. I tell you this to let you know, I know very little but am enjoying the learning process.

I got an insert image macro from this site that allows me to choose an image and insert it by hitting the button. What I need is a macro that will undo the insert. I recorded a macro and then altered it but it ends up deleting the button once it has deleted the image.

Here is the code:
Sub Undo()
'
' Undo Macro
' Macro recorded 05/06/2010 by Dr. Daniel T. Cole
'
ActiveSheet.Unprotect
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select Undo
Selection.Delete
ActiveSheet.Protect
'
End Sub

Any help?

 

[AlphaFrog]

Try something like this...

Define a persistent variable at the top (above all your macros) in what is called the General Declarations section e.g.

[COLOR="Red"]Dim p as Object[/COLOR]

 Sub Add_Picture()
   'do stuff
 End Sub

 Sub Undo()
   'do stuff
 End Sub

Then in your macro that adds the images, there is some line that does the actual adding of the image. It might look something like
ActiveSheet.Pictures.Insert(PictureFileName)

and change it to...
Set p = ActiveSheet.Pictures.Insert(PictureFileName)

Your actual code will probably look different, but hopefully you get the idea. This assigns the inserted picture to a variable that you use in your Undo macro.

Then your Undo macro would be something like this...

Sub Undo()
'
' Undo Macro
' Macro recorded 05/06/2010 by Dr. Daniel T. Cole
'
    ActiveSheet.Unprotect
        If Not p Is Nothing Then p.Delete
    ActiveSheet.Protect
'
End Sub

 

[dcoledc]

Thanks for the help, though I have yet to get it to work. Again please keep in mind I am new to this. When you say general declarations, I take that to mean go to the top of the macro box and in the upper right hand corner change from Undo to declarations. Is that right and do I put
Dim p as object in the declarations of both my Undo macro and my insert picture macro?

My insert macro looks like this:
Dim p As Object
Sub Posture()
'
' Posture Macro
' Macro recorded 04/20/2010 by Dr. Daniel T. Cole
ActiveSheet.Unprotect
Dim myPicture As String

myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")

If myPicture = "False" Then Exit Sub

Set p = ActiveSheet.Pictures.Insert(myPicture)
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select

I did not write this code. I got it off this site from another thread. I tell you that only to let you know I have yet to completely figure it all out.

Anyway, adding the Dim P as Object had no negative affect on the insert macro, but when I added the suggest code, If Not p Is Nothing Than p.Delete, I get a runtime error 424. When debug it highlights the above code.

Any more thoughts?

 

[AlphaFrog]

This works for me...

Dim p As Object

Sub Posture()
    '
    ' Posture Macro
    ' Macro recorded 04/20/2010 by Dr. Daniel T. Cole
    ActiveSheet.Unprotect
    Dim myPicture As String
    
    myPicture = Application.GetOpenFilename _
    ("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")
    
    If myPicture = "False" Then Exit Sub
    
    Set p = ActiveSheet.Pictures.Insert(myPicture)
    ActiveSheet.Protect

End Sub

Sub Undo()
'
' Undo Macro
' Macro recorded 05/06/2010 by Dr. Daniel T. Cole
'
    ActiveSheet.Unprotect
        If Not p Is Nothing Then
            p.Delete
            Set p = Nothing
        End If
    ActiveSheet.Protect
'
End Sub

 

[dcoledc]

Okay that worked, but what I had to do was copy the undo macro and place it in the same module as the posture macro. I haven't quite got the hang of what modules are. As I said before, up until know I have recorded my macros and then altered the code until it did what I wanted. When I recorded them, little did I know they were being created in various modules. To complicate matters the workbook has 8 worksheets.

Any recommended reading or sites you recommend for understanding the basics like modules and how macros are placed in them?

If not, thanks anyway for your help.

It works! Yippee!

 

[AlphaFrog]

I'm glad it works.

Given the way we defined the variable "p", the two macros have to be in he same Module. If we had used...
Public p as Object
Then you could have the two macros in different modules.

Here's a good discussion of Modules
Code Module And Code Names

When I need VBA help or info., I usually just do a web search e.g "Excel VBA Modules". This usually gets me a quick answer. If you are a VBA novice, you may want to take a look at the book "Excel 2007 VBA Programming for Dummies". This isn't intended as an insult. It is actually a good starter reference.

 

[dcoledc]

Thanks again!

 

[dcoledc]

I have run into an error regarding the undo insert image. I have a workbook with several sheets all with the insert macro and undo insert macro. If I insert an image on one page then move on to the next sheet and add an image then go back to undo the image on the first sheet, I get a message saying the sheet needs to be unprotected to do this. My concern is not that I actually want to undo the image on the first sheet but rather if the undo macro is accidentally hit I don't want the error. Do I need to set up a different variable for each sheet so the macro doesn't get confused or is there some way to exit the macro without leading to the error?

Thanks, Dan

 

[AlphaFrog]

This will only undo when you have the same sheet selected as the last image added.

Sub Undo()
'
' Undo Macro
' Macro recorded 05/06/2010 by Dr. Daniel T. Cole
'
    If Not p Is Nothing Then
        If ActiveSheet.Name = p.Parent.Name Then
            ActiveSheet.Unprotect
                    p.Delete
                    Set p = Nothing
            ActiveSheet.Protect
        Else
            MsgBox "The last image was added on a different sheet.", vbOKOnly, "Sheet Changed"
        End If
    End If
'
End Sub

 

[dcoledc]

Thanks yet again. Not only am I new to VBA but new to utilizing forums as well. Please let me know if I am abusing the service.

Thanks, Dan