{"id":5742,"date":"2023-05-07T19:44:50","date_gmt":"2023-05-07T10:44:50","guid":{"rendered":"https:\/\/okamurax.com\/?p=5742"},"modified":"2023-05-07T19:56:39","modified_gmt":"2023-05-07T10:56:39","slug":"vba-%e8%b2%a9%e5%a3%b2%e7%ae%a1%e7%90%86","status":"publish","type":"post","link":"https:\/\/appbay.org\/?p=5742","title":{"rendered":"VBA \u8ca9\u58f2\u7ba1\u7406"},"content":{"rendered":"<p>Excel\u3067\u8ca9\u58f2\u7ba1\u7406\u3092\u4f5c\u6210\u3057\u3066\u307f\u305f\u3002<\/p>\n<p><iframe loading=\"lazy\" title=\"VBA \u8ca9\u58f2\u7ba1\u7406\" width=\"525\" height=\"295\" src=\"https:\/\/www.youtube.com\/embed\/Cgx7s00N5Ig?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<p>UserForm1<\/p>\n<pre class=\"lang:vb decode:true\">Private WithEvents e As Application\r\n\r\nSub e_SheetSelectionChange(ByVal w As Object, ByVal target As Range)\r\n\r\nIf ToggleButton1.Value = False Then Exit Sub\r\nIf ActiveSheet.Name &lt;&gt; \"\u53d7\u6ce8\u5165\u529b\" Then Exit Sub\r\n\r\nr = target.Item(1).Row\r\n\r\nTextBox47.Text = r ' \u884c\r\nTextBox19.Text = w.Cells(r, 1).Value ' \u53d7\u6ce8\u65e5\r\nTextBox53.Text = w.Cells(r, 2).Value ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\nTextBox1.Text = w.Cells(r, 3).Value ' \u5f97\u610f\u5148\r\nTextBox52.Text = w.Cells(r, 4).Value ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\nTextBox2.Text = w.Cells(r, 5).Value ' \u54c1\u540d\r\nTextBox3.Text = w.Cells(r, 6).Value ' \u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\r\nTextBox4.Text = w.Cells(r, 7).Value ' \u5de5\u756a\r\nTextBox5.Text = w.Cells(r, 8).Value ' \u6570\u91cf\r\nTextBox6.Text = w.Cells(r, 9).Value ' \u5358\u4fa1\r\nTextBox7.Text = w.Cells(r, 10).Value  ' \u91d1\u984d\r\nTextBox48.Text = w.Cells(r, 11).Value ' \u6ce8\u6587\u756a\u53f7\r\nTextBox11.Text = w.Cells(r, 12).Value ' \u7d0d\u671f\r\nTextBox12.Text = w.Cells(r, 13).Value ' \u5099\u8003\r\n\r\nEnd Sub\r\n\r\n' --------------------------------------------------------\r\n' \u53d7\u6ce8\u5165\u529b\r\n' --------------------------------------------------------\r\n\r\nPrivate Sub CommandButton7_Click() ' \u30af\u30ea\u30a2\r\n\r\nCall TextBoxClear\r\nCall TextBoxInitialize\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton5_Click() ' \u524a\u9664\r\n\r\nIf TextBox47.Text = \"\" Then\r\n  MsgBox \"\u5bfe\u8c61\u306e\u884c\u304c\u9078\u629e\u3055\u308c\u3066\u3044\u307e\u305b\u3093\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\n' \u78ba\u8a8d\r\n\r\nIf MsgBox(\"\u672c\u5f53\u306b\u524a\u9664\u3057\u307e\u3059\u304b\uff1f\", vbYesNo) = vbNo Then Exit Sub\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\nSet t = ThisWorkbook.Sheets(\"\u66f4\u65b0\u524a\u9664\u5c65\u6b74\")\r\n\r\n' \u6700\u7d42\u884c\u53d6\u5f97\r\n\r\nr = 2\r\nDo While t.Cells(r, 1).Value &lt;&gt; \"\"\r\n  r = r + 1\r\nLoop\r\n\r\ntarget_row = r\r\n\r\n' \u5c65\u6b74\u3078\u8907\u5199\r\n\r\nr = TextBox47.Text\r\n\r\nt.Range(\"A\" &amp; target_row).Value = \"\u524a\u9664\" ' A\u5217\u3067\u6700\u7d42\u884c\u3092\u628a\u63e1\u3057\u3066\u3044\u308b\u306e\u3067\u5fc5\u9808\r\nt.Range(\"B\" &amp; target_row).Value = Format(Now, \"yyyy\/mm\/dd\")\r\nt.Range(\"C\" &amp; target_row &amp; \":S\" &amp; target_row).Value = w.Range(\"A\" &amp; r &amp; \":Q\" &amp; r).Value\r\n\r\n' \u524a\u9664\r\n\r\nw.Rows(r).Delete\r\nTextBox47.Text = \"\"\r\n\r\nMsgBox \"\u524a\u9664\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton6_Click() ' \u66f4\u65b0\r\n\r\n' \u30c1\u30a7\u30c3\u30af\r\n\r\nIf TextBox47.Text = \"\" Then\r\n  MsgBox \"\u5bfe\u8c61\u306e\u884c\u304c\u9078\u629e\u3055\u308c\u3066\u3044\u307e\u305b\u3093\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nIf TextBox19.Text = \"\" Or TextBox4.Text = \"\" Then\r\n  MsgBox \"\u5165\u529b\u304c\u4e0d\u8db3\u3057\u3066\u3044\u307e\u3059\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\nSet t = ThisWorkbook.Sheets(\"\u66f4\u65b0\u524a\u9664\u5c65\u6b74\")\r\n\r\n' \u6700\u7d42\u884c\u53d6\u5f97\r\n\r\nr = 2\r\nDo While t.Cells(r, 1).Value &lt;&gt; \"\"\r\n  r = r + 1\r\nLoop\r\n\r\ntarget_row = r\r\n\r\n' \u5c65\u6b74\u3078\u8907\u5199\r\n\r\nr = TextBox47.Text\r\n\r\nt.Range(\"A\" &amp; target_row).Value = \"\u66f4\u65b0\" ' A\u5217\u3067\u6700\u7d42\u884c\u3092\u628a\u63e1\u3057\u3066\u3044\u308b\u306e\u3067\u5fc5\u9808\r\nt.Range(\"B\" &amp; target_row).Value = Format(Now, \"yyyy\/mm\/dd\")\r\nt.Range(\"C\" &amp; target_row &amp; \":S\" &amp; target_row).Value = w.Range(\"A\" &amp; r &amp; \":Q\" &amp; r).Value\r\n\r\n' \u66f8\u304d\u8fbc\u307f\r\n\r\nr = TextBox47.Text\r\n\r\nw.Cells(r, 1).Value = TextBox19.Text ' \u53d7\u6ce8\u65e5\r\nw.Cells(r, 2).Value = TextBox53.Text ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\nw.Cells(r, 3).Value = TextBox1.Text  ' \u5f97\u610f\u5148\r\nw.Cells(r, 4).Value = TextBox52.Text ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\nw.Cells(r, 5).Value = TextBox2.Text  ' \u54c1\u540d\r\nw.Cells(r, 6).Value = TextBox3.Text ' \u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\r\nw.Cells(r, 7).Value = TextBox4.Text ' \u5de5\u756a\r\nw.Cells(r, 8).Value = TextBox5.Text ' \u6570\u91cf\r\nw.Cells(r, 9).Value = TextBox6.Text ' \u5358\u4fa1\r\nw.Cells(r, 10).Value = TextBox7.Text ' \u91d1\u984d\r\nw.Cells(r, 11).Value = TextBox48.Text ' \u6ce8\u6587\u756a\u53f7\r\nw.Cells(r, 12).Value = TextBox11.Text ' \u7d0d\u671f\r\nw.Cells(r, 13).Value = TextBox12.Text ' \u5099\u8003\r\n\r\nMsgBox \"\u66f4\u65b0\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton8_Click() ' \u767b\u9332\/\u5de5\u756a\u56fa\u5b9a\r\n\r\nRegisterOrder (False)\r\n\r\nEnd Sub\r\n\r\nPrivate Sub CommandButton1_Click() ' \u767b\u9332\r\n\r\nRegisterOrder (True)\r\n\r\nEnd Sub\r\n\r\nSub RegisterOrder(countup_serial)\r\n\r\n' \u672a\u5b9a\u306e\u5185\u5bb9\u3082\u3042\u308b\u305f\u3081\u767b\u9332\u6642\u306f\u53d7\u6ce8\u65e5\u3068\u5de5\u756a\u306e\u307f\u5fc5\u9808\r\n\r\nIf TextBox19.Text = \"\" Or TextBox4.Text = \"\" Then\r\n  MsgBox \"\u5165\u529b\u304c\u4e0d\u8db3\u3057\u3066\u3044\u307e\u3059\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\n\r\n' \u884c\u672b\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n  r = r + 1\r\nLoop\r\n\r\n' \u66f8\u304d\u8fbc\u307f\r\n\r\nw.Cells(r, 1).Value = TextBox19.Text ' \u53d7\u6ce8\u65e5\r\nw.Cells(r, 2).Value = TextBox53.Text ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\nw.Cells(r, 3).Value = TextBox1.Text  ' \u5f97\u610f\u5148\r\nw.Cells(r, 4).Value = TextBox52.Text ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\nw.Cells(r, 5).Value = TextBox2.Text  ' \u54c1\u540d\r\nw.Cells(r, 6).Value = TextBox3.Text ' \u578b\u5f0f\r\nw.Cells(r, 7).Value = TextBox4.Text ' \u5de5\u756a\r\nw.Cells(r, 8).Value = TextBox5.Text ' \u6570\u91cf\r\nw.Cells(r, 9).Value = TextBox6.Text ' \u5358\u4fa1\r\nw.Cells(r, 10).Value = TextBox7.Text ' \u91d1\u984d\r\nw.Cells(r, 11).Value = TextBox48.Text ' \u6ce8\u6587\u756a\u53f7\r\nw.Cells(r, 12).Value = TextBox11.Text ' \u7d0d\u5165\u65e5\r\nw.Cells(r, 13).Value = TextBox12.Text ' \u5099\u8003\r\n\r\n' \u5de5\u756a\r\n\r\nSheets(\"\u8a2d\u5b9a\").Range(\"A2\") = TextBox4.Text\r\nIf countup_serial = True Then TextBox4.Text = CLng(TextBox4.Text) + 1\r\n\r\n' \u30a2\u30af\u30c6\u30a3\u30d6\r\n\r\nw.Activate\r\nw.Range(\"A\" &amp; r &amp; \":Q\" &amp; r).Select\r\n\r\nEnd Sub\r\n\r\nPrivate Sub MultiPage1_Change()\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox21_Change()\r\n\r\n' \u5f97\u610f\u5148\u30de\u30b9\u30bf\r\n\r\nListBox1.Clear\r\n\r\nSet w = Sheets(\"\u5f97\u610f\u5148\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  If InStr(LCase(w.Cells(r, 2).Value), LCase(TextBox21.Text)) &gt; 0 Then\r\n  \r\n    ListBox1.AddItem\r\n    ListBox1.List(ListBox1.ListCount - 1, 0) = w.Cells(r, 2).Value\r\n    ListBox1.List(ListBox1.ListCount - 1, 1) = w.Cells(r, 1).Value ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\n    \r\n  End If\r\n\r\nr = r + 1\r\nLoop\r\n \r\nEnd Sub\r\n\r\nPrivate Sub ListBox1_Click() ' \u5f97\u610f\u5148\u30de\u30b9\u30bf\r\n\r\nTextBox1.Text = ListBox1.List(ListBox1.ListIndex, 0)\r\nTextBox53.Text = ListBox1.List(ListBox1.ListIndex, 1)\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox20_Change()\r\n\r\n' \u54c1\u540d\u578b\u5f0f\u30de\u30b9\u30bf\r\n\r\nListBox2.Clear\r\n\r\nSet w = Sheets(\"\u54c1\u540d\u30fb\u578b\u5f0f\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  If InStr(LCase(w.Cells(r, 2).Value &amp; \" \" &amp; w.Cells(r, 3).Value), LCase(TextBox20.Text)) &gt; 0 Then\r\n  \r\n    ListBox2.AddItem\r\n    ListBox2.List(ListBox2.ListCount - 1, 0) = w.Cells(r, 2).Value\r\n    ListBox2.List(ListBox2.ListCount - 1, 1) = w.Cells(r, 3).Value\r\n    ListBox2.List(ListBox2.ListCount - 1, 2) = w.Cells(r, 4).Value\r\n    ListBox2.List(ListBox2.ListCount - 1, 3) = w.Cells(r, 1).Value ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\n    \r\n  End If\r\n\r\nr = r + 1\r\nLoop\r\n \r\nEnd Sub\r\n\r\nPrivate Sub ListBox2_Click() ' \u54c1\u540d\u578b\u5f0f\u30de\u30b9\u30bf\r\n\r\nTextBox2.Text = ListBox2.List(ListBox2.ListIndex, 0)\r\nTextBox3.Text = ListBox2.List(ListBox2.ListIndex, 1)\r\nTextBox6.Text = ListBox2.List(ListBox2.ListIndex, 2)\r\nTextBox52.Text = ListBox2.List(ListBox2.ListIndex, 3) ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\n\r\nTotalAmount ' \u5358\u4fa1\u304c\u5909\u308f\u308b\u305f\u3081\u547c\u3076\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u6570\u91cf\r\n\r\nIf IsNumeric(TextBox5.Text) Then\r\n  TextBox5.Text = CDec(TextBox5.Text)\r\nElse\r\n  TextBox5.Text = \"\"\r\nEnd If\r\n\r\nCall TotalAmount\r\n\r\nEnd Sub\r\n \r\nPrivate Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u5358\u4fa1\r\n\r\nIf IsNumeric(TextBox6.Text) Then\r\n  TextBox6.Text = Format(CDec(TextBox6.Text), \"#,###\")\r\nElse\r\n  TextBox6.Text = \"\"\r\nEnd If\r\n\r\nCall TotalAmount\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u91d1\u984d\r\n\r\n' \u624b\u5165\u529b\u3059\u308b\u5834\u5408\r\n\r\nIf IsNumeric(TextBox7.Text) Then\r\n  TextBox7.Text = Format(CDec(TextBox7.Text), \"#,###\")\r\nElse\r\n  TextBox7.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nSub TotalAmount()\r\n\r\nOn Error GoTo exception\r\n\r\nTextBox7.Text = Format(CDec(TextBox5.Text) * CDec(TextBox6.Text), \"#,###\") ' \u6570\u91cf\u00d7\u5358\u4fa1\r\n\r\nExit Sub\r\nexception:\r\n\r\nTextBox7.Text = \"\"\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox19_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u53d7\u6ce8\u65e5\r\n\r\nIf IsDate(TextBox19.Text) Then\r\n  TextBox19.Text = Format(TextBox19.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox19.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox11_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u7d0d\u5165\u65e5\r\n\r\nIf IsDate(TextBox11.Text) Then\r\n  TextBox11.Text = Format(TextBox11.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox11.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\n' --------------------------------------------------------\r\n' \u51fa\u8377\u4e88\u5b9a\u8868\r\n' --------------------------------------------------------\r\n\r\nPrivate Sub CommandButton2_Click()\r\n\r\nIf TextBox22.Text = \"\" Then Exit Sub\r\nIf TextBox23.Text = \"\" Then Exit Sub\r\n\r\nCall create_shipping(TextBox22.Text, TextBox23.Text)\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox22_Exit(ByVal Cancel As MSForms.ReturnBoolean)\r\n\r\nIf IsDate(TextBox22.Text) Then\r\n  TextBox22.Text = Format(TextBox22.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox22.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox23_Exit(ByVal Cancel As MSForms.ReturnBoolean)\r\n\r\nIf IsDate(TextBox23.Text) Then\r\n  TextBox23.Text = Format(TextBox23.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox23.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\n' --------------------------------------------------------\r\n' \u5de5\u6570\u5165\u529b\r\n' --------------------------------------------------------\r\n\r\nPrivate Sub CommandButton3_Click()\r\n\r\n' \u30c1\u30a7\u30c3\u30af\r\n\r\nchk = True\r\nIf TextBox37.Text = \"\" Then chk = False ' \u767b\u9332\u65e5\r\nIf TextBox54.Text = \"\" Then chk = False ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\nIf TextBox32.Text = \"\" Then chk = False ' \u5f97\u610f\u5148\r\nIf TextBox55.Text = \"\" Then chk = False ' \u54c1\u540d\u30b3\u30fc\u30c9\r\nIf TextBox31.Text = \"\" Then chk = False ' \u54c1\u540d\r\nIf TextBox30.Text = \"\" Then chk = False ' \u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\r\nIf TextBox29.Text = \"\" Then chk = False ' \u5de5\u756a\r\nIf ComboBox1.Text = \"\" Then chk = False ' \u4f5c\u696d\u8005\r\nIf TextBox33.Text = \"\" Then chk = False ' \u4f5c\u696d\u65e5\r\nIf TextBox34.Text = \"\" Then chk = False ' \u5de5\u6570\r\nIf ComboBox2.Text = \"\" Then chk = False ' \u4f5c\u696d\u5185\u5bb9\r\n\r\nIf False = chk Then\r\n  MsgBox \"\u5165\u529b\u306b\u4e0d\u8db3\u304c\u3042\u308a\u307e\u3059\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nSet w = Sheets(\"\u5de5\u6570\u5165\u529b\")\r\n\r\n' \u884c\u672b\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n  r = r + 1\r\nLoop\r\n\r\n' \u66f8\u304d\u8fbc\u307f\r\n\r\nw.Cells(r, 1).Value = TextBox37.Text ' \u767b\u9332\u65e5\r\nw.Cells(r, 2).Value = TextBox54.Text ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\nw.Cells(r, 3).Value = TextBox32.Text ' \u5f97\u610f\u5148\r\nw.Cells(r, 4).Value = TextBox55.Text ' \u54c1\u540d\u30b3\u30fc\u30c9\r\nw.Cells(r, 5).Value = TextBox31.Text ' \u54c1\u540d\r\nw.Cells(r, 6).Value = TextBox30.Text ' \u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\r\nw.Cells(r, 7).Value = TextBox29.Text ' \u5de5\u756a\r\nw.Cells(r, 8).Value = ComboBox1.Text  ' \u4f5c\u696d\u8005\r\nw.Cells(r, 9).Value = TextBox33.Text ' \u4f5c\u696d\u65e5\r\nw.Cells(r, 10).Value = TextBox34.Text ' \u5de5\u6570\r\nw.Cells(r, 12).Value = TextBox35.Text ' \u5099\u8003\r\n\r\n' \u4f5c\u696d\u5185\u5bb9\r\nIf ComboBox2.Text = \"\u305d\u306e\u4ed6\" Then\r\n  w.Cells(r, 11).Value = TextBox36.Text\r\n  If w.Cells(r, 11).Value = \"\" Then w.Cells(r, 11).Value = \"\u305d\u306e\u4ed6\"\r\nElse\r\n  w.Cells(r, 11).Value = ComboBox2.Text\r\nEnd If\r\n\r\n' \u30a2\u30af\u30c6\u30a3\u30d6\r\n\r\nw.Activate\r\nw.Range(\"A\" &amp; r &amp; \":L\" &amp; r).Select\r\n\r\nMsgBox \"\u767b\u9332\u3057\u307e\u3057\u305f\u3002\"\r\n\r\nEnd Sub\r\n\r\nPrivate Sub ComboBox2_Change()\r\n\r\nIf ComboBox2.Text = \"\u305d\u306e\u4ed6\" Then\r\n  TextBox36.Enabled = True\r\n  TextBox36.BackColor = vbWindowBackground\r\n\r\nElse\r\n  TextBox36.Text = \"\"\r\n  TextBox36.Enabled = False\r\n  TextBox36.BackColor = vbMenuBar\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub ListBox3_Click()\r\n\r\nTextBox54.Text = ListBox3.List(ListBox3.ListIndex, 4) '\u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\nTextBox32.Text = ListBox3.List(ListBox3.ListIndex, 0) ' \u5f97\u610f\u5148\r\nTextBox55.Text = ListBox3.List(ListBox3.ListIndex, 5) ' \u54c1\u540d\u30b3\u30fc\u30c9\r\nTextBox31.Text = ListBox3.List(ListBox3.ListIndex, 1) ' \u54c1\u540d\r\nTextBox30.Text = ListBox3.List(ListBox3.ListIndex, 2) ' \u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\r\nTextBox29.Text = ListBox3.List(ListBox3.ListIndex, 3) ' \u5de5\u756a\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox33_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u4f5c\u696d\u65e5\r\n\r\nIf IsDate(TextBox33.Text) Then\r\n  TextBox33.Text = Format(TextBox33.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox33.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox34_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u5de5\u6570\r\n\r\nIf IsNumeric(TextBox34.Text) Then\r\n  \r\n  TextBox34.Text = Format(TextBox34.Text, \"#.#0\")\r\n\r\n  If CCur(TextBox34.Text) * 100 Mod 25 &lt;&gt; 0 Then TextBox34.Text = \"\"\r\n  \r\nElse\r\n  TextBox34.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\n' --------------------------------------------------------\r\n' \u8acb\u6c42\u66f8\r\n' --------------------------------------------------------\r\n\r\nPrivate Sub CommandButton4_Click() ' \u8acb\u6c42\u66f8\u767a\u884c\r\n\r\nCall create_invoice\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox40_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u767a\u884c\u65e5\r\n\r\nIf IsDate(TextBox40.Text) Then\r\n  TextBox40.Text = Format(TextBox40.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox40.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox41_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u5bfe\u8c61\u65e5(\u958b\u59cb)\r\n\r\nIf IsDate(TextBox41.Text) Then\r\n  TextBox41.Text = Format(TextBox41.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox41.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox46_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u5bfe\u8c61\u65e5(\u7d42\u4e86)\r\n\r\nIf IsDate(TextBox46.Text) Then\r\n  TextBox46.Text = Format(TextBox46.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox46.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox44_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u652f\u6255\u671f\u9650\r\n\r\nIf IsDate(TextBox44.Text) Then\r\n  TextBox44.Text = Format(TextBox44.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox44.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nPrivate Sub ListBox4_Click()\r\n\r\nTextBox42.Text = ListBox4.List(ListBox4.ListIndex, 0) ' \u5f97\u610f\u5148\r\nTextBox50.Text = ListBox4.List(ListBox4.ListIndex, 1) ' \u7de0\u65e5\r\nTextBox51.Text = ListBox4.List(ListBox4.ListIndex, 2) ' \u652f\u6255\u4e88\u5b9a(\u7d4c\u904e\u65e5\u6570)\r\n\r\nCall GetInvoiceTerm\r\n \r\nEnd Sub\r\n\r\n' \u5f97\u610f\u5148\u7de0\u65e5 OR \u652f\u6255\u4e88\u5b9a(\u7d4c\u904e\u65e5\u6570) OR \u7de0\u65e5(\u5e74\u6708\u65e5) \u304c\u5909\u5316\u3057\u305f\u3089\u8acb\u6c42\u671f\u9593\u306e\u81ea\u52d5\u8a08\u7b97\r\n\r\nPrivate Sub TextBox50_Exit(ByVal Cancel As MSForms.ReturnBoolean)  ' \u5f97\u610f\u5148\u7de0\u65e5\r\n\r\nCall GetInvoiceTerm\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox51_Exit(ByVal Cancel As MSForms.ReturnBoolean)  ' \u652f\u6255\u4e88\u5b9a(\u7d4c\u904e\u65e5\u6570)\r\n\r\nCall GetInvoiceTerm\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox49_Exit(ByVal Cancel As MSForms.ReturnBoolean) ' \u7de0\u65e5(\u5e74\u6708\u65e5)\r\n\r\nCall GetInvoiceTerm\r\n\r\n' \u30d5\u30a9\u30fc\u30de\u30c3\u30c8\u51e6\u7406\r\n\r\nIf IsDate(TextBox49.Text) Then\r\n  TextBox49.Text = Format(TextBox49.Text, \"yyyy\/mm\/dd\")\r\nElse\r\n  TextBox49.Text = \"\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\n' \u3053\u3053\u307e\u3067\r\n\r\nPrivate Sub TextBox27_Change()\r\n\r\n' \u30a4\u30f3\u30af\u30ea\u30e1\u30f3\u30bf\u30eb\u691c\u7d22 (\u54c1\u540d\u578b\u5f0f\u30de\u30b9\u30bf)\r\n\r\nListBox3.Clear\r\n\r\nSet w = Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  haystack = LCase(w.Cells(r, 2).Value &amp; \" \" &amp; w.Cells(r, 3).Value &amp; \" \" &amp; w.Cells(r, 4).Value &amp; \" \" &amp; w.Cells(r, 5).Value)\r\n  needle = LCase(TextBox27.Text)\r\n  \r\n  If InStr(haystack, needle) &gt; 0 Then\r\n  \r\n    ListBox3.AddItem ' \u5f97\u610f\u5148\u3001\u54c1\u540d\u3001\u578b\u5f0f\u3001\u5de5\u756a\r\n    \r\n    ListBox3.List(ListBox3.ListCount - 1, 0) = w.Cells(r, 2).Value\r\n    ListBox3.List(ListBox3.ListCount - 1, 1) = w.Cells(r, 3).Value\r\n    ListBox3.List(ListBox3.ListCount - 1, 2) = w.Cells(r, 4).Value\r\n    ListBox3.List(ListBox3.ListCount - 1, 3) = w.Cells(r, 5).Value\r\n  \r\n  End If\r\n  \r\nr = r + 1\r\nLoop\r\n\r\nListBox3.ColumnCount = 4\r\n\r\nEnd Sub\r\n\r\nPrivate Sub TextBox38_Change()\r\n\r\n' \u30a4\u30f3\u30af\u30ea\u30e1\u30f3\u30bf\u30eb\u691c\u7d22 (\u5f97\u610f\u5148\u30de\u30b9\u30bf)\r\n\r\nListBox4.Clear\r\n\r\nSet w = Sheets(\"\u5f97\u610f\u5148\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  If InStr(LCase(w.Cells(r, 2).Value), LCase(TextBox38.Text)) &gt; 0 Then\r\n  \r\n    ListBox4.AddItem\r\n    ListBox4.List(ListBox4.ListCount - 1, 0) = w.Cells(r, 2).Value\r\n    ListBox4.List(ListBox4.ListCount - 1, 1) = w.Cells(r, 3).Value\r\n    ListBox4.List(ListBox4.ListCount - 1, 2) = w.Cells(r, 4).Value\r\n    \r\n  End If\r\n\r\nr = r + 1\r\nLoop\r\n\r\nEnd Sub\r\n\r\nSub TextBoxClear()\r\n\r\n  TextBox19.Text = \"\" ' \u53d7\u6ce8\u65e5\r\n  TextBox47.Text = \"\" ' \u884c\r\n  TextBox53.Text = \"\" ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\n  TextBox1.Text = \"\" ' \u5f97\u610f\u5148\r\n  TextBox52.Text = \"\" ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\n  TextBox2.Text = \"\" ' \u54c1\u540d\r\n  TextBox3.Text = \"\" ' \u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\r\n  TextBox4.Text = \"\" ' \u5de5\u756a\r\n  TextBox5.Text = \"\" ' \u6570\u91cf\r\n  TextBox6.Text = \"\" ' \u5358\u4fa1\r\n  TextBox7.Text = \"\" ' \u91d1\u984d\r\n  TextBox11.Text = \"\" ' \u7d0d\u671f\r\n  TextBox48.Text = \"\" ' \u6ce8\u6587\u756a\u53f7\r\n  TextBox12.Text = \"\" ' \u5099\u8003\r\n  \r\nEnd Sub\r\n\r\nSub TextBoxInitialize()\r\n\r\n  TextBox19.Text = Format(Now, \"yyyy\/mm\/dd\") ' \u53d7\u6ce8\u65e5\r\n  TextBox4.Text = CLng(Sheets(\"\u8a2d\u5b9a\").Range(\"A2\")) + 1 ' \u5de5\u756a\r\n  \r\nEnd Sub\r\n\r\nPrivate Sub ToggleButton1_Click()\r\n\r\nIf ActiveSheet.Name &lt;&gt; \"\u53d7\u6ce8\u5165\u529b\" Then Sheets(\"\u53d7\u6ce8\u5165\u529b\").Activate\r\n\r\nIf ToggleButton1.Value = True Then ' \u66f4\u65b0\/\u524a\u9664 \u53ef\u80fd\u72b6\u614b\r\n\r\n  Call TextBoxClear\r\n  \r\n  ' Enabled\r\n  \r\n  CommandButton5.Enabled = True ' \u524a\u9664\r\n  CommandButton6.Enabled = True ' \u66f4\u65b0\r\n  CommandButton7.Enabled = False ' \u30af\u30ea\u30a2\r\n  CommandButton1.Enabled = False ' \u767b\u9332\r\n  CommandButton8.Enabled = False ' \u767b\u9332(\u56fa\u5b9a)\r\n  \r\n  TextBox47.Enabled = True ' \u884c\r\n  TextBox4.Enabled = True ' \u5de5\u756a\r\n\r\nElse ' \u901a\u5e38\u72b6\u614b\r\n\r\n  Call TextBoxClear\r\n\r\n  ' Enabled\r\n  \r\n  CommandButton5.Enabled = False ' \u524a\u9664\r\n  CommandButton6.Enabled = False ' \u66f4\u65b0\r\n  CommandButton7.Enabled = True ' \u30af\u30ea\u30a2\r\n  CommandButton1.Enabled = True ' \u767b\u9332\r\n  CommandButton8.Enabled = True ' \u767b\u9332(\u56fa\u5b9a)\r\n\r\n  TextBox47.Enabled = False ' \u884c\r\n  TextBox4.Enabled = False ' \u5de5\u756a\r\n  \r\n  ' \u521d\u671f\u5024\u8a2d\u5b9a\r\n\r\n  Call TextBoxInitialize\r\n\r\nEnd If\r\n\r\nEnd Sub\r\n\r\n' --------------------------------------------------------\r\n' Initialize\r\n' --------------------------------------------------------\r\n\r\nPrivate Sub UserForm_Initialize()\r\n\r\nSet e = Application\r\n\r\n' \u53d7\u6ce8\u5165\u529b --------------------------------------------------------\r\n\r\n' \u54c1\u540d\u578b\u5f0f\u30de\u30b9\u30bf\u30bb\u30c3\u30c8\r\n\r\nSet w = Sheets(\"\u54c1\u540d\u30fb\u578b\u5f0f\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  ListBox2.AddItem\r\n  ListBox2.List(ListBox2.ListCount - 1, 0) = w.Cells(r, 2).Value\r\n  ListBox2.List(ListBox2.ListCount - 1, 1) = w.Cells(r, 3).Value\r\n  ListBox2.List(ListBox2.ListCount - 1, 2) = w.Cells(r, 4).Value\r\n  ListBox2.List(ListBox2.ListCount - 1, 3) = w.Cells(r, 1).Value ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\n\r\nr = r + 1\r\nLoop\r\n\r\nListBox2.ColumnCount = 2\r\n\r\n' \u5f97\u610f\u5148\u30de\u30b9\u30bf\u30bb\u30c3\u30c8\r\n\r\nSet w = Sheets(\"\u5f97\u610f\u5148\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  ListBox1.AddItem\r\n  ListBox1.List(ListBox1.ListCount - 1, 0) = w.Cells(r, 2).Value\r\n  ListBox1.List(ListBox1.ListCount - 1, 1) = w.Cells(r, 1).Value ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\n  \r\nr = r + 1\r\nLoop\r\n\r\n' \u53d7\u6ce8\u65e5\r\n\r\nTextBox19.Text = Format(Now, \"yyyy\/mm\/dd\")\r\n\r\n' \u5de5\u756a\r\n\r\nserial = CLng(Sheets(\"\u8a2d\u5b9a\").Range(\"A2\")) + 1\r\nTextBox4.Text = serial\r\n\r\n' \u5de5\u6570\u5165\u529b --------------------------------------------------------\r\n\r\n' \u767b\u9332\u65e5\r\n\r\nTextBox37.Text = Format(Now, \"yyyy\/mm\/dd\")\r\n\r\n' \u54c1\u76ee\u578b\u5f0f\u30bb\u30c3\u30c8\r\n\r\nSet w = Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  ListBox3.AddItem ' \u5f97\u610f\u5148\u3001\u54c1\u540d\u3001\u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\u3001\u5de5\u756a\r\n  \r\n  ListBox3.List(ListBox3.ListCount - 1, 0) = w.Cells(r, 3).Value\r\n  ListBox3.List(ListBox3.ListCount - 1, 1) = w.Cells(r, 5).Value\r\n  ListBox3.List(ListBox3.ListCount - 1, 2) = w.Cells(r, 6).Value\r\n  ListBox3.List(ListBox3.ListCount - 1, 3) = w.Cells(r, 7).Value\r\n \r\n  ListBox3.List(ListBox3.ListCount - 1, 4) = w.Cells(r, 2).Value ' \u5f97\u610f\u5148\u30b3\u30fc\u30c9\r\n  ListBox3.List(ListBox3.ListCount - 1, 5) = w.Cells(r, 4).Value ' \u54c1\u76ee\u30b3\u30fc\u30c9\r\n  \r\nr = r + 1\r\nLoop\r\n\r\nListBox3.ColumnCount = 4\r\n\r\n' \u4f5c\u696d\u8005\u30de\u30b9\u30bf\u30bb\u30c3\u30c8\r\n\r\nSet w = Sheets(\"\u4f5c\u696d\u8005\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n  ComboBox1.AddItem w.Cells(r, 1).Value\r\nr = r + 1\r\nLoop\r\n\r\n' \u4f5c\u696d\u5185\u5bb9\u30de\u30b9\u30bf\u30bb\u30c3\u30c8\r\n\r\nSet w = Sheets(\"\u4f5c\u696d\u5185\u5bb9\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n  ComboBox2.AddItem w.Cells(r, 1).Value\r\nr = r + 1\r\nLoop\r\n\r\n' \u8acb\u6c42\u66f8 --------------------------------------------------------\r\n\r\n' \u767a\u884c\u65e5\r\n\r\nTextBox40.Text = Format(Now, \"yyyy\/mm\/dd\")\r\n\r\n' \u5f97\u610f\u5148\u30de\u30b9\u30bf\u30bb\u30c3\u30c8\r\n\r\nSet w = Sheets(\"\u5f97\u610f\u5148\u30de\u30b9\u30bf\")\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\n\r\n  ListBox4.AddItem\r\n  ListBox4.List(ListBox4.ListCount - 1, 0) = w.Cells(r, 2).Value\r\n  ListBox4.List(ListBox4.ListCount - 1, 1) = w.Cells(r, 3).Value\r\n  ListBox4.List(ListBox4.ListCount - 1, 2) = w.Cells(r, 4).Value\r\n  \r\nr = r + 1\r\nLoop\r\n\r\n' \u8acb\u6c42\u756a\u53f7\r\n\r\nserial = CLng(Sheets(\"\u8a2d\u5b9a\").Range(\"C2\")) + 1\r\nTextBox43.Text = serial\r\n\r\nEnd Sub\r\n<\/pre>\n<p>\u51fa\u8377\u4e88\u5b9a\u8868<\/p>\n<pre class=\"lang:vb decode:true\">Sub create_shipping(start_day, end_day) ' \u51fa\u8377\u4e88\u5b9a\u8868\r\n\r\nSet f = Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\nSet t = Sheets(\"\u51fa\u8377\u4e88\u5b9a\u8868\")\r\n\r\n' \u30ea\u30bb\u30c3\u30c8\r\n\r\nt.Rows(\"2:5000\").Delete\r\n\r\n' \u30e1\u30a4\u30f3\r\n\r\nr = 2\r\nrr = 2\r\nDo While f.Cells(r, 1).Value &lt;&gt; \"\"\r\n  \r\n  If CDate(f.Cells(r, 12).Value) &gt;= start_day And CDate(f.Cells(r, 12).Value) &lt;= end_day Then\r\n  \r\n    t.Cells(rr, 1).Value = f.Cells(r, 3).Value ' \u5f97\u610f\u5148\r\n    t.Cells(rr, 2).Value = f.Cells(r, 5).Value ' \u54c1\u540d\r\n    t.Cells(rr, 3).Value = f.Cells(r, 6).Value ' \u578b\u5f0f\u30fb\u30b5\u30a4\u30ba\r\n    t.Cells(rr, 4).Value = f.Cells(r, 7).Value ' \u5de5\u756a\r\n    t.Cells(rr, 5).Value = f.Cells(r, 8).Value ' \u6570\u91cf\r\n    t.Cells(rr, 6).Value = f.Cells(r, 11).Value ' \u6ce8\u6587\u756a\u53f7\r\n    t.Cells(rr, 7).Value = f.Cells(r, 12).Value ' \u7d0d\u671f\r\n    rr = rr + 1\r\n    \r\n  End If\r\n  \r\nr = r + 1\r\nLoop\r\n\r\n' \u7f6b\u7dda\r\n\r\nt.Range(\"A1:G\" &amp; rr - 1).Borders.LineStyle = True\r\n\r\n' \u5370\u5237\u7bc4\u56f2 (\u30d7\u30ea\u30f3\u30bf\u30fc\u95a2\u4fc2\u306e\u30a8\u30e9\u30fc\u306f\u7121\u8996)\r\n\r\nOn Error Resume Next\r\n\r\nt.PageSetup.PrintArea = \"A1:G\" &amp; rr - 1\r\n\r\nOn Error GoTo 0\r\n\r\n' \u30a2\u30af\u30c6\u30a3\u30d6\r\n\r\nt.Activate\r\nt.Range(\"A1:G\" &amp; rr - 1).Select\r\n\r\nEnd Sub\r\n<\/pre>\n<p>\u8acb\u6c42\u671f\u9593<\/p>\n<pre class=\"lang:vb decode:true\">Sub GetInvoiceTerm()\r\n\r\n' \u30c1\u30a7\u30c3\u30af\r\n\r\nIf False = IsDate(UserForm1.TextBox49.Text) Then Exit Sub\r\nIf False = IsNumeric(UserForm1.TextBox50.Text) Then Exit Sub\r\nIf False = IsNumeric(UserForm1.TextBox51.Text) Then Exit Sub\r\n\r\ncurrent_date = CDate(UserForm1.TextBox49.Text) ' \u4eca\u56de\u306e\u7de0\u65e5\r\nmaster_closing_date = CInt(UserForm1.TextBox50.Text) ' \u5f97\u610f\u5148\u7de0\u65e5\r\npayment_duration = CInt(UserForm1.TextBox51.Text) ' \u652f\u6255\u4e88\u5b9a(\u7d4c\u904e\u65e5\u6570)\r\n\r\nstart_day = \"\"\r\nend_day = \"\"\r\n\r\nIf master_closing_date &lt;&gt; \"30\" Then\r\n\r\n' \u672b\u7de0\u4ee5\u5916\r\n\r\n  If CInt(Day(current_date)) &gt; CInt(master_closing_date) Then\r\n  \r\n      tmp = DateAdd(\"m\", 1, current_date)\r\n      \r\n      start_day = Year(current_date) &amp; \"\/\" &amp; Month(current_date) &amp; \"\/\" &amp; master_closing_date + 1\r\n      end_day = Year(tmp) &amp; \"\/\" &amp; Month(tmp) &amp; \"\/\" &amp; master_closing_date\r\n      \r\n  Else\r\n  \r\n      tmp = DateAdd(\"m\", -1, current_date)\r\n      \r\n      start_day = Year(tmp) &amp; \"\/\" &amp; Month(tmp) &amp; \"\/\" &amp; master_closing_date + 1\r\n      end_day = Year(current_date) &amp; \"\/\" &amp; Month(current_date) &amp; \"\/\" &amp; master_closing_date\r\n      \r\n  End If\r\n    \r\nElse\r\n\r\n' \u672b\u7de0\u306e\u5834\u5408\r\n\r\n  start_day = Year(current_date) &amp; \"\/\" &amp; Month(current_date) &amp; \"\/1\"\r\n  end_day = Year(current_date) &amp; \"\/\" &amp; Month(current_date) &amp; \"\/\" &amp; GetLastDay(current_date)\r\n        \r\nEnd If\r\n\r\nIf False = IsDate(start_day) Or False = IsDate(end_day) Then\r\n\r\n' \u5f97\u610f\u5148\u7de0\u65e5\u306e\u5024\u304b\u3089start_day\u3001end_day\u304c\u6709\u52b9\u306a\u65e5\u4ed8\u306b\u306a\u3089\u306a\u304b\u3063\u305f\u5834\u5408\r\n\r\n  UserForm1.TextBox41.Text = \"\"\r\n  UserForm1.TextBox46.Text = \"\"\r\n  UserForm1.TextBox44.Text = \"\"\r\n  \r\n  Exit Sub\r\n\r\nEnd If\r\n\r\nUserForm1.TextBox41.Text = Format(CDate(start_day), \"yyyy\/mm\/dd\")\r\nUserForm1.TextBox46.Text = Format(CDate(end_day), \"yyyy\/mm\/dd\")\r\n\r\n' \u652f\u6255\u671f\u9650\u7b97\u51fa--------------------------------------------------------\r\n\r\n' \u652f\u6255\u4e88\u5b9a(\u7d4c\u904e\u65e5\u6570)\u309230\u3067\u5272\u308a\u00d7\u6708\u3001\u4f59\u308a\u00d7\u65e5\r\n\r\nm = payment_duration \/ 30\r\nd = payment_duration Mod 30\r\n  \r\nIf True = IsLastDay(CDate(end_day)) Then\r\n \r\n' end_day\u304c\u6708\u672b\u306e\u5834\u5408\u306f\u6307\u5b9a\u3057\u305f\u6708\u6570\u3092\u52a0\u7b97\u3057\u6708\u672b\u306b\u3059\u308b\u3002\r\n\r\n  tmp1 = DateAdd(\"m\", m, CDate(end_day))\r\n  tmp1 = CDate(Year(tmp1) &amp; \"\/\" &amp; Month(tmp1) &amp; \"\/\" &amp; GetLastDay(tmp1))\r\n  tmp2 = DateAdd(\"d\", d, tmp1)\r\n\r\nElse\r\n\r\n' end_day\u304c\u6708\u672b\u4ee5\u5916\r\n\r\n  tmp1 = DateAdd(\"m\", m, CDate(end_day))\r\n  tmp2 = DateAdd(\"d\", d, tmp1)\r\n\r\nEnd If\r\n\r\nUserForm1.TextBox44.Text = Format(tmp2, \"yyyy\/mm\/dd\")\r\n  \r\nEnd Sub\r\n\r\nFunction IsLastDay(target) ' \u6e21\u3055\u308c\u305f\u65e5\u4ed8\u304c\u6708\u672b\u304b\u3069\u3046\u304b\r\n\r\n' \u547c\u51fa\u5074\u3067\u65e5\u4ed8\u578b\u304b\u78ba\u8a8d\u6e08\r\n\r\nIf Day(target) = GetLastDay(target) Then\r\n  IsLastDay = True\r\nElse\r\n  IsLastDay = False\r\nEnd If\r\n\r\nEnd Function\r\n\r\nFunction GetLastDay(target) ' \u6e21\u3055\u308c\u305f\u65e5\u4ed8\u306e\u6708\u306e\u6708\u672b\u3092\u53d6\u5f97\r\n\r\n' \u547c\u51fa\u5074\u3067\u65e5\u4ed8\u578b\u304b\u78ba\u8a8d\u6e08\r\n\r\nGetLastDay = Day(DateAdd(\"d\", -1, Year(DateAdd(\"m\", 1, target)) &amp; \"\/\" &amp; Month(DateAdd(\"m\", 1, target)) &amp; \"\/1\"))\r\n\r\nEnd Function\r\n<\/pre>\n<p>\u8acb\u6c42\u66f8<\/p>\n<pre class=\"lang:vb decode:true \">Sub create_invoice()\r\n\r\n' \u30c1\u30a7\u30c3\u30af\r\n\r\nchk = True\r\nIf UserForm1.TextBox40.Text = \"\" Then chk = False ' \u767a\u884c\u65e5\r\nIf UserForm1.TextBox43.Text = \"\" Then chk = False ' \u8acb\u6c42\u756a\u53f7\r\n\r\n'If UserForm1.TextBox49.Text = \"\" Then chk = False ' \u7de0\u65e5\r\n' \u624b\u52d5\u3067\u8acb\u6c42\u671f\u9650\u3092\u5165\u529b\u3059\u308b\u5834\u5408\u3001\u4e0d\u8981\u306a\u305f\u3081\uff0b\u81ea\u52d5\u8a08\u7b97\u304c\u767a\u706b\u3057\u306a\u3044\u305f\u3081\r\n\r\nIf UserForm1.TextBox42.Text = \"\" Then chk = False ' \u5f97\u610f\u5148\r\nIf UserForm1.TextBox50.Text = \"\" Then chk = False ' \u5f97\u610f\u5148\u7de0\u65e5\r\nIf UserForm1.TextBox51.Text = \"\" Then chk = False ' \u652f\u6255\u4e88\u5b9a\r\nIf UserForm1.TextBox41.Text = \"\" Then chk = False ' \u8acb\u6c42\u671f\u9593\u30fb\u958b\u59cb\r\nIf UserForm1.TextBox46.Text = \"\" Then chk = False ' \u8acb\u6c42\u671f\u9593\u30fb\u7d42\u4e86\r\nIf UserForm1.TextBox44.Text = \"\" Then chk = False ' \u652f\u6255\u671f\u9650\r\n\r\nIf False = chk Then\r\n  MsgBox \"\u5165\u529b\u306b\u4e0d\u8db3\u304c\u3042\u308a\u307e\u3059\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\n\r\n' \u884c\u6570\u30ab\u30a6\u30f3\u30c8\r\n\r\nr = 2\r\nc = 1\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\" ' \u53d7\u6ce8\u65e5\u3067\u6700\u7d42\u884c\u53d6\u5f97\r\n\r\n  If w.Cells(r, 3).Value = UserForm1.TextBox42.Text Then ' \u5f97\u610f\u5148\u30de\u30c3\u30c1\r\n  \r\n  ' \u8acb\u6c42\u671f\u9593\u30de\u30c3\u30c1(\u53d7\u6ce8\u5165\u529b\u306e\u8a08\u4e0a\u65e5\u3092\u5229\u7528)\r\n  If CDate(w.Cells(r, 16).Value) &gt;= CDate(UserForm1.TextBox41.Text) And _\r\n     CDate(w.Cells(r, 16).Value) &lt;= CDate(UserForm1.TextBox46.Text) Then\r\n    \r\n    If c &gt; 75 Then ' \u6700\u5927\u884c\u6570\r\n    \r\n      MsgBox \"\u884c\u6570\u304c\u591a\u3044\u305f\u3081\u4e2d\u6b62\u3057\u307e\u3059\u3002\"\r\n      Exit Sub\r\n      \r\n    End If\r\n    \r\n  c = c + 1\r\n  \r\n  End If\r\n  End If\r\n\r\nr = r + 1\r\nLoop\r\n\r\n' \u30d8\u30c3\u30c0\r\n\r\nThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"A2\").Value = UserForm1.TextBox42.Text &amp; \" \u5fa1\u4e2d\"\r\n\r\nThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"L3\").Value = \" \u4f1d\u7968\u756a\u53f7\uff1a\" &amp; UserForm1.TextBox43.Text\r\nThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"L4\").Value = \" \u767a\u884c\u65e5\uff1a\" &amp; UserForm1.TextBox40.Text\r\n\r\nThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"P6\").Value = UserForm1.TextBox44.Text ' \u652f\u6255\u671f\u9650\r\nThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"P7\").Value = Format(UserForm1.TextBox41.Text, \"yyyy\/mm\/dd\") &amp; \"\uff5e\" &amp; Format(UserForm1.TextBox46.Text, \"yyyy\/mm\/dd\") ' \u8acb\u6c42\u671f\u9593\r\n\r\n' \u66f8\u304d\u8fbc\u307f\r\n\r\nr = 2\r\nc = 1\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\" ' \u53d7\u6ce8\u65e5\u3067\u6700\u7d42\u884c\u53d6\u5f97\r\n\r\n  If w.Cells(r, 3).Value = UserForm1.TextBox42.Text Then ' \u5f97\u610f\u5148\u30de\u30c3\u30c1\r\n  ' \u8acb\u6c42\u671f\u9593\u30de\u30c3\u30c1\r\n  If CDate(w.Cells(r, 16).Value) &gt;= CDate(UserForm1.TextBox41.Text) And _\r\n     CDate(w.Cells(r, 16).Value) &lt;= CDate(UserForm1.TextBox46.Text) Then\r\n  \r\n    If c &lt;= 15 Then\r\n\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 1).Value = w.Cells(r, 15).Value ' \u7d0d\u54c1\u65e5\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 2).Value = w.Cells(r, 14).Value ' \u7d0d\u54c1\u756a\u53f7\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 3).Value = w.Cells(r, 11).Value ' \u6ce8\u6587\u756a\u53f7\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 4).Value = w.Cells(r, 5).Value &amp; \" \" &amp; w.Cells(r, 6).Value ' \u54c1\u540d\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 6).Value = w.Cells(r, 8).Value ' \u6570\u91cf\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 7).Value = w.Cells(r, 9).Value ' \u5358\u4fa1\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 9).Value = w.Cells(r, 10).Value ' \u91d1\u984d\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 11).Value = w.Cells(r, 13).Value ' \u5099\u8003\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Cells(c + 18, 13).Value = w.Cells(r, 7).Value ' \u5de5\u756a\r\n      w.Cells(r, 17).Value = UserForm1.TextBox43.Text ' \u8acb\u6c42\u756a\u53f7\r\n      \r\n    ElseIf c &gt;= 16 And c &lt;= 45 Then\r\n\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 1).Value = w.Cells(r, 15).Value ' \u7d0d\u54c1\u65e5\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 2).Value = w.Cells(r, 14).Value ' \u7d0d\u54c1\u756a\u53f7\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 3).Value = w.Cells(r, 11).Value ' \u6ce8\u6587\u756a\u53f7\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 4).Value = w.Cells(r, 5).Value &amp; \" \" &amp; w.Cells(r, 6).Value ' \u54c1\u540d\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 6).Value = w.Cells(r, 8).Value ' \u6570\u91cf\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 7).Value = w.Cells(r, 9).Value ' \u5358\u4fa1\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 9).Value = w.Cells(r, 10).Value ' \u91d1\u984d\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 11).Value = w.Cells(r, 13).Value ' \u5099\u8003\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Cells(c - 13, 13).Value = w.Cells(r, 7).Value ' \u5de5\u756a\r\n      w.Cells(r, 17).Value = UserForm1.TextBox43.Text ' \u8acb\u6c42\u756a\u53f7\r\n      \r\n      p2_total = p2_total + CCur(w.Cells(r, 10).Value)\r\n    \r\n    ElseIf c &gt;= 46 And c &lt;= 75 Then\r\n   \r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 1).Value = w.Cells(r, 15).Value ' \u7d0d\u54c1\u65e5\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 2).Value = w.Cells(r, 14).Value ' \u7d0d\u54c1\u756a\u53f7\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 3).Value = w.Cells(r, 11).Value ' \u6ce8\u6587\u756a\u53f7\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 4).Value = w.Cells(r, 5).Value &amp; \" \" &amp; w.Cells(r, 6).Value ' \u54c1\u540d\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 6).Value = w.Cells(r, 8).Value ' \u6570\u91cf\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 7).Value = w.Cells(r, 9).Value ' \u5358\u4fa1\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 9).Value = w.Cells(r, 10).Value ' \u91d1\u984d\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 11).Value = w.Cells(r, 13).Value ' \u5099\u8003\r\n      ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Cells(c - 43, 13).Value = w.Cells(r, 7).Value ' \u5de5\u756a\r\n      w.Cells(r, 17).Value = UserForm1.TextBox43.Text ' \u8acb\u6c42\u756a\u53f7\r\n      \r\n      p3_total = p3_total + CCur(w.Cells(r, 10).Value)\r\n      \r\n    End If\r\n  \r\n  c = c + 1\r\n  End If\r\n  End If\r\n\r\nr = r + 1\r\nLoop\r\n\r\n' \u30d8\u30c3\u30c0\u30fc(\u91d1\u984d)\r\n\r\nThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"P3\").Value = p2_total\r\nThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"P4\").Value = p3_total\r\n\r\n' \u30d5\u30c3\u30bf\u30fc(\u30da\u30fc\u30b8\u6570\u306e\u6709\u7121\u3067\u8907\u5199\u3092\u5224\u65ad)\r\n\r\nIf c &lt;= 15 Then\r\n\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"M35\").Value = \"P1\/1\"\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Range(\"M34\").Value = \"\"\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Range(\"M34\").Value = \"\"\r\n  \r\nElseIf c &gt;= 16 And c &lt;= 45 Then\r\n\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"M35\").Value = \"P1\/2\"\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Range(\"M34\").Value = \"P2\/2\"\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Range(\"M34\").Value = \"\"\r\n  \r\nElseIf c &gt;= 46 And c &lt;= 75 Then\r\n\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"M35\").Value = \"P1\/3\"\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Range(\"M34\").Value = \"P2\/3\"\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Range(\"M34\").Value = \"P3\/3\"\r\n  \r\nEnd If\r\n\r\n' \u8a08\u4e0a\u5c65\u6b74\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u8a08\u4e0a\u5c65\u6b74\")\r\n\r\n' \u884c\u672b\r\n\r\nr = 2\r\nDo While w.Cells(r, 1).Value &lt;&gt; \"\"\r\nr = r + 1\r\nLoop\r\n\r\nw.Cells(r, 1).Value = UserForm1.TextBox40.Text ' \u767a\u884c\u65e5\r\nw.Cells(r, 2).Value = UserForm1.TextBox43.Text ' \u8acb\u6c42\u756a\u53f7\r\nw.Cells(r, 3).Value = UserForm1.TextBox44.Text ' \u652f\u6255\u671f\u9650\r\nw.Cells(r, 4).Value = Format(UserForm1.TextBox41.Text, \"yyyy\/mm\/dd\") &amp; \"\uff5e\" &amp; Format(UserForm1.TextBox46.Text, \"yyyy\/mm\/dd\") ' \u8acb\u6c42\u671f\u9593\r\nw.Cells(r, 5).Value = UserForm1.TextBox42.Text ' \u5f97\u610f\u5148\r\nw.Cells(r, 6).Value = ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"B12\").Value ' \u5408\u8a08\r\nw.Cells(r, 7).Value = ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"B13\").Value ' \u6d88\u8cbb\u7a0e8%\r\nw.Cells(r, 8).Value = ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"B14\").Value ' \u6d88\u8cbb\u7a0e10%\r\nw.Cells(r, 9).Value = ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"B15\").Value ' \u8acb\u6c42\u984d\r\n\r\n' \u30b7\u30fc\u30c8\u30b3\u30d4\u30fc\r\n\r\nCall copy_invoice(UserForm1.TextBox43.Text)\r\nCall reset_invoice\r\n\r\n' \u8acb\u6c42\u756a\u53f7\u66f4\u65b0\r\n\r\nThisWorkbook.Sheets(\"\u8a2d\u5b9a\").Range(\"C2\") = UserForm1.TextBox43.Text\r\nUserForm1.TextBox43.Text = CLng(UserForm1.TextBox43.Text) + 1\r\n\r\nEnd Sub\r\n\r\nSub copy_invoice(order)\r\n\r\n' \u30d6\u30c3\u30af\u304c\u958b\u3044\u3066\u3044\u308b\u304b\r\n\r\nopened = False\r\nFor Each wb In Workbooks\r\n  If wb.Name = \"\u8acb\u6c42\u66f8.xlsx\" Then opened = True\r\nNext\r\n\r\n' \u958b\u3044\u3066\u3044\u306a\u3044\u5834\u5408\u958b\u304f\r\n\r\nIf opened = False Then Workbooks.Open ThisWorkbook.Path &amp; \"\\\u8acb\u6c42\u66f8.xlsx\"\r\n  \r\n' \u91cd\u8907\u30c1\u30a7\u30c3\u30af\r\n\r\nSet target = Workbooks(\"\u8acb\u6c42\u66f8.xlsx\")\r\n\r\nexists = False\r\nFor Each w In target.Sheets\r\n  If w.Name = CStr(order) &amp; \"_1\" Then exists = True\r\n  If w.Name = CStr(order) &amp; \"_2\" Then exists = True\r\n  If w.Name = CStr(order) &amp; \"_3\" Then exists = True\r\nNext\r\n\r\nIf exists = True Then\r\n  MsgBox \"\u65e2\u306b\u8acb\u6c42\u66f8\u304c\u5b58\u5728\u3057\u307e\u3059\u3002\u524a\u9664\u5f8c\u3001\u3082\u3046\u4e00\u5ea6\u5b9f\u884c\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\n' \u30b7\u30fc\u30c8\u306e\u30b3\u30d4\u30fc\r\n\r\nSet target = Workbooks(\"\u8acb\u6c42\u66f8.xlsx\")\r\n\r\nIf ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Range(\"M35\").Value &lt;&gt; \"\" Then\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\").Copy After:=target.Sheets(target.Sheets.Count)\r\n  target.Sheets(\"\u8acb\u6c42\u66f8\").Name = order &amp; \"_1\"\r\nEnd If\r\n\r\nIf ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Range(\"M34\").Value &lt;&gt; \"\" Then\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\").Copy After:=target.Sheets(target.Sheets.Count)\r\n  target.Sheets(\"\u8acb\u6c42\u66f82\").Name = order &amp; \"_2\"\r\nEnd If\r\n\r\nIf ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Range(\"M34\").Value &lt;&gt; \"\" Then\r\n  ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\").Copy After:=target.Sheets(target.Sheets.Count)\r\n  target.Sheets(\"\u8acb\u6c42\u66f83\").Name = order &amp; \"_3\"\r\nEnd If\r\n\r\nEnd Sub\r\n\r\nSub reset_invoice()\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f8\")\r\n\r\nw.Range(\"A2\").Value = \"\u5fa1\u4e2d\"\r\nw.Range(\"M3\").Value = \"\u4f1d\u7968\u756a\u53f7\uff1a\"\r\nw.Range(\"M4\").Value = \"\u767a\u884c\u65e5\uff1a\"\r\n\r\nw.Range(\"P3\").Value = \"\"\r\nw.Range(\"P4\").Value = \"\"\r\nw.Range(\"P6\").Value = \"\"\r\nw.Range(\"P7\").Value = \"\"\r\n\r\nw.Range(\"M35\").Value = \"P. 1\/1\"\r\n\r\nFor r = 19 To 33\r\n\r\n  w.Cells(r, 1).Value = \"\"\r\n  w.Cells(r, 2).Value = \"\"\r\n  w.Cells(r, 3).Value = \"\"\r\n  w.Cells(r, 4).Value = \"\"\r\n  w.Cells(r, 6).Value = \"\"\r\n  w.Cells(r, 7).Value = \"\"\r\n  w.Cells(r, 9).Value = \"\"\r\n  w.Cells(r, 11).Value = \"\"\r\n  w.Cells(r, 13).Value = \"\"\r\n\r\nNext\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f82\")\r\n\r\nw.Range(\"M34\").Value = \"P. 2\/2\"\r\n\r\nFor r = 3 To 32\r\n\r\n  w.Cells(r, 1).Value = \"\"\r\n  w.Cells(r, 2).Value = \"\"\r\n  w.Cells(r, 3).Value = \"\"\r\n  w.Cells(r, 4).Value = \"\"\r\n  w.Cells(r, 6).Value = \"\"\r\n  w.Cells(r, 7).Value = \"\"\r\n  w.Cells(r, 9).Value = \"\"\r\n  w.Cells(r, 11).Value = \"\"\r\n  w.Cells(r, 13).Value = \"\"\r\n\r\nNext\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u8acb\u6c42\u66f83\")\r\n\r\nw.Range(\"M34\").Value = \"P. 3\/3\"\r\n\r\nFor r = 3 To 32\r\n\r\n  w.Cells(r, 1).Value = \"\"\r\n  w.Cells(r, 2).Value = \"\"\r\n  w.Cells(r, 3).Value = \"\"\r\n  w.Cells(r, 4).Value = \"\"\r\n  w.Cells(r, 6).Value = \"\"\r\n  w.Cells(r, 7).Value = \"\"\r\n  w.Cells(r, 9).Value = \"\"\r\n  w.Cells(r, 11).Value = \"\"\r\n  w.Cells(r, 13).Value = \"\"\r\n\r\nNext\r\n\r\nEnd Sub<\/pre>\n<p>\u8a2d\u5b9a<\/p>\n<pre class=\"lang:vb decode:true \">Sub test()\r\n\r\nApplication.CommandBars(\"cell\").Reset\r\n\r\nEnd Sub\r\n\r\nSub reset_right_click()\r\n\r\nFor i = 1 To 3\r\n  Application.CommandBars(\"cell\").Controls(\"\u30d5\u30a9\u30fc\u30e0\u8868\u793a\").Delete\r\n  Application.CommandBars(\"cell\").Controls(\"\u7d0d\u54c1\u66f8\u4f5c\u6210\").Delete\r\nNext\r\n\r\nEnd Sub\r\n\r\nSub auto_close()\r\n\r\nOn Error Resume Next\r\n\r\nCall reset_right_click\r\n\r\nEnd Sub\r\n\r\nSub auto_open()\r\n\r\nOn Error Resume Next\r\n\r\nCall reset_right_click\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n  .OnAction = \"show_form\"\r\n  .Caption = \"\u30d5\u30a9\u30fc\u30e0\u8868\u793a\"\r\nEnd With\r\n\r\nWith Application.CommandBars(\"cell\").Controls.Add\r\n  .OnAction = \"create_delivery_note\"\r\n  .Caption = \"\u7d0d\u54c1\u66f8\u4f5c\u6210\"\r\nEnd With\r\n\r\n' \u30d5\u30a3\u30eb\u30bf\u89e3\u9664\r\n\r\nFor Each s In Worksheets\r\n  s.AutoFilterMode = False\r\nNext\r\n\r\n' \u8d77\u52d5\r\n\r\nCall set_style\r\n\r\n' \u30a2\u30af\u30c6\u30a3\u30d6\r\n\r\nSheets(\"\u8a2d\u5b9a\").Activate\r\n\r\nEnd Sub\r\n\r\nSub show_form()\r\n\r\nIf ActiveWorkbook.Name &lt;&gt; ThisWorkbook.Name Then Exit Sub\r\n\r\nUserForm1.Show\r\n\r\nEnd Sub\r\n\r\nSub set_style()\r\n\r\nSet w = Sheets(\"\u53d7\u6ce8\u5165\u529b\")\r\n\r\nw.Columns(\"A\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"B\").NumberFormatLocal = \"@\"\r\nw.Columns(\"C\").NumberFormatLocal = \"@\"\r\nw.Columns(\"D\").NumberFormatLocal = \"@\"\r\nw.Columns(\"E\").NumberFormatLocal = \"@\"\r\nw.Columns(\"F\").NumberFormatLocal = \"@\"\r\nw.Columns(\"G\").NumberFormatLocal = \"@\"\r\nw.Columns(\"H\").NumberFormatLocal = \"0\"\r\nw.Columns(\"I\").NumberFormatLocal = \"#,##0\"\r\nw.Columns(\"J\").NumberFormatLocal = \"#,##0\"\r\nw.Columns(\"K\").NumberFormatLocal = \"@\"\r\nw.Columns(\"L\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"M\").NumberFormatLocal = \"@\"\r\nw.Columns(\"N\").NumberFormatLocal = \"@\"\r\nw.Columns(\"O\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"P\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"Q\").NumberFormatLocal = \"@\"\r\n\r\n' --------------------------------------------------------\r\n\r\nSet w = Sheets(\"\u66f4\u65b0\u524a\u9664\u5c65\u6b74\")\r\n\r\nw.Columns(\"A\").NumberFormatLocal = \"@\"\r\nw.Columns(\"B\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"C\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"D\").NumberFormatLocal = \"@\"\r\nw.Columns(\"E\").NumberFormatLocal = \"@\"\r\nw.Columns(\"F\").NumberFormatLocal = \"@\"\r\nw.Columns(\"G\").NumberFormatLocal = \"@\"\r\nw.Columns(\"H\").NumberFormatLocal = \"@\"\r\nw.Columns(\"I\").NumberFormatLocal = \"@\"\r\nw.Columns(\"J\").NumberFormatLocal = \"0\"\r\nw.Columns(\"K\").NumberFormatLocal = \"#,##0\"\r\nw.Columns(\"L\").NumberFormatLocal = \"#,##0\"\r\nw.Columns(\"M\").NumberFormatLocal = \"@\"\r\nw.Columns(\"N\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"O\").NumberFormatLocal = \"@\"\r\nw.Columns(\"P\").NumberFormatLocal = \"@\"\r\nw.Columns(\"Q\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"R\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"S\").NumberFormatLocal = \"@\"\r\n\r\n' --------------------------------------------------------\r\n\r\nSet w = Sheets(\"\u51fa\u8377\u4e88\u5b9a\u8868\")\r\n\r\nw.Columns(\"A\").NumberFormatLocal = \"@\"\r\nw.Columns(\"B\").NumberFormatLocal = \"@\"\r\nw.Columns(\"C\").NumberFormatLocal = \"@\"\r\nw.Columns(\"D\").NumberFormatLocal = \"0\"\r\nw.Columns(\"E\").NumberFormatLocal = \"0\"\r\nw.Columns(\"F\").NumberFormatLocal = \"@\"\r\nw.Columns(\"G\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\n\r\n' --------------------------------------------------------\r\n\r\nSet w = Sheets(\"\u8a08\u4e0a\u5c65\u6b74\")\r\n\r\nw.Columns(\"A\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"B\").NumberFormatLocal = \"0\"\r\nw.Columns(\"C\").NumberFormatLocal = \"yyyy\u5e74mm\u6708dd\u65e5\"\r\nw.Columns(\"D\").NumberFormatLocal = \"@\"\r\nw.Columns(\"E\").NumberFormatLocal = \"@\"\r\nw.Columns(\"F\").NumberFormatLocal = \"#,##0\"\r\nw.Columns(\"G\").NumberFormatLocal = \"#,##0\"\r\nw.Columns(\"H\").NumberFormatLocal = \"#,##0\"\r\nw.Columns(\"I\").NumberFormatLocal = \"#,##0\"\r\n\r\n' --------------------------------------------------------\r\n\r\nSet w = Sheets(\"\u5de5\u6570\u5165\u529b\")\r\n\r\nw.Columns(\"A\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"B\").NumberFormatLocal = \"@\"\r\nw.Columns(\"C\").NumberFormatLocal = \"@\"\r\nw.Columns(\"D\").NumberFormatLocal = \"@\"\r\nw.Columns(\"E\").NumberFormatLocal = \"@\"\r\nw.Columns(\"F\").NumberFormatLocal = \"@\"\r\nw.Columns(\"G\").NumberFormatLocal = \"@\"\r\nw.Columns(\"H\").NumberFormatLocal = \"@\"\r\nw.Columns(\"I\").NumberFormatLocal = \"yyyy\/mm\/dd\"\r\nw.Columns(\"J\").NumberFormatLocal = \"0.00\"\r\nw.Columns(\"K\").NumberFormatLocal = \"@\"\r\nw.Columns(\"L\").NumberFormatLocal = \"@\"\r\n\r\nEnd Sub<\/pre>\n<p>\u7d0d\u54c1\u66f8<\/p>\n<pre class=\"lang:vb decode:true \">Sub create_delivery_note()\r\n\r\n' \u7d0d\u54c1\u66f8\u4f5c\u6210\r\n\r\nIf ActiveWorkbook.Name &lt;&gt; ThisWorkbook.Name Then Exit Sub\r\n\r\nSet w = ActiveSheet\r\n\r\n' \u30b7\u30fc\u30c8\u540d\u30c1\u30a7\u30c3\u30af\r\n\r\nIf w.Name &lt;&gt; \"\u53d7\u6ce8\u5165\u529b\" Then\r\n  MsgBox \"\u53d7\u6ce8\u5165\u529b\u30b7\u30fc\u30c8\u3067\u5b9f\u884c\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\n'\u9078\u629e\u7bc4\u56f2\u3092\u4e00\u610f\u306e\u884c\u3078\r\n\r\nSet dic = CreateObject(\"scripting.dictionary\")\r\n\r\nFor Each r In Selection\r\n  If Not dic.exists(r.Row) Then dic.Add r.Row, \"\"\r\nNext\r\n\r\nReDim selected_rows(dic.Count - 1)\r\n\r\nk = dic.keys\r\nFor i = 0 To dic.Count - 1\r\n  selected_rows(i) = k(i)\r\nNext i\r\n\r\nselected_rows_count = dic.Count\r\n\r\n' \u9078\u629e\u884c\u6570\u30c1\u30a7\u30c3\u30af\r\n\r\nIf selected_rows_count &gt; 15 Then\r\n  MsgBox \"\u9078\u629e\u3067\u304d\u308b\u306e\u306f15\u884c\u307e\u3067\u3067\u3059\u3002\"\r\n  Exit Sub\r\nEnd If\r\n\r\n' \u5f97\u610f\u5148\u3001\u7d0d\u54c1\u65e5\u30c1\u30a7\u30c3\u30af\r\n\r\nFor Each r In selected_rows\r\n\r\n  If w.Cells(r, 15).Value = \"\" Then\r\n    MsgBox \"\u7d0d\u54c1\u65e5\u304c\u7a7a\u767d\u3067\u3059\u3002\"\r\n    Exit Sub\r\n  End If\r\n  \r\n  If w.Cells(r, 15).Value &lt;&gt; w.Cells(selected_rows(0), 15).Value Then\r\n    MsgBox \"\u8907\u6570\u306e\u7d0d\u54c1\u65e5\u304c\u9078\u629e\u3055\u308c\u3066\u307e\u3059\u3002\"\r\n    Exit Sub\r\n  End If\r\n  \r\n  If w.Cells(r, 3).Value &lt;&gt; w.Cells(selected_rows(0), 3).Value Then\r\n    MsgBox \"\u8907\u6570\u306e\u5f97\u610f\u5148\u304c\u9078\u629e\u3055\u308c\u3066\u307e\u3059\u3002\"\r\n    Exit Sub\r\n  End If\r\n  \r\nNext\r\n\r\n' \u91cd\u8907\u3001\u7d0d\u54c1\u756a\u53f7\r\n\r\nFor Each r In selected_rows\r\n  If w.Cells(r, 14).Value &lt;&gt; \"\" Then\r\n    MsgBox r &amp; \"\u884c\u76ee\u306f\u65e2\u306b\u767a\u884c\u6e08\u3067\u3059\u3002\u6ce8\u610f\u3057\u3066\u304f\u3060\u3055\u3044\u3002\"\r\n  End If\r\nNext\r\n\r\n' \u7d0d\u54c1\u66f8 --------------------------------------------------------\r\n\r\n' \u4f1d\u7968\u756a\u53f7\r\n\r\norder = CLng(Sheets(\"\u8a2d\u5b9a\").Range(\"B2\").Value) + 1\r\nSheets(\"\u8a2d\u5b9a\").Range(\"B2\").Value = order\r\n\r\n' \u66f8\u304d\u8fbc\u307f\r\n\r\nWith ThisWorkbook.Sheets(\"\u7d0d\u54c1\u66f8\")\r\n\r\nrr = 13\r\n\r\nFor Each r In selected_rows\r\n  \r\n  If r = selected_rows(0) Then  ' \u9078\u629e\u884c\u306e\u4e00\u756a\u4e0a\u306e\u884c\u3092\u5229\u7528(\u4e00\u5ea6\u3060\u3051\u66f8\u304d\u8fbc\u307f)\r\n  \r\n    .Range(\"J2\").Value = \"\u4f1d\u7968\u756a\u53f7\uff1a\" &amp; order ' \u7d0d\u54c1\u756a\u53f7\r\n    .Range(\"J3\").Value = \"\u7d0d\u54c1\u65e5\uff1a\" &amp; Format(w.Cells(r, 15).Value, \"yyyy\u5e74mm\u6708dd\u65e5\") ' \u7d0d\u54c1\u65e5\r\n    .Range(\"A2\").Value = w.Cells(r, 3).Value &amp; \"\u3000\u5fa1\u4e2d\"  ' \u5f97\u610f\u5148\r\n\r\n  End If\r\n  \r\n  .Cells(rr, 1).Value = w.Cells(r, 11).Value ' \u6ce8\u6587\u756a\u53f7\r\n  .Cells(rr, 3).Value = w.Cells(r, 5).Value &amp; \" \" &amp; w.Cells(r, 6).Value ' \u54c1\u540d\r\n  .Cells(rr, 5).Value = w.Cells(r, 8).Value ' \u6570\u91cf\r\n  .Cells(rr, 6).Value = w.Cells(r, 9).Value ' \u5358\u4fa1\r\n  .Cells(rr, 8).Value = w.Cells(r, 10).Value ' \u91d1\u984d\r\n  .Cells(rr, 9).Value = w.Cells(r, 13).Value ' \u5099\u8003\r\n  .Cells(rr, 11).Value = w.Cells(r, 7).Value ' \u5de5\u756a\r\n  \r\n  ' \u53d7\u6ce8\u5165\u529b\u30b7\u30fc\u30c8\u66f8\u8fbc\r\n  \r\n  w.Cells(r, 14).Value = \"\" &amp; order ' \u7d0d\u54c1\u756a\u53f7\r\n  \r\nrr = rr + 1\r\n\r\nNext\r\n\r\nEnd With\r\n\r\n' \u30a2\u30af\u30c6\u30a3\u30d6\r\n\r\nw.Activate\r\n\r\n' \u30b7\u30fc\u30c8\u30b3\u30d4\u30fc --------------------------------------------------------\r\n\r\nopened = False\r\n\r\n' \u30d6\u30c3\u30af\u304c\u958b\u3044\u3066\u3044\u308b\u304b\r\n\r\nFor Each wb In Workbooks\r\n  If wb.Name = \"\u7d0d\u54c1\u66f8.xlsx\" Then opened = True\r\nNext\r\n\r\n' \u958b\u3044\u3066\u3044\u306a\u3044\u5834\u5408\u958b\u304f\r\n\r\n'On Error GoTo exception\r\n\r\n  If opened = False Then Workbooks.Open ThisWorkbook.Path &amp; \"\\\u7d0d\u54c1\u66f8.xlsx\"\r\n  \r\n'On Error GoTo 0\r\n\r\n' \u30b7\u30fc\u30c8\u306e\u30b3\u30d4\u30fc\r\n\r\nSet b = Workbooks(\"\u7d0d\u54c1\u66f8.xlsx\")\r\n\r\nThisWorkbook.Sheets(\"\u7d0d\u54c1\u66f8\").Copy After:=b.Sheets(b.Sheets.Count)\r\n\r\n' \u30b7\u30fc\u30c8\u304c\u5b58\u5728\u3057\u3066\u3044\u308b\u304b\u3069\u3046\u304b\u3001\u5b58\u5728\u3057\u3066\u3044\u305f\u3089suffix\r\n\r\nexists = False\r\nFor Each w In b.Sheets\r\n  If w.Name = CStr(order) Then exists = True\r\nNext\r\n\r\nIf exists = False Then\r\n  b.Sheets(\"\u7d0d\u54c1\u66f8\").Name = order\r\nElse\r\n  b.Sheets(\"\u7d0d\u54c1\u66f8\").Name = order &amp; \"(2)\"\r\nEnd If\r\n\r\nCall reset_delivery\r\n\r\nExit Sub\r\n\r\nexception:\r\n\r\nCall reset_delivery\r\n\r\nMsgBox \"\u7d0d\u54c1\u66f8.xlsx\u304c\u5b58\u5728\u3057\u307e\u305b\u3093\u3002\"\r\n\r\nEnd Sub\r\n\r\nSub reset_delivery()\r\n\r\nSet w = ThisWorkbook.Sheets(\"\u7d0d\u54c1\u66f8\")\r\n\r\nw.Range(\"J2\").Value = \"\u4f1d\u7968\u756a\u53f7\uff1a\"\r\nw.Range(\"J3\").Value = \"\u7d0d\u54c1\u65e5\uff1a\"\r\nw.Range(\"A2\").Value = \"\u5fa1\u4e2d\"\r\n\r\nFor r = 13 To 27\r\n  w.Cells(r, 1).Value = \"\"\r\n  w.Cells(r, 3).Value = \"\"\r\n  w.Cells(r, 5).Value = \"\"\r\n  w.Cells(r, 6).Value = \"\"\r\n  w.Cells(r, 8).Value = \"\"\r\n  w.Cells(r, 9).Value = \"\"\r\n  w.Cells(r, 11).Value = \"\"\r\nNext\r\n\r\nEnd Sub<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel\u3067\u8ca9\u58f2\u7ba1\u7406\u3092\u4f5c\u6210\u3057\u3066\u307f\u305f\u3002 UserForm1 Private WithEvents e As Application Sub e_SheetSelectionChange(ByVal w As Object, &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/appbay.org\/?p=5742\" class=\"more-link\"><span class=\"screen-reader-text\">&#8220;VBA \u8ca9\u58f2\u7ba1\u7406&#8221; \u306e<\/span>\u7d9a\u304d\u3092\u8aad\u3080<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-5742","post","type-post","status-publish","format-standard","hentry","category-1","tag-vba"],"_links":{"self":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/5742","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5742"}],"version-history":[{"count":3,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/5742\/revisions"}],"predecessor-version":[{"id":5745,"href":"https:\/\/appbay.org\/index.php?rest_route=\/wp\/v2\/posts\/5742\/revisions\/5745"}],"wp:attachment":[{"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5742"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5742"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/appbay.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5742"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}