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

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

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.

Function ChangeConst(ByVal Const_Name As String, ByVal Const_Value As Variant) As Boolean

  ' This changes the value of a Const in the module CalendarAPI.
  ' This allows a value to be saved and loaded as the default
  ' value the next the frmCalendar is displayed, like the date cell.
  
    Dim Line As Long
    Dim What As String
            
            
        What = "Public Const " & Const_Name
        
        With ThisWorkbook.VBProject.VBComponents("CalendarAPI").CodeModule
            For Line = 1 To .CountOfLines
              ' This prevents VBA from hanging up if the constant is not found.
                DoEvents
                If .Find(What, Line, 1, Line, -1) = True Then
                    Select Case VarType(Const_Value)
                        Case vbString
                            .ReplaceLine Line, What & vbTab & "As String = """ & Const_Value & """"
                            ChangeConst = True
                            Exit For
                        Case vbInteger, vbLong
                            .ReplaceLine Line, What & vbTab & "As Long = " & Const_Value
                            ChangeConst = True
                            Exit For
                    End Select
                End If
            Next Line
        End With
        
End Function

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.

Function ChangeConst(ByVal Const_Name As String, ByVal Const_Value As Variant) As Boolean

  ' This changes the value of a Const in the module CalendarAPI.
  ' This allows a value to be saved and loaded as the default
  ' value the next the frmCalendar is displayed, like the date cell.
  
    Dim Line As Long
    Dim What As String
            
            
        What = "Public Const " & Const_Name
        
        With ThisWorkbook.VBProject.VBComponents("CalendarAPI").CodeModule
            For Line = 1 To .CountOfLines
              ' This prevents VBA from hanging up if the constant is not found.
                DoEvents
                If .Find(What, Line, 1, Line, -1) = True Then
                    Select Case VarType(Const_Value)
                        Case vbString
                            .ReplaceLine Line, What & vbTab & "As String = """ & Const_Value & """"
                            ChangeConst = True
                            Exit For
                        Case vbInteger, vbLong
                            .ReplaceLine Line, What & vbTab & "As Long = " & Const_Value
                            ChangeConst = True
                            Exit For
                    End Select
                End If
            Next Line
        End With
        
End Function

Hello chin67326,

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