PDA

View Full Version : Solved: Setting using Personal workbook to store settings



Djblois
04-24-2007, 06:30 AM
I am trying to use everyone's personal workbook to save settings since it is a hidden workbook and it starts with excel. I know I have to Create it on their computer initially but it is worth it. I am trying to test it to see if would work, I am trying to read from it but it isn't working. I am getting an error:

If Workbooks("Personal").Range("D1").Value = "Yes" Then Cases.Value = True
If Workbooks("Personal").Range("D2").Value = "Yes" Then Cases.Value = True

Charlize
04-24-2007, 07:06 AM
Maybe insert the sheet reference before the range ???
Workbooks("Personal").Sheets(1).Range("D1").Value = "Yes"
Could be that you need .xls after Personal because it is already saved. In Dutch it is Persnlk.xls to use.

Charlize

mdmackillop
04-24-2007, 08:12 AM
Will you need to change these setting? If so, having them stored in Personal could be a nuisance as you can't just overwrite it. Why not use a txt file or an xls file stored on the server. You could use Environ("UserName") to access the correct settings.

Djblois
04-24-2007, 08:25 AM
Yes. My users should be able to change their settings. Why can't you overwrite an option in the personal workbook? Also, if I use a seperate xls file on the server for each user-wouldn't my add-in have to open that file each time it needed to access those settings?

Ken Puls
04-24-2007, 08:59 AM
Yes. My users should be able to change their settings. Why can't you overwrite an option in the personal workbook? Also, if I use a seperate xls file on the server for each user-wouldn't my add-in have to open that file each time it needed to access those settings?

I wouldn't store a personal.xls on the server for each user.

Two other options you have:
-Registry keys (GetSetting and SaveSetting). This stores the keys for each user in their registry.
-If you're going the external file route, personally I'd use a database for this. I've done this with a couple of my add-ins and it works quite well. The db is then stored on the server. The advantages of the db are that it permits multiple users reading/writing to it at the same time, which can be a challenge when using Excel workbooks.

Djblois
04-24-2007, 09:02 AM
I like the idea of the registry but how easy is it to write to it? and do I have access to alter someones registry? Can you give me an example of how to work with the registry settings in some code?

mdmackillop
04-24-2007, 09:12 AM
Will you need to change these setting?
I didn't ask about your users. How will you as administarator change them?

If so, having them stored in Personal could be a nuisance as you can't just overwrite it.
I mean't that you can't just overwrite the file to update settings, as you could with alternative solutions.

Why not use a txt file or an xls file stored on the server.
I would not save Personal on the server, but an excel file could be used for storing settings. Access would be better if read/write is common. If you just need to read the settings, this can be done without opening the workbook.

Registry Keys - see the KB for an example

Ken Puls
04-24-2007, 09:17 AM
I like the idea of the registry but how easy is it to write to it? and do I have access to alter someones registry? Can you give me an example of how to work with the registry settings in some code?
Very simple, actually. As Malcolm says, the KB has examples, or you could try the Excel help files.

Basically, it is set as Application name (yours), Key Folder, Key Name, value:
SaveSetting "MyApp","Startup", "Top", 75
And retrieved as Application name (yours), Key Folder, Key Name, default value if not found:
GetSetting "MyApp","Startup","Left", "25"

Djblois
04-24-2007, 09:32 AM
would the regsistry be different for each user? also I guess the key folder is always the startup folder? and what is key name? I found it in the KB and I got the same explanatin you gave me Ken. Also, I haven't found it yet it the help files but I am still looking.

Djblois
04-24-2007, 09:36 AM
Ok I found it but it doesn't tell me I create the Key name or what? Do I first have to create the registry file? and would it be different for each user?

Ken Puls
04-24-2007, 09:42 AM
Here, lets say that you want to set up an application called DjbloisApp, and you want the following values stored:

ScreenPosition
-Left
-Top
-Width

Default Values
-All
-Favourite Color

You'd set up your settings something like this:

