Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

deleting picture from userform using code

hello again,

guys, is there any way how to use a code to delete a picture from userform but to maintain the picture object itself? I am not sure if I expresed myself clear... I have a code, that creates a chart, saves this chart as a picture and than displays it in the userform. and every time I want to run the code with different parameters I would like this old chart to be removed from the picture box.
I tried any possible combinations like "userform1.image1.delete" etc, but no success

than you very much


Post your answer or comment

comments powered by Disqus
Hi everyone,
Can anyone tell me if it is possible and how I could create a userform using
code. My idea is that I use an inputbox to find out how many textboxes the
user wants in their userform and then the userform is created. Any help would
be appreciated.
Thanks
Trini

is there a simple VB code that will remove all pictures from my file? i am currently using the following command to delete picture 2 - but i'm not always sure the picture will be named "pictured 2"....

current code:
ActiveSheet.Shapes("Picture 2").Select
Selection.Delete

thanks!

I have written a visual basic module to insert pictures from a list
contained in a worksheet. I have figured out how to delete the picture
when I load the next picture. For this I need to know the picture
number.

My problem is the indexing or the picture number. The number of shapes
ActiveSheet.Shapes.Count doesn't change But the picture number does
increment. I am using workaround by updating the picture number in a
cell on the active sheet BUT this is a kluge! More importantly, I will
be using this to display a large number of images [1000s 100000s]. I
don't know wheter there is an upper limit, but it would be cleaner if
the pic number always reset.

I am using Excel 2004 for the MAC [OSX]

Thanks

Brujolito

Hi,

I have a program that uses a userform to display data, on this form i
select a row on a listbox on the form and i use code to remove that
row.

if there any good code that will remove a row from a listbox, i have
the code for selecting the required row, and do i have to unload the
form so i can see the changes to the listbox within the userform
John

I need a basic macro that deletes all and any pictures from the current worksheet, where ever the picture be in that worksheet...
Thanks

Hi guys,

Is there a VBA code for copying all pictures from a certain folder & inserting them into a excel file then resizing them to 5cm square & assigning this to a button that when you press it, it does the above?

Thanks Peter

Hi all, I have a userform which takes a large amount of sequential geographical data and builds a theoretical model of my layout whithin excel. However I would now like to incorperate an interactive aspect to this userform. I have written code which draws a line diagram whithin a seperate CAD app and imports it as a picture to the excel userform each time the user updates the data, but I would like to be able to select different areas whithin the data by clicking in the appropriate place on the picture in the form. I know this kind of interactivity is a bit beyond visual basics line of duty but my model MUST stay whithin the confines of excel thereforwe my front end must remain VBA for the time being.

My other option is to build up a diagram out of smaller components (boxes etc) whithin the userform. This would be entirely acceptable, however I'm not too sure if its possible to 'create' new pictures on the form from whithin the code. Any help on this would be much appreciated as I'd HATE to prebuild a universal diagram and use the object.visible property to 'unhide' them as I need them, the system is a bit more bespoke than that. TIA anyone, Sam

is this even VBA possible. I have a Userform that I type the pictures name the Jpeg picture pops up in the userform.

Is there a code that it will extract pictures from other worksheets. I Have pictures in Microsoft Publisher I would like to type the file name and have the pictures pop up in the Userform. Is this possible ? or anything comes closes to it.


	VB:
	
 CommandButton1_Click() 
    Application.ScreenUpdating = False 
    ffname = PicPath & tbxpartnr & "-" & myop & ".jpg" 
    On Error Resume Next 
    Image1.Picture = LoadPicture("C:MyPicture" & tbxpartnr & ".jpg") 
    If Err = "53" Then 
        MsgBox "Could not find :  " 
    End If 
     'Next i
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi.

I want to delete a picture from an image control in a worksheet when pressing a button.
So my code is:


	VB:
	
 CommandButton1_Click() 
     
    Sheets("Sheet1").Image1.Picture = LoadPicture("") 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Well, the problem is that this code only works when I create it. If I save and close the excel file when I open it again and I press the CommandButton1 I get the following error:

Run-time error '-2147417848(80010108)'
Method "Picture of object "IImage" failed

Does anyone have a precious idea of of how to solve this problem?

Thank you in advance!

