Browse for a file and embed it in a userform to open in the future

[chin67326]

Hi folks,

It's time for the question that has been puzzling Excel VBA userform mankind for ages.

How can one browse for a file and embed it in a userform to open in the future? The file could be a Word, PDF, jpeg or Excel file, whereby one clicks a button, browses for a file, selects and click ok and the file is embedded in a userform and can be accessed and reopened in the future. Is there any method out there that can achieve or closely achieve this?

Thanks!

[lancer102rus]

store full path to your files in the hidden sheet and open them at any time you want

[Leith Ross]

Hello chin67326,

I just completed a project where I needed my UserForm to remember user selections without using a separate file or worksheet.

There are 3 parts to the process. Each embedded value is declared twice: Once as a Variable and again as a Constant. The third piece is a function that modifies the macro code for the constant declaration using the VBIDE.

During the UserForm_Initialize() Event, the Variable is set to the Constant value. Each time the Variable is changed, the function to modify the Constant needs to called to save it for next time.

I will work on creating a workbook to do what you want and post it in this thread for you. I should be able to finish it tonight.


Function to Change a Constant value in the Macro Module's Code
In my case, this macro function changes the constants in the same module the function is in.

1. Be sure to add the Keyword "Public" before your constants.
2. Change the name in blue to the name of your module.
3. This function accepts both String and Long type constants.

Please Login or Register  to view this content.

[Leith Ross]

Hello chin67326,

I just completed a project where I needed my UserForm to remember user selections without using a separate file or worksheet.

There are 3 parts to the process. Each embedded value is declared twice: Once as a Variable and again as a Constant. The third piece is a function that modifies the macro code for the constant declaration using the VBIDE.

During the UserForm_Initialize() Event, the Variable is set to the Constant value. Each time the Variable is changed, the function to modify the Constant needs to called to save it for next time.

I will work on creating a workbook to do what you want and post it in this thread for you. I should be able to finish it tonight.


Function to Change a Constant value in the Macro Module's Code
In my case, this macro function changes the constants in the same module the function is in.

1. Be sure to add the Keyword "Public" before your constants.
2. Change the name in blue to the name of your module.
3. This function accepts both String and Long type constants.

Please Login or Register  to view this content.

[chin67326]

Thanks a lot Mr Ross; I look forward to seeing the workbook. I am excited!

[Leith Ross]

Hello chin67326,

Here is the workbook as promised. Let me know what you think about it.

[chin67326]

Hi Leith,

First of all I want to thank you for this and second of all, GREAT JOB!!!! Do you think there is an opportunity for this to be developed further to perhaps be able to browse and add more files to a list box for example? Or perhaps one can use several occurrences of the instance if he wants to embed several files? Get some sleep as I don't want you going off to work on this further idea, but it could be a future project. Either way, this is awesome with what I have in mind and I will focus on integrating it into a project I am working on. I appreciate it; good job and thanks!