PDA

View Full Version : [SOLVED] how can i save userform settings



kofatoto
08-27-2017, 03:21 AM
hello
excuse me for my bad English language
i have userform including a change color button

On Error Resume Next
Dim UserColor As Variant
If ComboBox9.ListIndex = 0 Then
UserColor = GetAColor()
If UserColor <> False Then
Dim i As Long
For i = 1 To 256
Me.Controls("CommandButton" & i).BackColor = UserColor
Next i
End If
ComboBox9.Value = ""
End If

the function code

Function GetAColor() As Variant
Dim OldColor As Double
OldColor = ActiveWorkbook.Colors(1)
If Application.Dialogs(xlDialogEditColor).Show(1) = True Then
GetAColor = ActiveWorkbook.Colors(1)
Else
GetAColor = False
End If
ActiveWorkbook.Colors(1) = OldColor
End Function

how can i save these settings ( when i reopen userform again , i get it )

mana
08-27-2017, 06:12 AM
Standard module

Option Explicit

Dim UserColor As Variant

Sub test()

If IsEmpty(UserColor) Then
UserColor = GetAColor()
ElseIf MsgBox("chage color?", vbYesNo) = vbYes Then
UserColor = GetAColor()
End If

MsgBox UserColor

End Sub


Function GetAColor() As Variant
Dim OldColor As Double

OldColor = ActiveWorkbook.Colors(1)
If Application.Dialogs(xlDialogEditColor).Show(1) = True Then
GetAColor = ActiveWorkbook.Colors(1)
Else
GetAColor = False
End If
ActiveWorkbook.Colors(1) = OldColor

End Function


Userform module

Option Explicit

Private Sub UserForm_Click()
test
End Sub



マナ

kofatoto
08-27-2017, 09:28 AM
excuse me
not working
i don't understand

YasserKhalil
08-27-2017, 11:55 AM
Hello
Mana's code is working well for me
The main idea is to declare the variable at the top of the module so as to be used in public way

Dim UserColor As Variant

It would be better to attach a sample of your workbook with some desired or expected output instead of letting others guess the required

kofatoto
08-27-2017, 12:25 PM
my file

YasserKhalil
08-27-2017, 01:18 PM
Do you mean something like that?


Dim UserColor As Variant

Private Sub ComboBox1_Change()
On Error Resume Next
If ComboBox1.ListIndex = 0 Then
UserColor = GetAColor()
If UserColor <> False Then
Dim i As Long
For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = UserColor
Range("Z1") = UserColor
Next I
End If
ComboBox1.Value = ""
End If
End Sub


Function GetAColor() As Variant
Dim oldColor As Double
oldColor = ActiveWorkbook.Colors(1)
If Application.Dialogs(xlDialogEditColor).Show(1) = True Then
GetAColor = ActiveWorkbook.Colors(1)
Else
GetAColor = False
End If
ActiveWorkbook.Colors(1) = oldColor
End Function


Private Sub UserForm_Activate()
With ComboBox1
.AddItem "change all buttons color"
End With
If Range("Z1") = "" Then Exit Sub
For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = Range("Z1")
Next i
End Sub

SamT
08-27-2017, 02:48 PM
In this workbook, if you set The Worhsheet Object "SavedColors" Visible Property to Visible, you will be able to see my User Name and the last color I chose. If you can open the workbook on a computer with Login name SamT, the Form will use my last chosen color.

To test this workbook, Log on to a computer with one user name, set the form color and save the Workbook. Close Excel and Change Users on the Computer, then set the form color and save the workbook again. Now, both Users should see their last chosen color when they use the User form.


Code that saves User Color
Private Sub ComboBox1_Change()
Dim UserColor As Variant
Dim i As Long
Dim Found As Range

If ComboBox1.ListIndex = 0 Then

On Error Resume Next
UserColor = GetAColor()
If UserColor <> False Then


With SavedColors 'SavedColors is a worksheet Object name.
Set Found = .Range("A:A").Find(Application.UserName)
If Found Is Nothing Then
'Add a user name and the chosen color
With .Cells(Rows.Count, "A").End(xlUp).Offset(1)
.Value = Application.UserName
.Offset(, 1) = UserColor
End With

'Esle use the existing user name
Else: Found.Offset(, 1) = UserColor
End If
End With

For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = UserColor

Next i
End If
ComboBox1.Value = ""
End If

End Sub

Code that loads the User's saved color choice
Private Sub UserForm_Initialize()
Dim i As Long
Dim UserColor As Variant
Dim Found As Range

On Error Resume Next
Set Found = SavedColors.Range("A:A").Find(Application.UserName)
If Not Found Is Nothing Then 'This user has chosen a color alreadt

For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = UserColor
Next
Else 'This user has not chosen a color yet
End If

End Sub

Paul_Hossler
08-27-2017, 03:07 PM
I think using GetSetting and SaveSetting would be easier




Option Explicit
Private Sub ComboBox1_Change()
Dim UserColor As Long
Dim i As Long

If ComboBox1.ListIndex = 0 Then
UserColor = GetAColor()

If UserColor <> False Then
Call SaveSetting("Colors", "Color", "ButtonColor", CStr(UserColor))
For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = UserColor
Next I
End If

ComboBox1.Value = ""
End If
End Sub


Function GetAColor() As Long

Dim OldColor As Double
OldColor = ActiveWorkbook.Colors(1)
If Application.Dialogs(xlDialogEditColor).Show(1) = True Then
GetAColor = ActiveWorkbook.Colors(1)
Else
GetAColor = False
End If
ActiveWorkbook.Colors(1) = OldColor
End Function


Private Sub UserForm_Activate()
Dim UserColor As Long
Dim i As Long

If Len(GetSetting("Colors", "Color", "ButtonColor")) = 0 Then
UserColor = -2147483633
Else
UserColor = CLng(GetSetting("Colors", "Color", "ButtonColor"))
End If
For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = UserColor
Next i

With ComboBox1
.AddItem "change all buttons color"
End With
End Sub

kofatoto
08-27-2017, 03:37 PM
thanks
My respect to all

kofatoto
08-28-2017, 12:06 AM
another question please
how can i restore default settings (undo to first settings)

SamT
08-28-2017, 06:40 AM
Dim DefaultColor
'
'
DefaultColor = Me.Backcolor
'
'

For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = DefaultColor
Next

snb
08-28-2017, 07:26 AM
1. Why would you like to change all buttons in a Userform ?

2. if you .hide the userform and .show it again, the last colours will be shown.

3. If you unload the userform the colours will be reset to the values of design Mode.

So: as long as you do not unload the userform the new colours will be in effect.
But I do not see any reasonable purpose.

SamT
08-28-2017, 09:42 AM
Personalized themes.