I have a picture in a userform that I need to be able to dynamically update at time of execution. I have been doing this using the LoadPicture function, but I don't like the idea of having to rely on external files for the operation of my macro. I'd prefer to put the pictures I need into one of the sheets in the workbook and then access the appropriate picture from that worksheet. I've been able to successfully retrieve a "picture" object from the worksheet in question with the correct picture, but the user form doesn't accept a picture object. It's looking for an IPictureDisp object instead. Is there a way to convert a picture object to an IPictureDisp object or any other way to update an image on a userform dynamically based on a picture within the workbook?

I need code (attahced to a CommandButton) to delete any/all pictures on a worksheet.
The pictures will be placed through the Insert - Picture from file method.
Thanks!

I'm using the code below to rename some pictures and then delete them. I'm trying to delete only the images that are tagged with pictures. I do not want to delete other images such as wordart, macro buttons, etc.

The renaming part of the code works well.

The delete code does what it is supposed to do for about the 1st half of the pictures and then I get the following error:

Run-time Error
"The index into the specified collection is out of bounds."

The code is as follows:

Sub PictureRename()
'
Dim iLoop As Integer
Dim iCtr As Integer
Dim iDel As Integer

Set shtActive = ActiveSheet

' Renames the pictures in sequential order starting from 1
iCtr = 1
For iLoop = 1 To ActiveSheet.Shapes.Count
If Left(ActiveSheet.Shapes.Item(iLoop).Name, 7) = "Picture" Then
ActiveSheet.Shapes.Item(iLoop).Name = "Picture " + Str(iCtr)
iCtr = iCtr + 1
End If
Next iLoop

' Deletes each picture from 1 to 100
'On Error Resume Next
iDel = 1
For iDel = 1 To 100 Step 1
If Left(ActiveSheet.Shapes.Item(iDel).Name, 7) = "Picture" Then
ActiveSheet.Shapes.Item(iDel).Delete
End If
Next iDel

End Sub


The debug function breaks on the following line:

    If Left(ActiveSheet.Shapes.Item(iDel).Name, 7) = "Picture" Then
ActiveSheet.Shapes.Item(iDel).Delete


Any suggestions?

PS. I actually don't need to rename the pictures so if there is someway to remove this part of the code that would be great. I found the code to rename and changed to the delete part of the code and had the same problem. I thought this might be because the pictures were out of sequential order and added the rename part back in. However, the same error occurs in both cases.

Ive now got a userform with a combo box that selects data from column A.

Using the code below:-
If any of the columns contain an empty cell then I DO NOT wish them to be part of the range i.e.

How can I delete the rows where a blank cell is present in any of the columns for row x from the range??

Code:
Dim patientList As Range
Set patientList = Sheets("Data").Range("A:A,D:D,E:E,F:F,G:G").End(xlDown)
'patientList.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Me.comboPatient.RowSource = patientList.Columns(1).Address
The code (when the delete command is commented back into play) displays the following error:-

Error message '1004' Cannot use that command on overlapping sections appears with the code above.

Can anyone help with a way forward ?

Cheers
Rob

Hello,
I have been scouring the excel websites looking for a way to retrieve pictures (jpgs) from a folder to an excel spreadsheet. Here is the catch I will be using an sql query to get my list of photos. I have seen some cool ones that will get be a In column A will be a list of items that needs a photo and column B will be the photos. The concern is that each time I run the query, photos will have to be removed and replaced. So I will want an add picture macro and delete picture macro. The macro below looks great but I can't get it to work.

Code:
Sub PitureFill()
End Sub

Function ShowPicD(PicFile As String) As Boolean
     'Same as ShowPic except  deletes previous picture when picfile  changes
     'Created by Damon Ostrandor
    Dim AC As Range
    Static P As Shape
    On Error GoTo Done
    Set AC = Application.Caller
    If PicExists(P) Then
        P.Delete
    Else
         'look for a picture already over cell
        For Each P In ActiveSheet.Shapes
            If P.Type = msoLinkedPicture Then
                If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
                    If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
                        P.Delete
                        Exit For
                    End If
                End If
            End If
        Next P
    End If
    Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
    ShowPicD = True
    Exit Function
Done:
    ShowPicD = False
End Function
Sub PitureRemove()
End Sub
Function PicExists(P As Shape) As Boolean
     'Return true if P references an existing shape
    Dim ShapeName As String
    On Error GoTo NoPic
    If P Is Nothing Then GoTo NoPic
    ShapeName = P.Name
    PicExists = True
