Is there an alternative to using LoadPicture ("bmp_or_icon_file_path") to load images in Excel 2007 VBA

Question

I have an Excel 2007 worksheet with many buttons and labels that act as menu options (i.e. the user clicks buttons, labels with images) and is represented by forms or something else.

These images / icons for buttons and labels are loaded into VBA by setting the Image property of the control and calling the LoadPicture () method with the full path to the image file as a parameter, for example, So.

   With SomeFormObject
        .cmdOpenFile.Picture = LoadPicture("F:\projectname\images\fileopen.BMP")
   End With

This method of loading images for buttons, other controls causes 2 problems.

1) It creates a dependency on image files and physical location for each user, therefore, if the user does not have a mapped drive and files present, VBA fails with a runtime error of a file or path that was not found.
2) The application becomes very slow if the images are on a shared disk (this is so)

I want to fix both problems and somehow upload icons, images to the management internally, without any external dependencies on external image files.

What is the best way to achieve this in Excel 2007 VBA?

I could not load any Visual Basic 6.0 / Visual Studio Style "Resource File Editor" / function with which you can accomplish this.

I ask for advice! thank

-Shiva @ mycodetrip.com

Answer 1

, , , :

: , , , , , LoadPicture. VBA, , - Chart.

" Excel" johnske

Option Explicit

Sub setAllPictures()
    setPicture "Picture 18", "CommandButtonOpen"
    setPicture "Picture 3", "CommandButtonClose"
End Sub

Sub setPicture(pictureName As String, commandName As String)
    Dim pictureSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim embeddedPicture As Picture
    Dim pictureChart As Chart

    Dim MyPicture As String
    Dim PicWidth As Long
    Dim PicHeight As Long

    Set pictureSheet = Sheets("NameOfYourPictureSheet") ' <- to Change '
    Set targetSheet = Sheets("NameOfYourSheet")  ' <- to Change '

    Set embeddedPicture = pictureSheet.Shapes(pictureName).OLEFormat.Object
    With embeddedPicture
        MyPicture = .Name
        PicHeight = .ShapeRange.Height
        PicWidth = .ShapeRange.Width
    End With

    Charts.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:=pictureSheet.Name
    Set pictureChart = ActiveChart

    embeddedPicture.Border.LineStyle = 0

    With pictureChart.Parent
          .Width = PicWidth
          .Height = PicHeight
    End With

    With pictureSheet
        .Select
        .Shapes(MyPicture).Copy

        With pictureChart
            .ChartArea.Select
            .Paste
        End With

        .ChartObjects(1).Chart.Export Filename:="temp.jpg", FilterName:="jpg"
    End With

    pictureChart.Parent.Delete
    Application.ScreenUpdating = True

    targetSheet.Shapes(commandName).OLEFormat.Object.Object.Picture = LoadPicture("temp.jpg")

    Set pictureChart = Nothing
    Set embeddedPicture = Nothing
    Set targetSheet = Nothing
    Set pictureSheet = Nothing
End Sub

Sub listPictures()
    ' Helper Function to get the Names of the Picture-Shapes '
    Dim pictureSheet As Worksheet
    Dim sheetShape As Shape

    Set pictureSheet = Sheets("NameOfYourSheet")
    For Each sheetShape In pictureSheet.Shapes
        If Left(sheetShape.Name, 7) = "Picture" Then Debug.Print sheetShape.Name
    Next sheetShape

    Set sheetShape = Nothing
    Set pictureSheet = Nothing
End Sub

: , .

Answer 2

2 , : form.img.picture=pastepicture = oleobjects("ActiveXPictureName").object.picture.