昨日は、作成したチェックボックスのグループ番号更新に挑戦した。
infoment.hatenablog.com
今日は締めとして、今回シリーズのコードをまとめて紹介する。
最終的に、ユーザーフォームはこのようになった。
番号 |
種類 |
オブジェクト名 |
Caption |
備考 |
① |
UserForm |
CBsEditForm |
CheckBox(フォームコントロール)作成・編集 |
|
② |
Frame |
Frame1 |
選択セルの文字をCheckBoxに変換 |
単なる枠。フレームの機能は未使用。 |
③ |
CommandButton |
SetCheckBoxButton |
配 置 |
|
④ |
CheckBox |
IgnoreBlankCheck |
空白を無視する |
初期値:True |
⑤ |
CommandButton |
CloseButton |
終 了 |
|
⑥ |
Label |
Label1 |
名前 |
|
⑦ |
Label |
Label2 |
キャプション |
|
⑧ |
ListBox |
CBsListBox |
|
ColumnCount:2 |
⑨ |
CheckBox |
MultiSelectCheck |
リストの複数選択可 |
初期値:False |
⑩ |
Label |
Label3 |
選択CheckBoxのキャプション変更 |
|
⑪ |
TextBox |
NewCaptionInputBox |
|
|
⑫ |
CommandButton |
CaptionChangeButton |
変更 |
|
⑬ |
CommandButton |
SelectAllButton |
全選択 |
|
⑭ |
CommandButton |
GroupingButton |
選択グループ化 |
|
⑮ |
CommandButton |
SelectedCBsDeleteButton |
選択削除 |
|
⑯ |
CommandButton |
GroupingSelectButton |
グループを選択 |
|
⑰ |
ComboBox |
GroupNumberComboBox |
|
|
⑱ |
Label |
Label4 |
選択CheckBoxの名前変更 |
|
⑲ |
TextBox |
NewNameInputBox |
|
|
⑳ |
CommandButton |
NameChangeButton |
変更 |
|
コードは、長いので、例によって今回も折りたたんでおく。
Option Explicit
Function SetCheckBox(target_range As Range, _
Optional cb_name As String = vbNullString, _
Optional cb_caption As String = vbNullString) As CheckBox
Dim CB As CheckBox
With target_range
Set CB = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
End With
If cb_name <> vbNullString Then CB.Name = cb_name
If cb_caption <> vbNullString Then
CB.Caption = cb_caption
Dim CBX As OLEObject
Set CBX = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1")
CBX.Width = 1000
CBX.Object.Caption = cb_caption
CBX.Object.AutoSize = True
CB.Width = CBX.Width
CB.Height = CBX.Height
CBX.Delete
End If
Set SetCheckBox = CB
End Function
Public Property Get CBs() As CheckBoxes
Set CBs = ActiveSheet.CheckBoxes
End Property
Public Property Get CheckBoxList() As Variant
Dim arr() As Variant
If CBs.Count = 0 Then
CheckBoxList = Array()
Exit Property
Else
ReDim arr(1 To CBs.Count, 1 To 3)
End If
Dim i
On Error Resume Next
For i = 1 To CBs.Count
arr(i, 1) = CBs.Item(i).Name
arr(i, 2) = CBs.Item(i).Caption
arr(i, 3) = CLng(Split(CBs.Item(i).Name, "_")(1))
Next
CheckBoxList = arr
End Property
Sub SetSingleSelect()
Dim SelectedCBName As String
SelectedCBName = Application.Caller
Dim GroupNumber As Long
On Error Resume Next
GroupNumber = CLng(Split(SelectedCBName, "_")(1))
If Err.Number <> 0 Then Exit Sub
Dim CB As CheckBox
For Each CB In CBs
If CB.Name <> Application.Caller Then
If Split(CB.Name, "_")(1) = GroupNumber Then
CB.Value = xlOff
End If
End If
Next
On Error GoTo 0
End Sub
Function RenamedCB(check_box As CheckBox, _
group_number As Long, _
cb_caption As String) As CheckBox
check_box.Name = "CB_" & group_number & "_" & cb_caption
Set RenamedCB = check_box
End Function
Public Property Get MaxGroupNumber()
Dim arr() As Variant
ReDim arr(1 To CBs.Count)
Dim i As Long: i = 1
Dim CB As CheckBox
On Error Resume Next
For Each CB In CBs
arr(i) = CLng(Split(CBs.Item(i).Name, "_")(1))
i = i + 1
Next
On Error GoTo 0
MaxGroupNumber = WorksheetFunction.Max(arr)
End Property
Public Function SortArray(ByVal arr As Variant, _
Optional sort_order As Excel.XlSortOrder = xlAscending) As Variant
Dim aryList As Object
Dim s As Variant
Set aryList = CreateObject("System.Collections.ArrayList")
For Each s In arr
Call aryList.Add(s)
Next
Select Case sort_order
Case xlAscending
Call aryList.Sort
Case xlDescending
Call aryList.Sort
Call aryList.Reverse
End Select
SortArray = aryList.ToArray
End Function
Private Sub UpdateGroupNumber()
Dim TempDict As Object
Set TempDict = CreateObject("Scripting.Dictionary")
Dim CB As CheckBox
On Error Resume Next
For Each CB In CBs
TempDict(Split(CB.Name, "_")(1)) = 1
Next
On Error GoTo 0
Dim arr As Variant
arr = TempDict.keys
arr = SortArray(arr)
Dim Dict As Object
Set Dict = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = 0 To UBound(arr)
Dict(CLng(arr(i))) = i + 1
Next
Dim TempArray As Variant
For Each CB In CBs
TempArray = Split(CB.Name, "_")
If UBound(TempArray) > 0 Then
TempArray(1) = Dict(CLng(TempArray(1)))
CB.Name = Join(TempArray, "_")
End If
Next
End Sub
Option Explicit
Private Const SelectedColor As Long = vbRed
Private Const SelectedTransparency As Double = 0.7
Enum SelectType
SelectAll = -10
DeselectAll
ListSelect
[_eLast]
End Enum
Private Sub GroupingButton_Click()
Dim i As Long
Dim GroupNumber As Long
GroupNumber = MaxGroupNumber + 1
For i = 0 To CBsListBox.ListCount - 1
If CBsListBox.Selected(i) Then
RenamedCB CBs.Item(i + 1), GroupNumber, CBsListBox.List(i, 1)
CBs.Item(i + 1).OnAction = "SetSingleSelect"
End If
Next
UpdateGroupNumber
ResetCBsListBox
End Sub
Private Sub GroupNumberComboBox_Change()
Dim SelectedGroupNumber As Long
If GroupNumberComboBox.Value <> vbNullString Then
SelectedGroupNumber = CLng(GroupNumberComboBox.Value)
Else
Exit Sub
End If
MultiSelectCheck.Value = True
SelectSameGroup SelectedGroupNumber
Application.EnableEvents = False
GroupNumberComboBox.Value = SelectedGroupNumber
Application.EnableEvents = True
End Sub
Private Sub GroupSelectButton_Click()
Dim GroupNumber As Long
On Error Resume Next
GroupNumber = CBsListBox.List(CBsListBox.ListIndex, 2)
If GroupNumber = 0 Then Exit Sub
MultiSelectCheck.Value = True
SelectSameGroup GroupNumber
End Sub
Private Sub SelectAllButton_Click()
Select Case SelectAllButton.Caption
Case "全選択"
MultiSelectCheck.Value = True
SelectSameGroup SelectAll
SelectAllButton.Caption = "全解除"
Case "全解除"
ResetCBsListBox
SelectAllButton.Caption = "全選択"
End Select
End Sub
Private Sub MultiSelectCheck_Change()
ResetCBsListBox
Select Case MultiSelectCheck.Value
Case True
CBsListBox.MultiSelect = fmMultiSelectMulti
Case False
CBsListBox.MultiSelect = fmMultiSelectSingle
SelectAllButton.Caption = "全選択"
End Select
CaptionChangeButton.Enabled = Not MultiSelectCheck.Value
GroupSelectButton.Enabled = Not MultiSelectCheck.Value
End Sub
Private Sub SelectedCBsDeleteButton_Click()
Dim i As Long
For i = CBsListBox.ListCount - 1 To 0 Step -1
If CBsListBox.Selected(i) = True Then
CBs.Item(i + 1).Delete
End If
Next
ResetCBsListBox
End Sub
Private Sub UserForm_Initialize()
IgnoreBlankCheck = True
ResetCBsListBox
End Sub
Private Sub UserForm_Terminate()
ResetCBsListBox
End Sub
Private Sub CloseButton_Click()
Unload Me
End Sub
Private Sub CBsListBox_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
SelectSameGroup ListSelect
If CBsListBox.MultiSelect = fmMultiSelectSingle Then
NewCaptionInputBox.SetFocus
End If
End Sub
Private Sub SetCheckBoxButton_Click()
Dim r As Range
For Each r In Selection
If IgnoreBlankCheck = False Or r.Value <> vbNullString Then
SetCheckBox r, , r.Value
r.ClearContents
End If
Next
ResetCBsListBox
End Sub
Private Sub CaptionChangeButton_Click()
Dim SelectedIndex As Long
SelectedIndex = CBsListBox.ListIndex
If SelectedIndex = -1 Then
MsgBox "対象となるCheckBoxが未選択です。"
Exit Sub
End If
CBs.Item(SelectedIndex + 1).Caption = NewCaptionInputBox.Value
NewCaptionInputBox.Value = vbNullString
ResetCBsListBox
CBsListBox.Selected(SelectedIndex) = True
End Sub
Private Sub NameChangeButton_Click()
Dim SelectedIndex As Long
SelectedIndex = CBsListBox.ListIndex
If SelectedIndex = -1 Then
MsgBox "対象となるCheckBoxが未選択です。"
Exit Sub
End If
Dim OldName As String
OldName = CBs.Item(SelectedIndex + 1).Name
Dim NameArray As Variant
NameArray = Split(OldName, "_")
Dim NewName As String
If UBound(NameArray) > 0 Then
ReDim Preserve NameArray(0 To 2)
NameArray(2) = NewNameInputBox.Value
NewName = Join(NameArray, "_")
Else
NewName = NewNameInputBox.Value
End If
CBs.Item(SelectedIndex + 1).Name = NewName
NewNameInputBox.Value = vbNullString
ResetCBsListBox
CBsListBox.Selected(SelectedIndex) = True
End Sub
Private Sub ResetCBsListBox()
CBsListBox.Clear
CBsListBox.List = CheckBoxList
GroupNumberComboBox.List = GroupList
GroupNumberComboBox.Value = vbNullString
SelectSameGroup DeselectAll
End Sub
Private Sub SelectSameGroup(group_number As SelectType)
Dim i As Long
Select Case group_number
Case SelectType.SelectAll
For i = 0 To CBsListBox.ListCount - 1
CBsListBox.Selected(i) = True
CBs.Item(i + 1).Interior.Color = SelectedColor
CBs.Item(i + 1).ShapeRange.Fill.Transparency = SelectedTransparency
Next
Case SelectType.DeselectAll
For i = 1 To CBs.Count
CBsListBox.Selected(i) = False
CBs.Item(i).Interior.Color = xlNone
Next
Case SelectType.ListSelect
For i = 0 To CBsListBox.ListCount - 1
With CBs.Item(i + 1)
If CBsListBox.Selected(i) Then
.Interior.Color = SelectedColor
.ShapeRange.Fill.Transparency = SelectedTransparency
Else
.Interior.Color = xlNone
End If
End With
Next
Case Else
If group_number <> 0 Then
For i = 0 To CBsListBox.ListCount - 1
If CBsListBox.List(i, 2) = group_number Then
CBsListBox.Selected(i) = True
CBs.Item(i + 1).Interior.Color = SelectedColor
CBs.Item(i + 1).ShapeRange.Fill.Transparency = SelectedTransparency
Else
CBsListBox.Selected(i) = False
CBs.Item(i + 1).Interior.Color = xlNone
End If
Next
End If
End Select
End Sub
Private Sub UpdateGroupNumber()
Dim TempDict As Object
Set TempDict = CreateObject("Scripting.Dictionary")
Dim CB As CheckBox
On Error Resume Next
For Each CB In CBs
TempDict(Split(CB.Name, "_")(1)) = 1
Next
On Error GoTo 0
Dim arr As Variant
arr = TempDict.keys
arr = SortArray(arr)
Dim Dict As Object
Set Dict = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = 0 To UBound(arr)
Dict(CLng(arr(i))) = i + 1
Next
Dim TempArray As Variant
For Each CB In CBs
TempArray = Split(CB.Name, "_")
If UBound(TempArray) > 0 Then
TempArray(1) = Dict(CLng(TempArray(1)))
CB.Name = Join(TempArray, "_")
End If
Next
End Sub
Private Property Get GroupList() As Variant
Dim Dict As Object
Set Dict = CreateObject("Scripting.Dictionary")
Dim i As Long
For i = 0 To CBsListBox.ListCount - 1
If IsNumeric(CBsListBox.List(i, 2)) Then
Dict(CBsListBox.List(i, 2)) = i
End If
Next
Dim arr As Variant
arr = Dict.keys
GroupList = SortArray(arr)
End Property
今後は何かあれば、こちらを更新することとする。
本シリーズは、これでおしまい。
参考まで。