NoPic:
    PicExists = False
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
The next bit of advice for the above was
"Now try in a cell
=ShowPicD("Your picture path here"& a1 & ".jpg")
where a1 houses the picture/file name"

Well I did put the picture name in A1 and in B1 I put a reference to the picture path but how do I get "ShowPicD" to work? Is it okay to have it "ShowPicD..."or do you really need the "=".

Thanks for anybody that can help!!!

I'm working at a Marina and trying to make a comparison chart for different
types of boats. I am trying to make a list, and based upon the users
selection of a boat, insert a corresponding picture to match the boat
selected from that list. I've researched other thread discussions, and tried
to approach it this way, as reccommended by Ron Coder

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture, put it in the sheet and
resize it).
1b. Select the range of cells that contains the picture.
1c. Name that range of cells, using the prefix "pic" followed by the
dropdown list text:
Example for a picture of an Elephant on cells A210:
Select those cells
Insert>Name>Define
Name: picElephant

2)Build your data validation list on a cell in Sheet1 and pick one of the
items.
If you need help: use Debra Dalgleish's site:
http://www.contextures.com/xlDataVal01.html

3)Create a dynamic range name that refers to that cell:
Insert>Name>Define
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
....or whatever cell you chose.

4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.

5)With picture selected, type this in the formula bar, then press [Enter]:
=ShowMyPic

After trying this method, instead of getting the picture inserted, I get the
name of the cells that the picture is in...(the name I gave the cells that
contained the picture). It does change according to my selection from the
list, but doesnt display the picture. The text that is returned instead of
the picture seems distorted as well. Any ideas of what I could be doing
wrong? I've also tried making this work through macros which I am even
less familiar with. I was able to get that to work for one selection, but I
dont know how to properly code it to work for every boat in my list. I used
this code reccommended by Bernie Deitrick:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Address "$A$1" Then Exit Sub
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
Range("B9").Select
ActiveSheet.Pictures.Insert( _
"C:Documents and SettingsPHILMy DocumentsMy
Picturestest.jpg").Select
Selection.Name = "PictureName"
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error Resume Next
ActiveSheet.Shapes("PictureName").Delete
End If
End Sub

I assume I would need to somehow nest this or create If - Else commands?
Also to clarify, I did change the file names and paths to those that matched
my project. It worked for one selection, but I don't know how to code it for
a list of say 40 boats

Any suggestions of the easiest way to go about doing this? Ive been trying
for a few days now, and could use any help I can get!

I am able to work out a code to upload pictures from my files using this:


	VB:
	
 
 
 
Private Sub cmdfind_Click() 
    Application.ScreenUpdating = False 
    Dim strfind As String 
    Dim rsearch As Range 
    Dim strfolder As String 
    Dim strname As String 
    Dim strpic As String 
    Dim b As Range 
     
    Set rsearch = Worksheets("m").Range("ap3", Range("ap65536").End(xlUp)) 
     
     'define path to images
    strfolder = "F:SEC FILESMAC2PIC" 
     
     'get data from userform
    strname = frmsearch.txt201.Value 
     
     'check if pic exists
    strpic = strfolder & strname & ".jpg" 
     
     'lets do some validation
     
    With rsearch 
         
        Set b = .Find(strname) 
        On Error Resume Next 
        If b Is Nothing Then 
            Image1.Picture = LoadPicture(strpic) 
             
            On Error Goto 0 
             
             
        End If 
    End With 
     
     
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do i work around this code now to help me avoid the 'File Not Found' error if the picture is not available in my file folder?

Any advice would be appreciated.

Hi All,

Can anyone please help me out with the correct method of passing values from a UserForm to the Module (written codes)?

I just want to load the UserForm & run the codes via using shortcut keys (Ctrl + k), so this methodology can give me a freedom to run the codes on any worksheet, which is active.

Please see my codes and help me:-
I am unable to populate all the worksheet names in a list box by UserForm_Initialize.
I am unable to pass values from UserForm to the module. I Just want to setect anyone of them in the ListBox, so that the Module codes {Variance_Finder} should be run on that paticular worksheet

I tried with a TextBox too instead of the above ListBox. It shows me the ActiveWorksheet Name, where I want to run my codes via shortcut keys (Ctrl + k), but the appeared worksheet name doesn't passes from UserForm to the Module, so the codes do not work at all.

Please help to have a perfect tuning between the UserForm and Module Codes.

Thanks in advance!

Please see below, is the attached sample in MS Excel 2007:

I am using Excel 97. I have grouped a number of shapes together on a
worksheet so that I can copy them to a powerpoint slide using the code
below that I have found on the web. The only trouble is when it is
pasted in the format isn't correct so I need it to be pasted in to the
powerpoint with a PASTE SPECIAL as PICTURE command. Can anyone assist
me with a solution, please?

Here's the Code:
****************

Sub Create_PowerPoint_Presentation()
' Add a reference to the PowerPoint-library
Dim pptApp As PowerPoint.Application
Dim pptPres As PowerPoint.Presentation
Dim pptSlide As PowerPoint.Slide
Dim pptShape As PowerPoint.Shape
Dim i As Integer, strString As String

Set pptApp = CreateObject("PowerPoint.Application")
Set pptPres = pptApp.Presentations.Add(msoTrue) ' create a new
presentation
' or open an existing presentation
' Set pptPres =
pptApp.Presentations.Open("C:FoldernameFilename.ppt")

' apply a slide template
pptPres.ApplyTemplate "C:Program FilesMicrosoft
OfficeTemplatesPresentation DesignsWatermark.pot"

Sheets("TitlePage").Shapes("Slide1").Copy ' copy a picture from
Excel

With pptPres.Slides
Set pptSlide = .Add(.Count + 1, ppLayoutText) ' add a slide
End With

With pptSlide

.Shapes(1).Delete ' remove title
'.Shapes(2).Delete ' remove the text box
.Shapes.PasteSpecial ' I need something here!

With .Shapes(.Shapes.Count)
.Left = 0
.Top = 0
.Width = 720
.Height = 540
End With

...............

Thanks in advance

Mark

I'm working at a Marina and trying to make a comparison chart for different
types of boats. I am trying to make a list, and based upon the users
selection of a boat, insert a corresponding picture to match the boat
selected from that list. I've researched other thread discussions, and tried
to approach it this way, as reccommended by Ron Coderre:

Select Sheet2 and turn off Grid Lines
(Tools>Options>View tab:Uncheck Grid Lines)
1)For each picture to be displayed:
1a. Insert>Picture from file. (select picture, put it in the sheet and
resize it).
1b. Select the range of cells that contains the picture.
1c. Name that range of cells, using the prefix "pic" followed by the
dropdown list text:
Example for a picture of an Elephant on cells A2:D10:
Select those cells
Insert>Name>Define
Name: picElephant

2)Build your data validation list on a cell in Sheet1 and pick one of the
items.
If you need help: use Debra Dalgleish's site:
http://www.contextures.com/xlDataVal01.html

3)Create a dynamic range name that refers to that cell:
Insert>Name>Define
Name: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
....or whatever cell you chose.

4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.

5)With picture selected, type this in the formula bar, then press [Enter]:
=ShowMyPic

After trying this method, instead of getting the picture inserted, I get the
name of the cells that the picture is in...(the name I gave the cells that
contained the picture). It does change according to my selection from the
list, but doesnt display the picture. The text that is returned instead of
the picture seems distorted as well. Any ideas of what I could be doing
wrong? I've also tried making this work through macros which I am even
less familiar with. I was able to get that to work for one selection, but I
dont know how to properly code it to work for every boat in my list. I used
this code reccommended by Bernie Deitrick:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
Range("B9").Select
ActiveSheet.Pictures.Insert( _
"C:Documents and SettingsPHILMy DocumentsMy
Picturestest.jpg").Select
Selection.Name = "PictureName"
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error Resume Next
ActiveSheet.Shapes("PictureName").Delete
End If
End Sub

I assume I would need to somehow nest this or create If - Else commands?
Also to clarify, I did change the file names and paths to those that matched
my project. It worked for one selection, but I don't know how to code it for
a list of say 40 boats

Any suggestions of the easiest way to go about doing this? Ive been trying
for a few days now, and could use any help I can get!

Hello!

I have a problem here..

I'm trying to create a UserForm using VBA
I currently have:
1) Worksheet that have a list of names in it
2) A folder of photos.. Pic1.jpg, pic2.jpg

I'm trying create a userform that would have a list and an area for the corresponding picture.

---------------------------------
| Name Photo |
| |
| |
|________________________ |

I've already created the list using combo box and sourced it to all the names.
But how do i show the photo? And also, how do i insert the photo into a cell if need be?

Like when i select

