none
Picture won't stick in footer when chosen with Application.GetOpenFilename RRS feed

  • Question

  • Hi

    I can't get the code below to work. Sometimes it does, and sometimes it doesn't. The purpose is to give the user the right to change the picture in the center footer by changing the file path.

     Sub FindLogo()

     ActiveSheet.PageSetup.CenterFooterPicture.Filename = Application.GetOpenFilename("", 1, "Choose a picture", , False)
          
    End Sub

    any suggestions?

    cha59

    Wednesday, May 6, 2015 7:34 PM

Answers

  • You'll also need to assign "&G" to the CenterFooter property to enable the image to show up in the center footer...

    ActiveSheet.PageSetup.CenterFooter = "&G"

    Also, I'd suggest adding a file filter when prompting to select a picture.  So maybe something like this...

    Sub FindLogo()

        Dim vFileName As Variant
        
        vFileName = Application.GetOpenFilename( _
            "Pictures (*.gif;*.jpg;*.png), *.gif;*.jpg;*.png", , "Choose a picture", , False)
        
        If vFileName = False Then
            MsgBox "User cancelled...", vbInformation
            Exit Sub
        End If
        
        With ActiveSheet.PageSetup
            .CenterFooterPicture.Filename = vFileName
            .CenterFooter = "&G"
        End With
        
    End Sub

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    • Edited by Domenic Tamburino Thursday, May 7, 2015 3:08 AM
    • Marked as answer by cha59 Thursday, May 7, 2015 6:48 PM
    Thursday, May 7, 2015 3:05 AM

All replies

  • You'll also need to assign "&G" to the CenterFooter property to enable the image to show up in the center footer...

    ActiveSheet.PageSetup.CenterFooter = "&G"

    Also, I'd suggest adding a file filter when prompting to select a picture.  So maybe something like this...

    Sub FindLogo()

        Dim vFileName As Variant
        
        vFileName = Application.GetOpenFilename( _
            "Pictures (*.gif;*.jpg;*.png), *.gif;*.jpg;*.png", , "Choose a picture", , False)
        
        If vFileName = False Then
            MsgBox "User cancelled...", vbInformation
            Exit Sub
        End If
        
        With ActiveSheet.PageSetup
            .CenterFooterPicture.Filename = vFileName
            .CenterFooter = "&G"
        End With
        
    End Sub

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    • Edited by Domenic Tamburino Thursday, May 7, 2015 3:08 AM
    • Marked as answer by cha59 Thursday, May 7, 2015 6:48 PM
    Thursday, May 7, 2015 3:05 AM
  • Thanks Domenic

    Your filter helps, but my main problem is still, that I cannot change the picture everytime. It seems to stick to the old picture although I justed choosed a new picture.

    But thank you for your help

    best regards

    cha59

    Thursday, May 7, 2015 3:22 PM
  • Interesing...  I've tested it and a new picture is inserted into the center footer each time I run the code and select a new one.

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Thursday, May 7, 2015 4:21 PM
  • Hi Domenic

    It works. My mistake. It was a question of which sheet was active sheet. Hurra.

    Thanks a lot again

    Best regards

    cha59

    Thursday, May 7, 2015 6:48 PM