
Excel VBAã§ãã§ãã¯ããã¯ã¹ä»ããªã¹ãããã¯ã¹ã䜿ãããïŒ ãªããªãäœã£ãŠããŸãã
- å²åŒãã
ä»é²ïŒãœãŒã¹ã³ãŒãã®ããŠã³ããŒã

Excel VBAã®ListBoxã¯è€æ°éžæã§ããŸããããã¡ãã¡CTRLããŒãæŒããªããã¯ãªãã¯ããã®ã¯é¢åã§ãããã
ãã£ã±ãããã§ãã¯ããã¯ã¹ä»ããªã¹ãããã¯ã¹ã®ã»ãã䜿ããããã
ã§ããExcel VBAã«ã¯æšæºã§çšæãããŠããŸãããèªåã§å®è£
ããã®ãæéã§ãã
ã ã£ãããã¢ãžã¥ãŒã«ãã€ã³ããŒãããã ãã§ããã䜿ãããã®ãããã§ããã
ãšããããšã§ãäœã£ãŠã¿ãŸããã
1. CheckListãšã¯ïŒ

ãCheckListãã¯ãExcel VBAã®UserFormã§äœ¿ããããã§ãã¯ããã¯ã¹ä»ãã®ä»®æ³ãªã¹ãããã¯ã¹ã§ãã
UserFormã«é 眮ããFrameã®äžã«ãCheckBoxãšLabelãèªåçæããŠãªã¹ãã衚瀺ããŸãã
ãã§ãã¯ããã¯ã¹ã®æäœãšè¡ã®éžæã¯ç¬ç«ããŠããã®ã§ããã§ãã¯ãä»ããã ãã§ãªããéåžžã®ListBoxã®ããã«è¡ãéžæããããšãã§ããŸãã
é ç®ã®è¿œå ã»åé€ããã§ãã¯ç¶æ ã®ååŸã»å€æŽãè€æ°éžæãã¹ã¯ããŒã«ã«ã察å¿ããŠããŸãã
䜿ãåŽã§å¿ èŠãªã®ã¯ãã¢ãžã¥ãŒã«ã®ã€ã³ããŒããšFrameã®é 眮ã ããããšã¯æ¬¡ã®ããã«åŒã³åºããŸãã
Call CheckList.Makev(Frame1)
Call CheckList.additemv("æ±äº¬")
Call CheckList.additemv("倧éª")
Call CheckList.additemv("åå€å±")
ããã§3é ç®ã®ãã§ãã¯ãªã¹ããã§ããŸãã
è€éãªã³ã³ãããŒã«çæãã€ãã³ãåŠçã¯ãCheckListåŽã§ç®¡çããŸãã
2. CheckListã®å°å ¥æ¹æ³

å¿ èŠãªã®ã¯ã次ã®2ã€ã®ãã¡ã€ã«ã§ãã
CheckList.basïŒãã§ãã¯ãªã¹ãæ¬äœ
CheckListItem.clsïŒããŠã¹ã€ãã³ããåŠçããã¯ã©ã¹
èšäºæ«å°Ÿã®ZIPãã¡ã€ã«ãè§£åããExcelã®VBEïŒAltïŒF11ïŒããããã¡ã€ã«ãâããã¡ã€ã«ã®ã€ã³ããŒããã§ã2ã€ãšãåã蟌ã¿ãŸãã
次ã«ãUserFormã«Frameã1ã€é 眮ããŸããåå㯠Frame1 ãšããŸãã
UserFormã® Initialize ã€ãã³ãã«ã次ã®ã³ãŒããèšè¿°ããŠãã ããã
Private Sub UserForm_Initialize()
Call CheckList.Makev(Frame1)
End Sub
ããã§æºåå®äºã§ãã
Makev ã¯ãæå®ããFrameããã§ãã¯ãªã¹ããšããŠåæåããåœä»€ã§ããFrameã«Captionããã£ãŠããªããŠã䜿çšã§ããŸãã
è€æ°è¡éžæãæå¹ã«ãããå Žåã¯ã次ã®ããã«ããŸãã
Call CheckList.Makev(Frame1, True)
ãªãããã§ãã¯ããã¯ã¹ã§è€æ°é ç®ã«ãã§ãã¯ãä»ããã ããªããTrue ã®æå®ã¯äžèŠã§ãããã®æå®ã¯ããã§ãã¯ç¶æ ãšã¯å¥ã®ãè¡éžæããè€æ°è¡ã«èš±å¯ããããã®ãã®ã§ãã
3. é ç®ã®è¿œå ã»ååŸã»å€æŽã»åé€

