Allowing users to change an Add-In's Form Defaults

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
I have an add-in that i distribute to other users who can't see/view the code.

The add-in puts a button on their toolbar that when clicked calls a form which has text boxes (filled in with default values) and pre-selected radio buttons.

Without allowing users to view/change code, how can i allow them to change the default values when the form is called?

Calling the form, changing the selections, and clicking SAVE won't work as the macro would be 'in use' and therefore not able to be saved.

Is the best method (if it's even possible) to create a separate file which contains a macro that will open the add-in file, change the default vaues, and then save the add-in? Can this even be done? Are there any other ways/suggestions?

Thank you.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi t,

Your best bet would probably be to store the information in a text file. You then open the text file to get the details to add in the userform and, if appropriate, save any new details to the text file.

Give us a little more detail if you want some help implementing this.

HTH
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
Thank you for responding.

It sounds like you're saying i could have a text file and have the program read the text file for default values? Then users could change the text file if they want to change the default values?

Yes, i'd like more details!!

What add'l info do you need to know to get me started?
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi t,

OK, a basic example follows. The example assumes:

1. A userform called UserForm1.
2. Three textboxes called TextBox1, TextBox2, and TextBox3 respectively.
3. One commandbutton called CommandButton1.

In the UserForm1 object paste in the following code:
Code:
Const strTextFName As String = "C:\My Documents\TextTest.txt"
'the name and location of the text file, amend to suit

Private Sub CommandButton1_Click()
'open the text file, write the data from the textboxes, close the text file
    Dim iFreeNo As Integer
    
    iFreeNo = FreeFile()
    Open strTextFName For Output As #iFreeNo
    Print #iFreeNo, Me.TextBox1.Text
    Print #iFreeNo, Me.TextBox2.Text
    Print #iFreeNo, Me.TextBox3.Text
    Close #iFreeNo

End Sub

Private Sub UserForm_Initialize()
'if it exists, get textbox values from the text file
    Dim iFreeNo As Integer, i As Integer, strText As String
    
    If Not Dir(strTextFName) = "" Then
        iFreeNo = FreeFile()
        Open strTextFName For Input As #iFreeNo
        For i = 1 To 3
            Line Input #iFreeNo, strText
            Me.Controls("TextBox" & i).Text = strText
        Next i
        Close #iFreeNo
    End If

End Sub
If you have trouble implementing the basic idea then post some details regarding the structure of your userform, number of textboxes, what is used to trigger the saving of the data etc.

HTH
 

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,833
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top