Excel VBA練習 xlCheckBox

使い捨てマクロ

• セルのダブルクリックで文字☑をセルに挿入
• セルの右クリックで文字☐をセルに挿入
• Shapes.AddFormControl でワークシートに xlCheckBox を追加

' Excel 2019, Windows 11

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  Target.Value = VBA.Strings.ChrW(&H2611) & Target.Text
End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  Target.Value = VBA.Strings.ChrW(&H2610) & Target.Text
End Sub

Public Sub TestXlCheckBox()
  Dim xlsApp As Excel.Application
  Dim xlsWb As Excel.Workbook
  Dim xlsWs As Excel.Worksheet
  Dim xlsRng As Excel.Range
  Dim xlsShp As Excel.Shape

  Set xlsApp = New Excel.Application
  xlsApp.Visible = True

  Set xlsWb = xlsApp.Workbooks.Add(Template:=Excel.XlWBATemplate.xlWBATWorksheet)
  Set xlsWs = xlsWb.Sheets(1)

  Set xlsRng = xlsWs.Columns("B")
  xlsRng.ColumnWidth = 25

  Set xlsRng = xlsWs.Columns("C")
  xlsRng.ColumnWidth = 15

  Set xlsRng = xlsWs.Rows()
  xlsRng.RowHeight = 30


  Set xlsRng = xlsWs.Range("B2")
  Set xlsShp = xlsWs.Shapes.AddFormControl( _
    Type:=Excel.XlFormControl.xlCheckBox, _
    Left:=xlsRng.Left, Top:=xlsRng.Top, _
    Width:=xlsRng.Width, Height:=xlsRng.Height)

  With xlsShp
    .Name = "チェックボックス_001"
    .AlternativeText = "代替テキスト xlCheckBox xlOff"
    .TextFrame.Characters.Text = "チェックボックス オフ"
    .ControlFormat.Value = Excel.Constants.xlOff
    .ControlFormat.LinkedCell = "C2"
  End With


  Set xlsRng = xlsWs.Range("B4")
  Set xlsShp = xlsWs.Shapes.AddFormControl( _
    Type:=Excel.XlFormControl.xlCheckBox, _
    Left:=xlsRng.Left, Top:=xlsRng.Top, _
    Width:=xlsRng.Width, Height:=xlsRng.Height)

  With xlsShp
    .Name = "チェックボックス_002"
    .AlternativeText = "代替テキスト xlCheckBox xlOn"
    .TextFrame.Characters.Text = "チェックボックス オン"
    .ControlFormat.Value = Excel.Constants.xlOn
    .ControlFormat.LinkedCell = "C4"
  End With


  Set xlsRng = xlsWs.Range("B6")
  Set xlsShp = xlsWs.Shapes.AddFormControl( _
    Type:=Excel.XlFormControl.xlCheckBox, _
    Left:=xlsRng.Left, Top:=xlsRng.Top, _
    Width:=xlsRng.Width, Height:=xlsRng.Height)

  With xlsShp
    .Name = "チェックボックス_003"
    .AlternativeText = "代替テキスト xlCheckBox xlMixed"
    .TextFrame.Characters.Text = "チェックボックス 淡色表示"
    .ControlFormat.Value = Excel.Constants.xlMixed
    .ControlFormat.LinkedCell = "C6"
  End With

End Sub