Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 49

Thread: Solved: Setting using Personal workbook to store settings

  1. #1
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location

    Solved: Setting using Personal workbook to store settings

    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:

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

  2. #2
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    Maybe insert the sheet reference before the range ???
    [VBA]Workbooks("Personal").Sheets(1).Range("D1").Value = "Yes"[/VBA]
    Could be that you need .xls after Personal because it is already saved. In Dutch it is Persnlk.xls to use.

    Charlize

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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?

  5. #5
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Djblois
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  6. #6
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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?

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Djblois
    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:
    [vba]SaveSetting "MyApp","Startup", "Top", 75[/vba]
    And retrieved as Application name (yours), Key Folder, Key Name, default value if not found:
    [vba]GetSetting "MyApp","Startup","Left", "25"[/vba]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  9. #9
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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.

  10. #10
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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?

  11. #11
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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:

    [vba]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"[/vba]
    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, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  12. #12
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Djblois
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  13. #13
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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:

    [VBA]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[/VBA]

  14. #14
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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:

    [vba]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[/vba]
    (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,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  15. #15
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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.

  16. #16
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    It isn't working for me. This is the code I am using to set the settings:

    [VBA]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
    [/VBA]

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

    [VBA]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[/VBA]

    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?

  17. #17
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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:

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

    HTH,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  18. #18
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Djblois
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  19. #19
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location
    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.
    Last edited by Djblois; 04-25-2007 at 06:06 AM.

  20. #20
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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?
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •