VBA UserForm Get Filename

Question

I have constructed a UserForm that consists of 2 CommandButtons, each of which contains a Picture. The user is asked to select one of the two CommandButtons. I would like the filename of the picture selected to be copied to a cell in a worksheet. At the moment I can't figure out how to get the filename of the picture and so I have manually inserted the filename for each CommandButton, as such:

Private Sub cmdQ2Opt1_Click()
    Worksheets("UserQuestionnaire").Range("C4").Value = "242.216.490"
End Sub

Private Sub cmdQ2Opt2_Click()
    Worksheets("UserQuestionnaire").Range("C4").Value = "354.129.401"
End Sub

How can I code this so that VBA automatically copies the picture's filename?

Thanks in advance for the help!

Answer 1

If you assign the picture in the Forms Designer the path is not stored as the image is subsequently loaded from storage within the workbook itself.

To persist the path you would load at runtime:

dim path As string
path = "C:\...\foo.jpg"
set commandbutton1.Picture = loadpicture(path)

You can then store the path in the buttons Tag property:

commandbutton1.Tag = path

Then read it back when its clicked:

ActiveWorkbook.Sheets("UserQuestionnaire").Range("C4").Value = commandbutton1.Tag