SaveSetting "DjbloisApp","ScreenPosition", "Left", 75
SaveSetting "DjbloisApp","ScreenPosition", "Top", 10
SaveSetting "DjbloisApp","ScreenPosition", "Width", 100
SaveSetting "DjbloisApp","Defaults", "All", False
SaveSetting "DjbloisApp","Defaults", "FavCol", "VBAXGreen"
So what you see here is that you end up with a folder like heirarchy of your own creation. The DjbloisApp would then be a folder found at HKEY_CURRENT_USER\Software\VB and VBA Program Settings\DjbloisApp. ScreenPosition and Defaults are two folders within them. The next parts are the keys, and the final pieces are the values that you set for them.

You have control to call them whatever you want. Just keep everything in the same AppName folder though, as that keeps it organized. You can create as many key folders and key names within as you like.

HTH,

Ken Puls
04-24-2007, 09:43 AM
Ok I found it but it doesn't tell me I create the Key name or what? Do I first have to create the registry file? and would it be different for each user?

The key names are created when you give it the SaveSetting command. And yes, it will be different for each user. :)

Djblois
04-24-2007, 10:45 AM
Ok, so far it sounds great. However don't kill me I have one more question. The options are defaults for the user forms I have created.
This was how I was going to read them:

If SettingsPage.Sheets(1).Range("B1").Value = "No" Then Cases.Value = False
If SettingsPage.Sheets(1).Range("B2").Value = "Yes" Then Units.Value = True
If SettingsPage.Sheets(1).Range("B3").Value = "Yes" Then Profit.Value = False
If SettingsPage.Sheets(1).Range("B4").Value = "Yes" Then ProfitPerc.Value = True

Ken Puls
04-24-2007, 10:52 AM
Okay, so you'll need to figure out at which point you want the defaults saved. If it's to whatever the user sets in your boxes, then you youd could the BeforeUpdate event (don't use change or it will fire every time you type a character), or when you go to unload the userform.

Then, to read them in to your form, you'd set up your userform_initialize event to read the settings in:

With Me
.Cases.Value = Getsetting("DjbloisApp","Defaults","Cases",false)
.Units.Value = Getsetting("DjbloisApp","Defaults","Units",true)
.Profit.Value = Getsetting("DjbloisApp","Defaults","Profit",false)
.ProfitPerc.Value = Getsetting("DjbloisApp","Defaults","ProfitPerc",true)
end with
(This was written in the browser, so is untested.)

For reference, it looks a little funny to me that you are setting what look like numerical values to true/false. I can't tell if those are checkboxes, though, as you don't seem to have a naming convention. Might be something you want to think about.

HTH,

Djblois
04-24-2007, 10:56 AM
no Ken,

Cases for example is a checkbox for my user to set wether or not they want cases in their report. Same with the others. Also, I want to set it up so the settings don't get set until the form gets unloaded.

Djblois
04-24-2007, 12:22 PM
It isn't working for me. This is the code I am using to set the settings:

Sub SetSalesReportOptions()

If PivotTableOptions.Cases Then
SaveSetting "Business Reporting Today", "SalesReportOptions", "Cases", True
Else
SaveSetting "Business Reporting Today", "SalesReportOptions", "Cases", False
End If
If PivotTableOptions.Units Then
SaveSetting "Business Reporting Today", "SalesReportOptions", "Units", True
Else
SaveSetting "Business Reporting Today", "SalesReportOptions", "Units", False
End If

End Sub


This is the code I am getting to read the Settings:

With Me
.Cases.Value = GetSetting("Business Reporting Today", "Sales Report Options", "Cases", False)
.Units.Value = GetSetting("Business Reporting Today", "Sales Report Options", "Units", False)
.Profit.Value = GetSetting("Business Reporting Today", "Sales Report Options", "Profit", False)
.ProfitPerc.Value = GetSetting("Business Reporting Today", "Sales Report Options", "ProfitPerc", False)
End With

PivotTableOptions is my User form
Cases, Units, etc. are all checkboxes
I am also using Radio buttons that I want to set. However, I think they should be pretty much read and set the same?

Ken Puls
04-24-2007, 03:55 PM
Hmmm... notice anything different between the following two lines?
"SalesReportOptions"
"Sales Report Options"

:) (They need to be the same between SaveSetting and GetSetting.)