CheckListã®æäœã¯ãéåžžã®ListBoxãšåãããã«ãé ç®çªå·ãæå®ããŠè¡ããŸããé ç®çªå·ã¯ 0ããå§ãŸããŸãã
é ç®ã远å ãã
additemv ã§ããªã¹ãã®æ«å°Ÿã«é ç®ã远å ããŸãã
Call CheckList.additemv("æ±äº¬")
Call CheckList.additemv("倧éª")
Call CheckList.additemv("åå€å±")
ãã®å Žåããæ±äº¬ãã0çªãã倧éªãã1çªããåå€å±ãã2çªã«ãªããŸãã
é ç®ã®æååãååŸã»å€æŽãã
listv ã¯ãæååã®ååŸãšå€æŽã®äž¡æ¹ã«äœ¿ããŸãã
s = CheckList.listv(0) ' 0çªã®æååãååŸ
Call CheckList.listv(1, "京éœ") ' 1çªãã京éœãã«å€æŽ
é ç®ãåé€ãã
removev ã¯æå®ããé ç®ã ããåé€ããŸãã
Call CheckList.removev(1)
1çªã®ã京éœããåé€ãããåŸãã«ãã£ããåå€å±ãã1çªã«ç¹°ãäžãããŸãã
ãªã¹ããå šéšæ¶ãããå Žå㯠clearv ã§ãã
Call CheckList.clearv()
é ç®æ°ãååŸãã
çŸåšã®é ç®æ°ã¯ countv ã§ååŸã§ããŸãã
n = CheckList.countv()
clearv ã§å šé ç®ãåé€ããŠããFrameèªäœã¯ãã®ãŸãŸäœ¿çšã§ããåã³ additemv ã§é ç®ã远å ã§ããŸãã
4. ãã§ãã¯ããã¯ã¹ã®æäœ

CheckListã§äžçªãã䜿ãã®ãããã§ãã¯ããã¯ã¹ã®ON/OFFã§ãã
ãã§ãã¯ããã¯ã¹ã¯é ç®ããšã«ç¬ç«ããŠããã®ã§ãè€æ°ã®é ç®ã«èªç±ã«ãã§ãã¯ãä»ããããŸãã
ãã§ãã¯ãä»ããã»å€ã
checkedv ã«é ç®çªå·ãš True ãŸã㯠False ãæå®ããŸãã
Call CheckList.checkedv(0, True) ' 0çªããã§ãã¯
Call CheckList.checkedv(1, False) ' 1çªã®ãã§ãã¯ãå€ã
Call CheckList.checkedv(2, True) ' 2çªããã§ãã¯
ãã§ãã¯ç¶æ ãååŸãã
é ç®çªå·ã ããæå®ãããšããã§ãã¯ç¶æ ãååŸã§ããŸãã
b = CheckList.checkedv(0)
ãã§ãã¯ãããŠããã° TrueããããŠããªããã° False ãè¿ããŸãã
ãã§ãã¯ãããé ç®ããŸãšããŠååŸãã
å®éã«ã¯ããã§ãã¯ãããŠããé ç®ã ããåŠçãããããšãå€ãã§ãããã
äŸãã°ããã§ãã¯ãããé ç®åãé çªã«åãåºããªããæ¬¡ã®ããã«ããŸãã
Dim i As Long
For i = 0 To CheckList.countv() - 1
If CheckList.checkedv(i) Then
Debug.Print CheckList.listv(i)
End If
Next i
countv ã§é ç®æ°ãååŸããcheckedv ã§ãã§ãã¯ç¶æ ã調ã¹ãlistv ã§é ç®åãåãåºããŠããŸãã
ãã§ãã¯ããã¯ã¹ã䜿ãã ããªããCTRLããŒãSHIFTããŒãå¿ èŠãããŸããã ãã§ãã¯ãããé ç®ãã¯ãªãã¯ããã ãã§ãã
ãªãããã§ãã¯ç¶æ ãšè¡ã®éžæç¶æ ã¯å¥ã ã«ç®¡çããŠããŸãããã§ãã¯ãä»ããŠãããã®è¡ãéžæç¶æ ã«ãªãããã§ã¯ãããŸããã
5. è¡ã®éžæãšè€æ°éžæ

