[VBA ] 獻醜，簡陋的定便當 Excel VBA

[yinjing (青)]

載點：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)