Also, save yourself some typing. This:

SaveSetting "Business Reporting Today", "SalesReportOptions", "Cases", PivotTableOptions.Cases
is equivalent to this:
If PivotTableOptions.Cases Then
SaveSetting "Business Reporting Today", "SalesReportOptions", "Cases", True
Else
SaveSetting "Business Reporting Today", "SalesReportOptions", "Cases", False
End If

HTH,

Ken Puls
04-24-2007, 03:59 PM
Cases for example is a checkbox for my user to set wether or not they want cases in their report. Same with the others.

Right, see, and that's kind of my point on the naming conventions. I'd do something like this:

cmdMyButton
chkMyCheckbox (so chkCases)
lblMyLabel
lbMyListbox

Etc...

That way, anyone who reads your code can see exactly what you are working with.

Djblois
04-25-2007, 05:54 AM
Ken that is great. I will be changing the naming convention like you suggested just not yet because I have to change a lot. However, one last question. When using Radio buttons, do I have to set each radio button seperate and read each one seperate even though only one of them can be true? Or can I set which one is true?

Technically I am talking about option buttons set to a group or in a frame.

Ken Puls
04-25-2007, 08:44 AM
It depends how many buttons you have. You'd always need to check you reach the count -1.

What I mean is that if you only have two buttons, yes, checking one will do. If it's false, the other must be true.

If you have three, though, you'd need to check at least two. If 1 & 2 are false, 3 must be true. For 4, check 3, etc..

Does that make sense?

Djblois
04-25-2007, 11:21 AM
Ken,

It is working great but now I am trying to use the settings to not show a user form if the user never wants to. Here is the code I have tested:

If GetSetting("Business Reporting Today", "DetailTab", "Dont Show") = True Then
Organize.Hide
End If

This would be great if I can get this to work

mdmackillop
04-25-2007, 11:27 AM
You can set the frame tag value using the option buttons, then you just have one value to check. You can also set the initial optionbutton values based on a fixed or retrieved value. eg
Private Sub UserForm_Initialize()
x = 3
Me.Controls("OptionButton" & x) = True
End Sub

Private Sub CommandButton1_Click()
MsgBox Frame1.Tag
End Sub

Private Sub OptionButton1_Click()
If OptionButton1 Then Frame1.Tag = 1
End Sub
Private Sub OptionButton2_Click()
If OptionButton2 Then Frame1.Tag = 2
End Sub
Private Sub OptionButton3_Click()
If OptionButton3 Then Frame1.Tag = 3
End Sub

Djblois
04-25-2007, 12:25 PM
Mdmack that looks promising to cut down on typing but how can I use that with the registry settings? Also, how can I let my users set options that would affect code directly? Ex:

I want to let my users set it so a userform will not show. Here is the code I tested:



If GetSetting("Business Reporting Today", "DetailTab", "Dont Show") = True Then
Organize.Hide
End If


I am running that code in the initialize for that form but it still shows. However I do not get an error.

mdmackillop
04-25-2007, 01:11 PM
What code is used to open the userform?

Djblois
04-25-2007, 01:19 PM
organize.show

I originally want to show the code because it loads the settings that the user wants and then it hides the form.

mdmackillop
04-25-2007, 01:43 PM
As far as I can see Hide does nothing and Visible is not valid. You could set Height and Width to 0, but that leaves the Caption bar.
I've no knowledge what you are using the settings for, what applies to the form, or the workbook, or the interaction between them, or really what you are trying to achieve.

Djblois
04-25-2007, 01:51 PM
I have a form that pops up for what invoices the user wants to delete and a few other options. However certain users will want to set these defaults and never see the form again but some people might always want to see the form. Also, hide I use in other situations which hides the form from sight.

Charlize
04-26-2007, 02:44 AM
Before showing the form, check the value of "Dont Show".
If GetSetting("Business Reporting Today", "DetailTab", "Dont Show") <> True Then
Organize.show
End If Charlize

