Settings?

[Djblois]

Is it possible to create a Settings page for addin's. I have different users using my add-in and some might want Cases (a checkbox) to be active by default. While others might prefer Amt (also a checkbox) to be active by default. Is it possible I can create a settings form where people can set their defaults, and the add-in would save it. I was thinking about programatically adding and removing code. However, the add-in would not save it. Is this possible?

Daniel

[CBrine]

Djblois,
I use GetSetting and SaveSetting to write and extract addin settings from the registry of the local system.
You might want to think about using that. I find it's much better then a .ini file.

HTH
Cal

[Djblois]

Ok can you explain more because I am new to writitng code. I am still just a beginner!

[mdmackillop]

Hi Daniel,
Are these settings on a userform or spreadsheet items?

[Bob Phillips]

[vba]

Function SaveInfo(UserId)
SaveSetting appname:="MyApp", _
section:=UserId, _
key:="ActiveCB", _
setting:="Cases"
End Function

Function GetInfo(UserId)
GetInfo = GetSetting(appname:="MyApp", _
section:=UserId, _
key:="ActiveCB", _
Default:="Cases")
End Function
[/vba]

[Bob Phillips]

This is probably a better example

[vba]

Function SaveInfo(UserId, thiskey, thisVal)
SaveSetting appname:="MyApp", _
section:=UserId, _
key:=thiskey, _
setting:=thisVal
End Function

Function GetInfo(UserId, thiskey, defaultVal)
GetInfo = GetSetting(appname:="MyApp", _
section:=UserId, _
key:=thiskey, _
default:=defaultVal)
End Function

Sub DemoRegistry()
Call SaveInfo("Bob", "ActiveCB", "Cases")

MsgBox GetInfo("Bob", "ActiveCB", "Cases")
End Sub
[/vba]

[Djblois]

I haven't set it up yet. They would be in userforms. I have almost a seperate program built into excel for my users. I am trying to make it as user friendly as possible.

[CBrine]

Djblois,
I use this on a my addin user form.

To load....
[vba]
Private Sub UserForm_Initialize()
'Get Level 1 Settings from Registry
TextBox1 = GetSetting("CSCTool", "AgentSettings", "L1-Extra/Order")
End sub
[/vba]

On button press to update defaults
[vba]
Private Sub CommandButton1_Click()
'Save Level 1 Settings to Registry
SaveSetting "CSCTool", "AgentSettings", "L1-Extra/Order", TextBox1.Value
End Sub
[/vba]

HTH
Cal

[tpoynton]

You could also write values to a hidden sheet (?). I use this method as it enables both mac and PC users to use the same add in (and therefore, I only need to maintain a single file!). Using the registry is simpler, as long as all of your users are windows based!

[Ken Puls]

You could also write values to a hidden sheet (?). I use this method as it enables both mac and PC users to use the same add in (and therefore, I only need to maintain a single file!). Using the registry is simpler, as long as all of your users are windows based!

The only problem with this is that it makes it a real pain to distribute updates since the user is storing their settings within the file. Wherever possible, I'd recommend storing setting info externally.

I tend to use a mix between registry and a database, depending on the scope of the project. If the add-in is going to be used by each user on a single workstation only, and the settings are relatively few, then I'd go registry.

As an alternative, though, I would throw this option to you. My favourites add-in stores all the users favourites in a database that is stored in the same directory as the add-in. (Created on the fly and maintained from the add-in.) The benefits to this is that I can store the add-in on the network, and no matter which machine my users log on to, they can access their own settings. It does take way more code than the registry route, but can reap some significant benefits. Much of the code to work with it can be found on my site, and I'd be happy to point you to it if you'd like to experiment.

Again, it may be way out of scope for what you want to do, but it is something to keep in mind.

Cheers!

[Bob Phillips]

Sounds like the registry to me!

[mdmackillop]

Hi Daniel,
As an alternative, and if it's appropriate, you could also use Custom Document Properties to store settings for individual workbooks.
Regards
MD

[Ken Puls]

Sounds like the registry to me!

In this case, I'd go registry too, but I'm just throwing out the options in case it gets bigger.

Custom Document properties is also a valid method, but holds the same issue as storing in a worksheet. Replace the add-in with an upgraded version, and you lose the info.

[Djblois]

Ken,

I will probably go with the registry but can you send me your site. I am always open to new options.

Daniel

[Ken Puls]

Hi Daniel,

Here's a few links for you...
This one is on using Custom Document properties to store values. It's an alternative to registry keys, but stores the data locally in the workbook.

The next article is more for setup, but is on Creating an Access database (on the fly) using VBA and SQL. You don't need access to do this, as it uses ADO.

To write results to an Access DB, we'd just use a modified version of that routine:
[vba]
Private Sub WriteToDatabase()
'Macro Purpose: Write to an Access database

Dim dbConnectStr As String
Dim cnt As ADODB.Connection
Dim dbPath As String

'Set database name here
dbPath = "C:\" & Application.UserName & ".mdb"
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

'Connect to database and execute SQL clause
Set cnt = New ADODB.Connection
With cnt
.Open dbConnectStr
.Execute "Your SQL Insert Clause Here"
End With
Set cnt = Nothing

End Sub[/vba]

I just realized that I don't actually have a simple routine to pull back data from Access though. I do have this one, but it's a bit more complex, as it is for pulling back multi line results and putting them in an Excel worksheet. Generally, I'd call that down signficantly if I just wanted single records. I have an old version here for you that I used to pulll the date of an invoice from a database. (It's not very efficient, but it will work.)

[vba]
'Constant for Database connection string
Public Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_DBPath & ";"
Public Const glob_DBPath = "F:\Automation\FlashCosts\databasename.mdb"

Function gfunc_GetInvoiceDate(sInvoiceID As String) As Date
'Function Purpose: Return date of an invoice based on the invoice ID

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sSQL As String
Dim rcArray As Variant

'Generate the SQL query
sSQL = "SELECT tblInvoices.InvDate, tblInvoices.InvID " & _
"FROM tblInvoices WHERE tblInvoices.InvID=" & sInvoiceID & ";"

'Open connection to the database
cnt.Open glob_sConnect

'Open recordset
rst.Open sSQL, cnt

'Check if any records have been returned.
If rst.EOF = True Then
gfunc_GetInvoiceDate = 0
Else
'Copy recordset to an array
rcArray = rst.GetRows

'Records have been returned, so return the value
gfunc_GetInvoiceDate = rcArray(0, 0)
End If

' Close ADO objects
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing

End Function[/vba]

HTH,