| |
フォームの履歴ですよね？
前に書いてみた物ですが...。
シート名に「フォーム履歴」と名づけた物を用意してください。
（非表示にした方が何となく良いかも。）
意味が違ったらすみません。
全部フォームモジュール。
Private Sub UserForm_Initialize()
Dim Cel As Range
'Me.ComboBox1.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
'Me.ListBox1.List = Array("AA", "BB", "CC", "DD", "EE", "FF")
With ThisWorkbook.Sheets("フォーム履歴")
For Each Cel In .Range("A1", .Range("B65536").End(xlUp))
If Cel.Offset(, 2).Value <> "" Then
With Me.Controls(Cel.Offset(, 1).Value)
If Cel.Value = "ComboBox" Or _
Cel.Value = "ListBox" Then
.ListIndex = Cel.Offset(, 2).Value
ElseIf Cel.Value = "CheckBox" Or _
Cel.Value = "OptionButton" Then
.Value = Cel.Offset(, 2).Value
ElseIf Cel.Value = "TextBox" Or _
Cel.Value = "MultiPage" Then
.Value = Cel.Offset(, 2).Value
End If
End With
End If
Next
End With
'無駄な部分もあるけど．．．。
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim Pt As Control, i As Long
With ThisWorkbook.Sheets("フォーム履歴")
For Each Pt In Me.Controls
i = i + 1
If .Cells(i, 1).Value <> TypeName(Pt) Then
.Cells(i, 1).Value = TypeName(Pt)
End If
If .Cells(i, 2).Value <> Pt.Name Then
.Cells(i, 2).Value = Pt.Name
End If
If TypeName(Pt) = "ComboBox" Or _
TypeName(Pt) = "ListBox" Then
If .Cells(i, 3).Value <> Pt.ListIndex Then
.Cells(i, 3).Value = Pt.ListIndex
End If
ElseIf TypeName(Pt) = "CheckBox" Or _
TypeName(Pt) = "OptionButton" Then
If .Cells(i, 3).Value <> Pt.Value Then
.Cells(i, 3).Value = Pt.Value
End If
ElseIf TypeName(Pt) = "TextBox" Or _
TypeName(Pt) = "MultiPage" Then
If .Cells(i, 3).Value <> Pt.Value Then
.Cells(i, 3).Value = Pt.Value
End If
End If
Next
'.Cells(i + 2, 2).Value = Me.ActiveControl.Name
End With
End Sub
|
|
Jaka
- 06/1/17(火) 9:25 -