CheckListã§ã¯ããã§ãã¯ããã¯ã¹ã®ON/OFFãšã¯å¥ã«ããªã¹ãã®è¡ãéžæã§ããŸãã
ãã§ãã¯ããã¯ã¹ãã¯ãªãã¯ãããšãã§ãã¯ç¶æ ãå€ãããæååéšåãã¯ãªãã¯ãããšè¡ãéžæãããŸããéžæãããè¡ã¯èãéè²ã§è¡šç€ºãããŸãã
åäžéžæãšè€æ°éžæ
éåžžã¯åäžéžæã§ãããåæåæã« True ãæå®ãããšè€æ°éžæã«ãªããŸãã
Call CheckList.Makev(Frame1) ' åäžéžæ
Call CheckList.Makev(Frame1, True) ' è€æ°éžæ
è€æ°éžææã®æäœã¯æ¬¡ã®ãšããã§ãã

éžæãããŠããè¡ã調ã¹ã
ListIndex ã¯ãçŸåšéžæãããŠããæåã®è¡çªå·ãè¿ããŸããæªéžæã®å Žå㯠-1 ã§ãã
idx = CheckList.ListIndex()
è€æ°éžæã®å Žåã¯ãSelected ã䜿ã£ãŠåè¡ã調ã¹ãŸãã
Dim i As Long
For i = 0 To CheckList.countv() - 1
If CheckList.Selected(i) Then
Debug.Print CheckList.listv(i)
End If
Next i
éžæè¡æ°ã¯ SelCount ã§ååŸã§ããŸãã
n = CheckList.SelCount()
éžæã®è§£é€ãšããŠã¹ãã¿ã³
ããã°ã©ã ããè¡éžæããã¹ãŠè§£é€ããå Žåã¯ãClearSelect ã䜿çšããŸãã
Call CheckList.ClearSelect()
æåŸã«æäœããããŠã¹ãã¿ã³ã¯ MouseButton ã§ååŸã§ããŸãã
b = CheckList.MouseButton() ' å·Š=1ãå³=2
ãã®ããã«ããã§ãã¯ããé ç®ãååŸããå Žå㯠checkedvãéžæããè¡ãååŸããå Žå㯠Selected ãšãç®çã«ãã£ãŠäœ¿ãåããŸãã
6. è€æ°ã®ãã§ãã¯ãªã¹ãã䜿ã

CheckListã¯ã1ã€ã®UserFormã§è€æ°ã®ãã§ãã¯ãªã¹ãã䜿çšã§ããŸãã
äŸãã°ãFrame1ã«ã¯ãåŠç察象ããFrame2ã«ã¯ãåºå圢åŒãã衚瀺ãããå Žåã§ãã
ãããããåºå¥ããããã«ã管ççªå·ã䜿çšããŸãã
管ççªå·ãæå®ããŠåæåãã
Call CheckList.Makev(Frame1)
Call CheckList.Makev(Frame2, False, 1)
Frame1ã¯ç®¡ççªå·0ãFrame2ã¯ç®¡ççªå·1ã«ãªããŸãã
管ççªå·0ã¯çç¥ã§ãããããéåžžã¯æå®ããå¿ èŠããããŸããã
ããããã®ãªã¹ãã«é ç®ã远å ãã
' Frame1ïŒåŠç察象
Call CheckList.additemv("売äžããŒã¿")
Call CheckList.additemv("顧客ããŒã¿")
Call CheckList.additemv("åšåº«ããŒã¿")
' Frame2ïŒåºå圢åŒ
Call CheckList.additemv("Excel", 1)
Call CheckList.additemv("CSV", 1)
Call CheckList.additemv("PDF", 1)
åã0çªã®é ç®ã§ãã管ççªå·ã«ãã£ãŠå¥ã ã«æ±ãããŸãã
Call CheckList.checkedv(0, True) ' Frame1ã®0çª
Call CheckList.checkedv(0, True, 1) ' Frame2ã®0çª
ãã§ãã¯ç¶æ ã®ååŸãåæ§ã§ãã
b1 = CheckList.checkedv(0)
b2 = CheckList.checkedv(0, , 1)
é ç®ã®æååãååŸããå Žåã¯ã次ã®ããã«æå®ããŸãã
s1 = CheckList.listv(0)
s2 = CheckList.listv(0, 1)
管ççªå·ã¯æåŸã«æå®ãã
CheckListã®åã³ãã³ãã§ã¯ã管ççªå·ã¯åºæ¬çã«æåŸã®åŒæ°ã§ãã
äŸãã°ã管ççªå·1ã®ãªã¹ããæäœããå Žåã¯ã
Call CheckList.listv(0, "ããã¹ã圢åŒ", 1)
Call CheckList.removev(1, 1)
Call CheckList.clearv(1)
ãšèšè¿°ããŸãã
管ççªå·0ãªãçç¥ã2ã€ç®ä»¥éã®ãªã¹ãã§ã¯ç®¡ççªå·ãæå®ããã ãã®ã«ãŒã«ã ãèŠããŠããã°ãè€æ°ã®ãã§ãã¯ãªã¹ããç°¡åã«æ±ããŸãã
7. ãã§ãã¯ãªã¹ãã®å 容ãTextBoxãšããåããã

