Macro to undo insert image

dcoledc

Active Member
Joined
May 6, 2010
Messages
403
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?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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.
Code:
[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...
Code:
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

Active Member
Joined
May 6, 2010
Messages
403
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
This works for me...
Code:
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

Active Member
Joined
May 6, 2010
Messages
403

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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

Active Member
Joined
May 6, 2010
Messages
403

ADVERTISEMENT

Thanks again!
 

dcoledc

Active Member
Joined
May 6, 2010
Messages
403
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
This will only undo when you have the same sheet selected as the last image added.

Code:
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

Active Member
Joined
May 6, 2010
Messages
403
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,785
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top