PDA

View Full Version : How to get around userform inability to "remember"



gringo287
04-07-2013, 01:27 PM
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


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)

snb
04-07-2013, 03:05 PM
Did you try the result of


LblB1Targets.ForeColor =16777215

SamT
04-07-2013, 03:54 PM
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
Enum FormColorDefaults
Form4BackColor = 16777215
Form1BackColor = 16777215
B1TargetsForeColor = 16777215
B2TargetsForeColor = 16777215
B3TargetsForeColor = 16777215
AcwTargetsForeColor = 16777215
Box1TargetForeColor = 16777215
Box2TargetForeColor = 16777215
Box3TargetForeColor = 16777215
AcwTargetForeColor = 16777215
End Enum
Sub SetColorsToDefault()
UserForm4.BackColor = Form4BackColor
'etc

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.

gringo287
04-11-2013, 12:17 PM
Sorry for late response guys and thanks for your input.

It turned out to be quite simple

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

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

And simply

UserForm1.BackColor = .Range("M1").Value

For the Initialize jobby