Consulting

Results 1 to 4 of 4

Thread: How to get around userform inability to "remember"

  1. #1

    How to get around userform inability to "remember"

    Hi,

    I have an existing userform that's all working fine etc, but i need to be able to 1/ enable users to adjust the colour to suit their needs 2/ change the targets of the stats that the userform tracks, so i don't need to keep updating it myself.

    "2" Is easy and that's working fine with a few spinbuttons linked to cells etc

    "1" I'm 90% there as I've just set up coloured command buttons that change the colour of the userform to the respective colour selected. Now,I'm stumped with how to get the userform initialize event, to pick up what colour was selected last time. I was thinking of referring to the relevant colour selected in a cell, but dont know where to go from there. Using the example below places "16777215" in the cell


    [VBA]Private Sub Cmd2_Click()
    Application.ScreenUpdating = False
    With ThisWorkbook.Sheets("KpiTracker")
    .Range("M1").Value = RGB(255, 255, 255)
    UserForm4.BackColor = RGB(255, 255, 255)
    UserForm1.BackColor = RGB(255, 255, 255)
    LblB1Targets.ForeColor = RGB(255, 0, 0)
    LblB2Targets.ForeColor = RGB(255, 0, 0)
    LblB3Targets.ForeColor = RGB(255, 0, 0)
    LblAcwTargets.ForeColor = RGB(255, 0, 0)
    LblBox1Target.ForeColor = RGB(255, 0, 0)
    LblBox2Target.ForeColor = RGB(255, 0, 0)
    LblBox3Target.ForeColor = RGB(255, 0, 0)
    LblAcwTarget.ForeColor = RGB(255, 0, 0)[/VBA]

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,645
    Did you try the result of

    [vba]
    LblB1Targets.ForeColor =16777215
    [/vba]

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Are you just trying to find a way to store the color selections of various users? Do you want to save many color themes, or just the default.

    If you just want to save the defaults, use an Enumeration
    [vba]Enum FormColorDefaults
    Form4BackColor = 16777215
    Form1BackColor = 16777215
    B1TargetsForeColor = 16777215
    B2TargetsForeColor = 16777215
    B3TargetsForeColor = 16777215
    AcwTargetsForeColor = 16777215
    Box1TargetForeColor = 16777215
    Box2TargetForeColor = 16777215
    Box3TargetForeColor = 16777215
    AcwTargetForeColor = 16777215
    End Enum[/vba] [vba]
    Sub SetColorsToDefault()
    UserForm4.BackColor = Form4BackColor
    'etc[/vba]

    If you are trying to save multiple themes, I would use an xlSheetVeryHidden sheet with an enumerator like above and Column Range Names selected by the Users.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Sorry for late response guys and thanks for your input.

    It turned out to be quite simple

    [VBA]Private Sub Cmd1_Click()
    Application.ScreenUpdating = False
    With ThisWorkbook.Sheets("KpiTracker")
    UserForm1.BackColor = RGB(0, 0, 0)
    .Range("M1").Value = RGB(0, 0, 0)[/VBA]

    Which places "0" (or 16711680 etc) in M1

    And simply

    [VBA]UserForm1.BackColor = .Range("M1").Value[/VBA]

    For the Initialize jobby

Posting Permissions

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