User-preference Customizations in Personal.xls

[R Avery]

I have a public add-in which I would like to extend to be able to allow each
user to customize its menu. Obviously, I do not want to have a custom
menu-creation script for each user. Rather, I would like to store their
preferences locally, and just apply those prefs each time after the menu is
refreshed. However, I have been having a little trouble deciding exactly
where to store this data.
Also, i would like whatever approach I adopt to be flexible and general
enough to work outside of this particular add-in.

My first thought was to create new sheets in each user's Personal.xls that
can house the preferences. Create a named range, and populate it with data.
Then create a class module that represents a 'preference', and have
userforms act as the bridge between the user and the class.

Then, I discovered CustomProperties of a workbook, from which it is very
easy to add and delete. Therefore, to avoid a host of issues relating to
overlapping preferences and multitudes of worksheets in Personal.xls, I
could store all the data (virtually invisibly to both users and programmers
alike, unless they know where I store it) in the CustomProperties.

I have also read that the RecentFiles collection's data is stored in the
Registry... but I don't think that would be a good idea.


I think my 2nd idea is the best, but I am concerned that there may be
additional issues relating to CustomProperties that I might not be aware of.
Therefore, if anyone has done anything similar, or knows anything that could
go wrong (either from a storage or performance standpoint) with
CustomProperties, I would appreciate it if you shared this knowledge.

Of course, if my whole approach is misguided, and there is a much better way
to do what I am thinking, I would appreciate if someone shared that, as
well.


Thanks!

 

[onedaywhen]

The usual places for user settings are either the Registry (specific
to the current machine and/or user) or an .ini file in your app's
install folder.

 

[R Avery]

My add-in is a public add-in ... it does not reside locally. And I am a
little bit wary of making changes to the Registry (since I know nothing
about it -- unless I can find a thorough tutorial). Also, I may store
hundreds of KB of data... would the Registry still be an appropriate place
for such a thing?

Would creating some sort of dummy folder to only contain a .ini file be a
better solution than the method I suggested?

 

[onedaywhen]

My add-in is a public add-in ... it does not reside locally. And I am a
little bit wary of making changes to the Registry (since I know nothing
about it -- unless I can find a thorough tutorial). Also, I may store
hundreds of KB of data... would the Registry still be an appropriate place
for such a thing?

Would creating some sort of dummy folder to only contain a .ini file be a
better solution than the method I suggested?

If you are persisting individual user settings for multiple users of
the same workbook then the registry would be the place. An ini file
would suffice but its contents are quite 'visible'. However, it's
probably not appropriate to use either the registry or an ini file if
the data amount is large.

If the settings are just for the last user of the workbook then I
think I'd persist them in a range on a (very) hidden worksheet within
the workbook. If I couldn't use a worksheet, I'd go for constant
values in my own defined Names. The CustomProperties are again a bit
too 'visible' for my liking, but it's just a personal preference.

Speaking of which, I don't think I have a Personal.xls (I've never
used one anyhow!) Even if I did, I'm sure I wouldn't want you changing
it.

--

 

[R Avery]

Ok. Thanks for your input!

"R Avery" <[email protected]> wrote in message

If you are persisting individual user settings for multiple users of
the same workbook then the registry would be the place. An ini file
would suffice but its contents are quite 'visible'. However, it's
probably not appropriate to use either the registry or an ini file if
the data amount is large.

If the settings are just for the last user of the workbook then I
think I'd persist them in a range on a (very) hidden worksheet within
the workbook. If I couldn't use a worksheet, I'd go for constant
values in my own defined Names. The CustomProperties are again a bit
too 'visible' for my liking, but it's just a personal preference.

Speaking of which, I don't think I have a Personal.xls (I've never
used one anyhow!) Even if I did, I'm sure I wouldn't want you changing
it.

--