載點：http://yvesjing.miroko.tw/BadonVBA.xls (聽說這空間只能外連到年底，反正到年底這文章也沉了沒差 XD) 說明：目前只有弄定便當並統計、隨機建議要定啥便當、清除所有資料，還有一些防呆 (不過這篇主要功能只是給人參考些函式基本用法 囧) 雖然不怎麼好用，但是是為了拿來練習一下VB = =|| 不過由於偷懶，便當廠商跟菜單寫死的 XD 要新增可以，不過請自行放到 Sheet2 裡面並在程式碼裡面修改 XD 要用巨集必須將 [工具] -> [巨集] -> [安全性] 改成中或以下 @@ 由於沒啥基礎，只是拿來練習用，所以函式用的蠻亂的，結構不良之類的就別鞭我了 Orz 下面簡單介紹怎麼進去Excel的VBA編輯畫面騙點P幣 (￣ε(#￣) #○=(一-一o) -- 首先開啟 Excel [工具] -> [巨集] -> [Visual Basic 編輯器]，[滑鼠左鍵]單擊 在跳出Visual Basic 編輯器視窗中 [插入] -> [自訂表單]，[滑鼠左鍵]單擊 然後就跟VB的介面差不多了 (￣▽￣＃)﹏﹏ 如果想要Excel開啟時就啟動先前設計的VBA 在Visual Basic 編輯器視窗中左上的[專案]視窗的[ThisWorkBook]上[滑鼠左鍵]點兩下 然後在[Book1 - ThisWorkBook (程式碼)]視窗裡貼上以下內容 Private Sub Workbook_Open() UserForm1.Show End Sub [自訂表單] 一開始預設名稱應該是 [UserForm1]，不是的話再自己改囉 -- 不過第一次使用這東西當就出現很多問題了 一個是下拉式不方便看，也許以後改列表比較好 然後打電話時發現有些賣完了 =口= 要聯絡哪些人改餐很不方便，更OX的是有些還有漲價 Orz 總之一整個手忙腳亂 ˋ(′_‵||)ˊ 等哪天想不開再來改吧...... 對了，如果有人想拿去修改玩玩 甚至交作業 的請自取，不用先徵得我同意 不要拿去賣 這種也賣不出去吧 XD 或宣稱原著就好了 (老師問例外 囧 附註： 本著作依據 Creative Commons 姓名標示-非商業性-相同方式分享 授權條款為授權 http://creativecommons.org/licenses/by-nc-sa/2.0/tw/ -- '補一下程式碼 Dim number As Integer Dim total As Integer Private Sub UserForm_Activate() '先載入點餐者名單與店家名稱等 number = 2 While Cells(number, 1) <> "" And Cells(number, 1) <> "共計" number = number + 1 Wend total = 2 While Cells(total, 6) <> "" total = total + 1 Wend Label2.Caption = "今天: " & Date & " 星期" & Weekday(Date) ComboBox1.AddItem "老師" For i = 2 To 31 ComboBox1.AddItem i - 1 Next ComboBox1.AddItem "其他" For i = 1 To 9 ComboBox2.AddItem Sheet2.Cells(1, i) i = i + 1 Next ComboBox3.Clear End Sub Private Sub ComboBox1_Change() '選學號後才可以點餐 If ComboBox2.Text <> "今天挑哪家呢" Then ComboBox3.Locked = False ComboBox3.Text = "菜單" End If If ComboBox3.Text = "菜單" Then CommandButton1.Caption = "等候點餐" CommandButton1.Enabled = False CommandButton1.Locked = True End If End Sub Private Sub ComboBox2_Change() '選店家名稱後載入菜單 ComboBox3.Clear If ComboBox2.Text = "寬中港" Then For i = 2 To 18 ComboBox3.AddItem Sheet2.Cells(i, 1) & Sheet2.Cells(i, 2) Next ElseIf ComboBox2.Text = "佳佳" Then For i = 2 To 15 ComboBox3.AddItem Sheet2.Cells(i, 3) & Sheet2.Cells(i, 4) Next ElseIf ComboBox2.Text = "KISS快餐" Then For i = 2 To 25 ComboBox3.AddItem Sheet2.Cells(i, 5) & Sheet2.Cells(i, 6) Next ElseIf ComboBox2.Text = "向陽樓麵食館" Then For i = 2 To 30 ComboBox3.AddItem Sheet2.Cells(i, 7) & Sheet2.Cells(i, 8) Next ElseIf ComboBox2.Text = "鑫鮮園鐵板燒烤" Then For i = 2 To 32 ComboBox3.AddItem Sheet2.Cells(i, 9) & Sheet2.Cells(i, 10) Next End If ComboBox2.Locked = True If ComboBox1.Text = "號碼?" Then ComboBox3.Text = "請選擇號碼" Else ComboBox3.Locked = False ComboBox3.Text = "菜單" End If CommandButton2.Locked = False End Sub Private Sub ComboBox3_Change() '點過餐才可以新增 If ComboBox3.Text <> "請選擇號碼" And ComboBox3.Text <> "菜單" Then CommandButton1.Locked = False CommandButton1.Enabled = True CommandButton1.Caption = "確定新增" End If End Sub Private Sub CommandButton1_Click() '將資料輸入表格並統計 Sheet1.Cells(number, 1) = ComboBox1.Text Sheet1.Cells(number, 2) = Left(ComboBox3.Text, Len(ComboBox3.Text) - 2) Sheet1.Cells(number, 3) = TextBox1.Text Sheet1.Cells(number, 4) = Right(ComboBox3.Text, 2) * TextBox1.Text number = number + 1 Sheet1.Cells(number, 1) = "共計" Sheet1.Cells(number, 2) = ComboBox2.Text Sheet1.Cells(number, 3) = "=SUM(C2:C" & number - 1 & ")" Sheet1.Cells(number, 4) = "=SUM(D2:D" & number - 1 & ")" For i = 1 To total - 1 If Sheet1.Cells(i, 6) = Left(ComboBox3.Text, Len(ComboBox3.Text) - 2) Then Sheet1.Cells(i, 7) = Sheet1.Cells(i, 7) + TextBox1.Text Sheet1.Cells(total, 6).Clear Sheet1.Cells(total, 7).Clear GoTo OK Else Sheet1.Cells(total, 6) = Left(ComboBox3.Text, Len(ComboBox3.Text) - 2) Sheet1.Cells(total, 7) = TextBox1.Text End If Next total = total + 1 OK: End Sub Private Sub CommandButton2_Click() MsgBox (ComboBox3.List(Rnd * 100 Mod ComboBox3.ListCount)) End Sub Private Sub CommandButton3_Click() '清空重新點菜用的 Sheet1.Range("A2:G32").Clear number = 2 total = 2 ComboBox1.Text = "號碼?" ComboBox2.Locked = False ComboBox2.Text = "今天挑哪家呢" ComboBox3.Text = "--" ComboBox3.Locked = True CommandButton1.Caption = "等候點餐" CommandButton1.Enabled = False CommandButton1.Locked = True CommandButton2.Locked = True End Sub -- ︴ 尼普藍得的守護靈 托那魯啊. ﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎ ︴ 這裡有一位獲得你完整加護的勇士. ﹎﹎﹎﹎﹎﹎﹎ ︴ 請你回顧記憶亞妮莉夏 美菈蓓死亡並立下誓約的瞬間, ﹎﹎﹎﹎﹎﹎﹎ ︴ 這裡有證明你們存在的言語, ﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎ ︴ 歐半婆羅 羅貝 易險 萊貝雅 ﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎﹎ ╔╕ ┌╥ ︴ 現在開啟道路吧... ╘╝ └╯ --

※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 59.126.52.79 ※ yinjing:轉錄至看板 EZsoft 12/11 22:29 ※ 編輯: yinjing 來自: 59.126.52.79 (12/11 22:32)