Results 1 to 7 of 7

Thread: [RESOLVED] [Excel] Repeat savesettings for every control in form

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Resolved [RESOLVED] [Excel] Repeat savesettings for every control in form

    Hi PeopleWithMuchMoreKnowledge

    I would appreciate some assistance with this:
    Before I open a form, I want to retrieve the last values of each control on it (only checkboxes). Similarly save all settings before closing the form.

    The prooblem that I have is that the code only runs the for loop once and does not recocknize any controls on my form.

    Sub routine called on opening form:
    Code:
    Public Sub CheckLastSettings(fileName As String, mySystemType As String)
    On Error Resume Next
    Dim myCheckBox As CheckBox
        For Each myCheckBox In Me.Controls
            myCheckBox.Value = GetSetting(fileName, mySystemType, myCheckBox, 0)
        Next myCheckBox
    End Sub
    subroutine called on closing the form:
    Code:
    Public Sub SaveLastSettings(frmName As frmOptions, fileName As String, mySystemType As String)
    On Error Resume Next
    Dim myCheckBox As CheckBox
        For Each myCheckBox In frmOptions.Controls
             SaveSetting fileName, mySystemType, myCheckBox.Value, 0
        Next myCheckBox
    End Sub
    The myChechbox variable created remains empty in both sub routines.

    What am I missing?

    Regards, Ossewa

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] Repeat savesettings for every control in form

    you need to loop all controls, then savesetting if type is checkbox, or if name = check............, or if the control has a value property
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: [Excel] Repeat savesettings for every control in form

    Not sure how to do this, please show some code as example? Thx

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] Repeat savesettings for every control in form

    this will save the value from all controls that have a value property, on error resume next to avoid error if any controls with no value property
    vb Code:
    1. Dim c As Control
    2. On Error Resume Next
    3. For Each c In Me.Controls
    4.    SaveSetting "thisApp", "settings", c.Name, c.Value
    5. Next
    6. On Error GoTo 0
    if you only want to save checkbox values use
    if typename(c) = "CheckBox" then
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: [Excel] Repeat savesettings for every control in form

    If you want just checkboxs then
    Code:
    Dim ctrl As Control
    For Each ctrl In Me.Controls
         If TypeOf ctrl Is CheckBox Then
            SaveSetting "thisApp", "settings", c.Name, c.Value
         End If
    Next

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] Repeat savesettings for every control in form

    @ hack i tested with typeof, it worked for command button, but not checkbox (or other, i presume, forms2 controls)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Re: [Excel] Repeat savesettings for every control in form

    Thanks, it works.

Posting Permissions

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



Click Here to Expand Forum to Full Width