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 )
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
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)
Dim DefaultColor
'
'
DefaultColor = Me.Backcolor
'
'
For i = 1 To 30
Me.Controls("CommandButton" & i).BackColor = DefaultColor
Next
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.