Private Sub UserForm_Initialize()
Dim nam As Name, t
For Each n In ThisWorkbook.Names
If n.Name = "Check" Then t = Evaluate(n.RefersTo): Exit For
Next
'MsgBox TypeName(t)
If IsArray(t) Then
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "CheckBox" Then
A = A + 1: Ctrl.Value = t(A)
End If
Next
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim Ctrl, A&, t(), nam As Name
On Error Resume Next
Set nam = ThisWorkbook.Names.Add("Check", "0)")
On Error GoTo 0
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "CheckBox" Then
A = A + 1: ReDim Preserve t(1 To A): t(A) = Abs(Ctrl.Value)
End If
Next
Debug.Print Join(t, ";")
ThisWorkbook.Names("Check").RefersTo = t
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim Ctrl, A&
ufname = Me.Name
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "CheckBox" Then
If Ctrl.Value Then A = A + 1: ReDim Preserve T(0 To A - 1): T(A - 1) = Ctrl.Name
End If
Next
memocheck
End Sub
Dim fermé As Boolean
Public T()
Public ufname
'Activer la référence Microsoft Visual Basic for Application Extensibility.5.x
Sub memocheck()
If fermé = False Then
fermé = True: Application.OnTime Now + 0.000005, "memocheck"
Else
fermé = False
Dim uf As VBComponent, i&
Set uf = ThisWorkbook.VBProject.VBComponents(ufname)
For i = LBound(T) To UBound(T)
uf.designer.Controls(T(i)).Value = True
Next
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Const mesControles = "TextBox1;OptionButton1;OptionButton2;OptionButton3;ScrollBar1;CheckBox1;CheckBox2"
Dim n&, x
n = 1
With Sheets("HiddenData")
.Range("a2:b9999").Clear
For Each x In Split(mesControles, ";"): n = n + 1: .Cells(n, 1) = x: .Cells(n, 2) = Controls(x): Next
End With
End Sub
Private Sub UserForm_Initialize()
Dim n&, i&
ScrollBar1_Change
With Sheets("HiddenData")
n = .Cells(Rows.Count, "a").End(xlUp).Row
If n > 1 Then For i = 2 To n: Me.Controls(.Cells(i, 1).Value) = .Cells(i, 2).Value: Next i
End With
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
CB = CheckBox1
Application.OnTime 1, "Memorise" 'lancement différé
End Sub
Public CB As Boolean 'mémorise la variable
Sub Memorise()
ThisWorkbook.VBProject.VBComponents("UserForm2").Designer.Controls("CheckBox1").Value = CB
ThisWorkbook.Save 'enregistre le fichier
End Sub
Il faut avoir coché l'option Accès approuvé au modèle d'objet du projet VBA (onglet Fichier-Options-Centre de gestion de la confidentialité-Paramètres...-Paramètres des macros).
c'est vrai quoi on peut même plus s'amuser a foutre le boxon dans nos app aux boulotDans un certain nombre d'organisation, l'utilisateur n'a pas accès à cette gestion des autorisations (et c'est bien dommage)
BonsourBonne année
je joins le classeur