ãã§ãã¯ãªã¹ãã®å 容ããå¥ã®åŠçã§å©çšãããããšããããŸãã
ãããªãšãã«äŸ¿å©ãªã®ã ParaCB ã§ãã
ParaCB ã䜿ããšãCheckListã®å 容ãTextBoxã«åãåºããããTextBoxããCheckListãžæ»ãããã§ããŸãã
CheckListããTextBoxãž
UserFormã« TextBox1 ãé 眮ããŠã次ã®ããã«èšè¿°ããŸãã
Call CheckList.ParaCB(TextBox1, "â", 0)
管ççªå·0ã®CheckListã®å 容ãTextBoxã«åãåºããŸãã
TextBoxããCheckListãž
éã«ãTextBoxã®å 容ãCheckListãžæ»ãå Žåã¯ã次ã®ããã«ããŸãã
Call CheckList.ParaCB(TextBox1, "â", 0)
TextBoxã«åãåºãããšäœã䟿å©ïŒ
TextBoxã«å 容ãç§»ããã°ãæååãšããŠä¿æããããå¥ã®åŠçã«åãæž¡ããããããããªããŸãã
ä¿åçšã®ããŒã¿ãšããŠæ±ãå Žåã«ã䟿å©ã§ãã
ç»é¢ã«è¡šç€ºããå¿ èŠããªããã°ãTextBoxã® Visible ã False ã«ããŠããã°æ§ããŸããã
ãã§ãã¯ãªã¹ãå°çšã®ä¿ååŠçãæ¯åäœããªããŠããTextBoxãä»ããŠå å®¹ãæ±ããã
ãããCheckListã«çšæãã䟿å©ãªæ©èœã®ã²ãšã€ã§ãã
8. CheckListã䜿ããšãã®æ³šæç¹