---------------------------------
| Name Photo |
| Person 1 Pic1.jpg |
| |
|________________________ |

---------------------------------
| Name Photo |
| Person 2 Pic2.jpg |
| |
|________________________ |

THANK YOU! =)

Hi All,

I've used the following code to export macros from one workbook to another but I was wondering how you export macros from the 'Sheet' code module to the 'Sheet' code module of another workbook.


	VB:
	
 Import_Macro() 
     
    With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule 
         
        strRet = .Lines(1, .CountOfLines) 
         
    End With 
     
     
    Set wkb = Workbooks("Test Workbook.xls") 
    wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosGeneral.bas") 
    wkb.VBProject.VBComponents.Import ("G:SCSSCSALLReportsVB MacrosMJ Selections.bas") 
     
    With wkb.VBProject.VBComponents("ThisWorkbook").CodeModule 
         
        .DeleteLines 1, .CountOfLines '//Delete Codes already wrriten
        .InsertLines 1, strRet '// Write Code
         
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated as always.

I have some code to insert a picture based on the value of a cell. The user would select the name of the item by selecting the cell and then clicking a macro button.

In my code I need to delete the previous picture first. I used the following code:


	VB:
	
 get_system_picture() 
     
    Dim pic As Shape 
    Dim picname As String 
     
     
    picname = ActiveCell.Value 
    On Error Resume Next 
     
     'exit sub if selection is not a system number
    If IsEmpty(ActiveCell) Then 
        MsgBox "Please select a system number first!" 
        Exit Sub 
    End If 
     
    If Not ActiveCell.Column = 2 Then 
        MsgBox "Please select a system number first!" 
        Exit Sub 
    End If 
     
     'delete existing picture
    For Each pic In ActiveSheet.Shapes 
        pic.Delete 
    Next pic 
     
     'insert new picture based on selected cell
    ActiveSheet.Range("J8").Select 
    With Selection 
        .Pictures.Insert( _ 
        "C:MyFilesproductsdwgs" & picname & ".jpg").Select 
    End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is this also deletes my macro button! Any suggestions?

I have inserted pictures to a spreadsheet report and wish to remove them using a macro assigned to a command button. I recorded the macro below but it does not work. When the macro is run a message is returned that says "The item with the specified name can't be found."


	VB:
	
 Remove_Pictures() 
     '
     ' Remove_Pictures Macro
     ' Macro recorded 11/17/2006 by
     '
     '
    ActiveSheet.Shapes("Picture 33").Select 
    Selection.Delete 
    ActiveSheet.Shapes("Picture 34").Select 
    Selection.Delete 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When recording the macro I just clicked on the image to select it and hit the delete key.

Can you help?

Dickiebird

Hi....I have a worksheet with 10 columns with headings from A1:J1.
I have just learnt how to colour the rows that have values in column J and that leaves the rows with blank cells in column J non-coloured.

I am trying to figure out how I could give users the print option from userform i.e.
Option 1 : To print Entire List,
Option 2 : To print list with coloured rows (the records with values in column J),
Option 3: To print list with all non-coloured rows.....but should only include rows that are used...i.e have a value in column A.

I am hoping to achieve this via userform either by using combobox with these 3 options in the list or checkboxes or something else anyone can suggest that will be user friendly....

I already have a code for Option 1. I am pasting that below:


	VB:
	
 cmdPrint_Click() 
    Dim LastRow As Long, ws As Worksheet 
     
    Set ws = Sheets("ComplaintData") 
     
    Application.ScreenUpdating = False 
    With ws 
        .Visible = xlSheetVisible 
        LastRow = .Range("A" & Rows.Count).End(xlUp).Offset(2).Row 
        .PageSetup.PrintArea = "A1:J" & LastRow 
    End With 
     
    With ws.PageSetup 
        .PrintTitleRows = "$1:$1" 
        .PrintTitleColumns = "" 
    End With 
     
     
    With ws.PageSetup 
        .CenterHorizontally = True 
        .Orientation = xlLandscape 
        .Draft = False 
        .FitToPagesWide = 1 
         
    End With 
     
    With Application 
        .Dialogs(xlDialogPrinterSetup).Show 
        ws.PrintOut 
        ws.Visible = True 
        .ScreenUpdating = True 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can someone help me with this? I tried to look at Autofilter option.....but struggling to incorporate that with my userform...Cheers


No luck finding an answer? You could always try Google.