Djblois
04-26-2007, 05:09 AM
Thank you Charlize,

That is how I am doing it now, I just have to change the logic a little.

Djblois
04-26-2007, 05:21 AM
One last thing I am trying to do is at a later time I want to save one setting equal to another setting. To explain, I want to read the first setting and then set the second setting equal to the first setting. Is this possible?

Djblois
04-26-2007, 07:15 AM
I still can't find a way to mark a thread unsolved. Can someone do it for me? thank you

lucas
04-26-2007, 07:23 AM
Thread tools....edit thread
edit the title and save changes

Djblois
04-26-2007, 07:56 AM
Lucas,

Thank you for your help but I don't have an edit thread under thread tools

lucas
04-26-2007, 08:16 AM
Solved removed from title....

Djblois
04-26-2007, 08:17 AM
thank you

Djblois
04-26-2007, 08:45 AM
I am just reposting this to move it to the bottom:

One last thing I am trying to do is at a later time I want to save one setting equal to another setting. To explain, I want to read the first setting and then set the second setting equal to the first setting. Is this possible?

The reason I want to do this is My users will have their own defaults but then if one day they need to run a lot of reports with the same options (That are different than their defaults), I don't want them to have to go into settings to change their defaults and then when they are done go back to change them into their normal defaults. I am adding a check box to the bottem to reuse the settings they have chosen on the form and then to go back to their defaults they just uncheck it.

Djblois
04-26-2007, 11:30 AM
Has anyone read this?

Ken Puls
04-26-2007, 11:44 AM
So... what you're trying to do is create an "options" screen that your users can go to so that they can set defaults, but then not be bothered with seeing them unless they want to change them? Is that right?

You could use a multipage control for that. I usually would put a button on the main page for "Options" that takes you to page 2. Add a button to page 2 labelled "Home" or something that takes you back to page one. Then hide the tabs at design time. Can work quite slick.

Is that the kind of thing you meant?

Djblois
04-26-2007, 11:48 AM
No, but close Ken. The options they go to the help drop down and then Options to set the options. But now if they are running reports that all have the same options I want to include a check box on the bottom of the screen that they can check and it will save them for the next time the form opens up but if it is unchecked it will load the default from the settings page.

Ken Puls
04-26-2007, 11:54 AM
Where is the help dropdown, exactly? Is this the help menu, or on your userform. I assume that you've got this set to store the registry settings already?

As for reading the stuff and setting the defaults, you'd set your defaults in the Userform_Intialize event. Or did I miss something?

Djblois
04-26-2007, 12:01 PM
The help menu is on a custom toolbar. I know where to set my defaults but the reason why I want to be able to set one setting = to another setting is I want to give my user the option not to show the form but if the form does show use those options. so can I save one setting = to another setting?

Ken Puls
04-26-2007, 12:07 PM
If I think I follow you here...

You're trying to run a routine where the form is optional. If the user elects to open the form, they'll use the forms settings. If they don't, then they use the defaults you provide? Either way, the form is closed before the routine continues?

Sorry, it's a little muddy, and I'd like to be clearer before I attempt to give you an answer on this.

Djblois
04-26-2007, 12:10 PM
That is right ken

Djblois
04-26-2007, 02:44 PM
Ken where are you I need you. hehe

Ken Puls
04-26-2007, 02:50 PM
Sorry, I have a job. :)

What I'd do then, is set up some public variables. Set them to yoru defaults when you open your calling routine.

Then, the form can be optionally launched. If it is launched, you'll play with the registry settings how you were. When the form is closed, write the checkbox values to your public variables during the unload.

Voila! You should have what you need to continue on.

How does that sound?

Djblois
04-26-2007, 02:52 PM
I will test

Ken Puls
04-27-2007, 03:27 PM
Daniel,

I assumed that you got this portion working?

Djblois
04-27-2007, 03:28 PM
Sorry, yes I did I just didn't have time to post a thank you.

Ken Puls
04-27-2007, 03:29 PM
No worries. Just wanted to make sure. :)