CheckListã¯ãExcel VBAã®æšæºListBoxãã®ãã®ã§ã¯ãªããFrameäžã«ãã§ãã¯ããã¯ã¹ä»ãã®ãªã¹ããåçŸããã¢ãžã¥ãŒã«ã§ãã
åºæ¬çãªæäœã¯ç°¡åã§ãããããã€ã泚æç¹ããããŸãã
åæåã«ã¯Frameãæå®ãã
Makev ã«æž¡ãã®ã¯ãFrameã®ååã衚ãæååã§ã¯ãªããFrameãªããžã§ã¯ããã®ãã®ã§ãã
Call CheckList.Makev(Frame1)
åã管ççªå·ã§ Makev ãåå®è¡ãããšããã§ãã¯ãªã¹ãã¯åæåãããŸãã
ãã®ãããéåžžã¯UserFormã®åæåæã«äžåºŠã ãå®è¡ããŸãã
é ç®çªå·ã¯0ããå§ãŸã
æåã®é ç®ã¯0çªã次ã1çªã§ãã
ååšããªãé ç®çªå·ãæå®ãããšãšã©ãŒã«ãªããŸãã
ãŸããremovev ã§é ç®ãåé€ãããšãåŸç¶ã®é ç®çªå·ãèªåçã«è©°ããããŸãã
Frameå ã®æ¢åã³ã³ãããŒã«ã¯åé€ããªã
clearv ã§å šé ç®ãåé€ããŠãããããããFrameã«é 眮ããŠããLabelãOptionButtonãªã©ã¯åé€ãããŸããã
ãã ãããããã®æ¢åã³ã³ãããŒã«ã¯ãCheckListã®ã¯ãªãã¯æ€åºå¯Ÿè±¡ã§ã¯ãããŸããã
Frameã®å€ãã¯ãªãã¯ããŠãè¡éžæã¯è§£é€ãããªã
Frameå ã®ç©ºçœéšåãã¯ãªãã¯ãããšãè¡éžæãè§£é€ãããŸãã
äžæ¹ãFrameã®å€åŽãã¯ãªãã¯ããŠãéžæç¶æ ã¯ç¶æãããŸãã
ããã°ã©ã ããè§£é€ãããå Žåã¯ã次ã®ããã«ããŸãã
Call CheckList.ClearSelect()
UserFormãžã®ã¯ãªãã¯ã€ãã³ãéç¥ã¯æªå®è£
UserFormåŽã§ CheckList_Click ãšããã€ãã³ããåãåãä»çµã¿ã¯ãçŸæç¹ã§ã¯å®è£ ããŠããŸããã
ãã§ãã¯ç¶æ ãè¡éžæç¶æ ã¯ãcheckedvãSelectedãListIndex ãªã©ã®ã³ãã³ãã§ååŸããŠãã ããã
ãã®ç¹ãé€ãã°ãéåžžã®ãã§ãã¯ãªã¹ãæäœã«å¿ èŠãªåŠçã¯CheckListåŽã«ãŸãšããŠããŸãã
9. ãŸãšã

Excel VBAã®æšæºListBoxã§ãè€æ°éžæã¯ã§ããŸããããã§ãã¯ããã¯ã¹ããã£ãã»ãããã©ã®é ç®ãéžãã§ããã®ãåããããããªããŸãã
ä»åäœæãããCheckListãã¯ããã®äœ¿ããããããæšæºã®FrameãšVBAã ãã§å®çŸããããã®ãã®ã§ãã
äžåºŠã¢ãžã¥ãŒã«ãã€ã³ããŒãããã°ãããšã¯Frameãé 眮ã㊠Makev ã§åæåããã ããé ç®ã®è¿œå ãåé€ããã§ãã¯ç¶æ ã®ååŸããç°¡åãªåœä»€ã§æäœã§ããŸãã
䜿ããã³ã«ãã§ãã¯ããã¯ã¹ãã€ãã³ãåŠçãäœãçŽãå¿ èŠã¯ãããŸããã
ãœãŒã¹ã³ãŒãã¯ã次ã®2ãã¡ã€ã«ãZIP圢åŒã«ãŸãšããŠé åžããŸãã
CheckList.bas
CheckListItem.cls
èªåã®UserFormã«ããæ°è»œã«çµã¿èŸŒãã§äœ¿ã£ãŠã¿ãŠãã ããã
åèã«ããèšäºã»è³æïŒ
Microsoft Learn
+2
Microsoft LearnïŒMultiSelectãSelectedããããã£ã®äœ¿çšäŸ
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/multiselect-selected-properties-example
Microsoft LearnïŒCheckBoxã³ã³ãããŒã«
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/checkbox-control
Microsoft LearnïŒAddã¡ãœããïŒMicrosoft FormsïŒ
https://learn.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/add-method-microsoft-forms
Microsoft LearnïŒSelectedããããã£
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/selected-property
Microsoft LearnïŒUserFormã«ã³ã³ãããŒã«ã远å ãã
https://learn.microsoft.com/en-us/office/vba/excel/concepts/controls-dialogboxes-forms/add-controls-to-a-user-form
ä»é²ïŒãœãŒã¹ã³ãŒãã®ããŠã³ããŒã

CheckListã®ãœãŒã¹ã³ãŒãã¯ãZIP圢åŒã§ãŸãšããŠé åžããŸãã
ZIPãã¡ã€ã«ã«ã¯ã次ã®2ã€ã®ã¢ãžã¥ãŒã«ãåé²ããŸãã

ZIPãã¡ã€ã«ãè§£åããExcelã®VBEãã2ã€ã®ãã¡ã€ã«ãã€ã³ããŒãããŠãã ããã
詳ããå°å ¥æ¹æ³ãšåã³ãã³ãã®äœ¿ãæ¹ã¯ãæ¬æã®èª¬æãåç §ããŠãã ããã