VBA代码在Excel工作表中制作简易的点餐系统

VBA代码在Excel工作表中制作简易的点餐系统

操作思路：

1、制作工作表《菜单》和《房间号》，《菜单》工作表A列为

菜单列表，双击菜单单元格，该菜单前□变■,并在最后一列计算选

中菜单价格的合计数，再次双击选中的菜单，该菜单前■变口，该菜

单取消；《房间号》工作表A列为所有可就餐房间号，B列为可用房

间号，C列为已用房间号。

2、双击单元格点餐，工作表最后一行显示所选菜单的合计金额；

3、选择可用房间的下拉菜单，选择房间号，点击出单，自动添

加以房间号命名的工作表名称，同时选中的菜单和单价及合计金额有

新工作表中显示；若不选择房间号，直接点击出餐，会弹出Msgbox

提示先选择房间号；

4、《房间号》工作表中的B列已用房间号消失，C列添加已用

房间号；

5、已用房间加菜，先双击需添加有单，选择加餐房间号的下拉

列表，点“加餐”按钮，所添加菜单在相应房间号的工作表行后列示；

6、点击“重置系统”，《房间号》工作表B列为所有房间号，C

列数据为空。

本系统还可优化，如将结账的房间号做特别标记，计算当日、当

月的营收，甚至可以做一个记账系统，将两个系统关联等，若有兴趣

可以一试。

视频效果请点击＜VBA在Excel工作表在制作简易点餐系统》

代码如下：

1、菜单工作表内代码

PrivateSubWorksheet_SeIestionChange(ByVaITargetAsRange)'添加下拉菜单

DimRowscountBAsInteger,RowscountCAsInteger

IfTarget.Row=2AndTarget.Column=5AndTarget.Count=1Then

RowscountB=Sheets("房间号Cel(Rows.Count,2).End(xIUp).Row

WithTarget.Vaiidation'下拉菜单

.Delete

.AddType:=xIVaIidateList,AlertStyle:=xlValidAlertStop,Formulal:="=房间

号！$B$2：$B$"&RowscountB

.IgnoreBlank=True

EndWith

EndIf

IfTarget.Row=3AndTarget.Column=5AndTarget.Count=1Then

RowscountC=Sheets("房间号CelIs(Rows.Count,3).End(xIUp).Row

WithTarget.Vaiidation'下拉菜单

.Delete

.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Formulal:="=房间

号！$C$2：$C$“&RowscountC

.IgnoreBlank=True

EndWith

EndIf

EndSub

2、ThisWorkbook内代码：

PrivateSubY/orkbook_SheetBeforeDoubIeCIick(ByVaIShAsObject,ByVaiTargetAsRange,CanceI

AsBoolean)

DimMenuAsString

DimMenutmpAsString

DimMenuAAsString

DimMenuBAsString

DimMenutmpBAsString

DimRowscountAsInteger

DimjiageAsInteger

DimRowsMenuAsInteger

IfActiveSheet.Name="菜单"Then

Rowscount=ActiveSheet.[A1].End(xIDown).Row

Menu=CelIs(Target.Row,Target.Column).Value

Menutmp=Left(Menu,1)

MenuA=Replace(Menu,Menutmp,"")

RowsMenu=0

jiage=0

IfMenutmp=Then

Menutmp=

CelIs(Target.Row,Target.Column)=Menutmp&MenuA

Else

Menutmp-

CelIs(Target.Row,Target.Column)=Menutmp&MenuA

EndIf

Fori=2ToRowscount

MenuB=Cells(i,1).Value

MenutmpB=Left(MenuB,1)

IfMenutmpB="H"Than

jiage=jiage+Ce11s(i,2).Value

EndIf

Next

IfSheets("菜单").Cells(Rowscount,1)<>“合计"Then

Sheets("菜单").CelIsRowscount+1,1)="合计”

Sheets("菜单").CelIs(Rowscount+1,2)=jiage

Else

Sheets("菜单").CelIs(Rowscount,2)=jiage

EndIf

EndIf

EndSub

3、模块内代码：

SubAddroom0

DimMenuAsString

DimMenutmpAsString

DimMenuAAsString,MenuBAsString

DimRowscountAsInteger

DimjiageAsString

DimfangjianAsString

DimfangjianEmptyAsBoolean

DimiAsInteger,jAsInteger,totalAsInteger

DimwsAsWorksheet

OnErrorResumeNext

AppIication.DispIayAIerts=FaIse

fangjian=Sheets("菜单").CelIs(2,5).VaIue

Iffangjian=""Then

MsgBox「请选择房间号")

ExitSub

EndIf

Rowscount=Sheets("菜单”).[A1].End(xIDown).Row

J=3

Fori=2ToRowscount-1'判断房间是否有人

Menu=Sheets("菜单”).Cells(i,1).Value

Menutmp=Left(Menu,1)

IfMenutmp=Then

CalIfangjianIsEmpty(fangjianEmpty,fangjian)

ExitFor

EndIf

Next

Fori=2ToRowscount-1'点冬

Menu=Sheets("菜单”).Cells(i,1).Value

jiage=Sheets(■菜单”).Cells(i,2).Value

Menutmp=Left(Menu,1)

IfMenutmp=Then

MenuA=RepIace(Menu,Menutmp,"")

MenuB=&MenuA

Sheets("菜单").CelIs(i,1)=MenuB

Sheets(fangjian).Cells(j,1)=MenuA

Sheets(fangjian).Cells(j,2)=jiage

totaI=totaI+Clnt(jiage)

j=j+1

EndIf

Next

Sheets(fangjian).CelIs(j,1)="合计"

Sheets(fangjian).Ce11s(j,2)=totaI

SheetsC•菜单CelIs(Rowscount,1)=""'清空合计和总金额

Sheets("菜单").CelIs(Rowscount,2)=""

Ca11fanjianhaoAddOrDel(fangjian)

Sheets("菜单").CelIs(2,5).Value=""

Sheets(fangjian).Activate

EndSub

SubAddMenus0

DimRowscountAsInteger

DimfangjianRowsAsInteger

DimfangjianAsString

OnErrorResumeNext

AppIication.DispIayAIerts=FaIse

fangjian=Sheets("菜单").CelIs(3,5).VaIue

Iffangjian=""Then

MsgBox「请选择房问号")

ExitSub

EndIf

Rowscount=Sheets("菜单").[A1].End(xIDown).Row

fangjianRows=Sheets(fangjian).[A1].End(xIDown).Row

Fori=2ToRowscount-1'点系

Menu=Sheets("菜单”).Cells(i,1).Value

jiage=SheetsC•菜单”〔Cells(i,2).Value

Menutmp=Left(Menu,1)

IfMenutmp=Then

MenuA=RepIace(Menu,Menutmp,"")

MenuB=&MenuA

Sheets("菜单").CelIs(i,1)=MenuB

Sheets(fangjian).CelIs(fangjianRows,1)=MenuA

Sheets(fangjian).Cells(fangjianRows,2)=jiage

fangjianRows=fangjianRows+1

EndIf

Next

Fori=2TofangjianRov/s-1

jiage=Sheets(fangjian).CeIIs(i,2)

total=total+Clnt(jiage)

Next

Sheets(fangjian).Cells(fangjianRows,1)="合计"

Sheets(fangjian).Cells(fangjianRows,2)=total

SheetsC1菜单[.Cells(Rowscount,1)=""'清空合计和总金颔

Sheets.Ce11s(Rowscount,2)=""

SheetsC1菜单”).Cell$(3,5)=""

Sheets(fangjian).Activate

EndSub

SubrefreshO'重置.系统

DimRowscountAAsInteger

DimiAsInteger

RowscountA=Sheets(“房间号").[A1].End(xIDown).Row

Fori=2ToRowscountA

SheetsC•房间号“).Cells(i,2)=Sheets。'房间号CelIs(i,1).Value

Sheets^房间号TCells(i,3)=""

Next

EndSub

FunctionfanjianhaoAddOrDel(fangjianhaoAsString)’添加、榭除房间号的下拉菜单

DimRowscountBAsInteger,RowscountCAsInteger

DimiAsInteger

OnErrorResumeNext

RowscountB=Sheets("房间号").[B1].End(xIDown).Row

RowscountC=SheetsC"房间号").[C1].End(xlDown).Row

IfRowscountC=0Then

Rov/scountC=1

EndIf

Fori=2ToRowscountB

IfSheets("房间号").CelIs(i,2).VaIue=fangjianhaoThen

Sheets("房间号").Activate

Sheets("房间号").Ce11s(i,2).Select

Selection.DeleteShift:=xlUp

Sheets("房间号").CelIs(RowscountC+1,3).VaIue=fangjianhao

RowscountC=RowscountC+1

ExitFunction

EndIf

Next

EndFunction

FunctionfangjianIsGnpty(fangjiatiDnptyAsBoolean,fangjianAsString)'判断房间足否已点

餐

DimwsAsWorksheet

ForEachwsInWorksheets

Ifws.Name=fangjianTnen

fangjianEmpty=FaIse

ExitFor

Else

fangjianEmpty=True

EndIf

Next

IffangjianEmpty=TrueTnen'判断房间号是否是点装

Sheets.AddAfter:=Sheets(Sheets.Count)

Sheets(Sheets.Count).Nane=fangjian

Sheets(fangjian).Ce11s(1,1)=fangjian

Sheets(fangjian).Ce11s(2,1)="菜单"

Sheets(fangjian).Ce11s(2,2)="价格"

Else

MsgBox(fangjian&-房间已有人，是否加菜")

ExitFunction

EndIf

EndFunction

代码截图:

Friv«t«SubYork£h«et_Sel«cti(ByV^l[*Mg.qAsRui<e)'：5^1)下及菜单

DitiRaAseountBAsRovscountCAsIz-g"

IfTargetR”=2AndTwg«t.Colurat=5AndTarget.Count=1Than

RoazcouxitB-Sha«txCS§-^*).C«115®ovsCount.2).Ind(xlVp)Rw

tilthTirg«tValidation，下粒菜电

AddTyp«:=xlV»li<l.t«List,Al«rtStyle：：xlV*lidAltrtStop.Forr.nl«1="二国词号!$BS23B$"&EgzcostB

.IcnoreBltitk=True

EndWith

EndI£

IfTargetR"=3AndTar«tColunn=5AndTarcet.Coxmt=1Then

RawscountC=SheerC•房且号DC-llsQUvsCount,3).En4GclVp)Rw

thTir&etValidation'下抬至单

・D"*g

AddType=xlVdidUtListzAltrtStyl•:=xlVtlidAlwrtStdp,Fc.rr.ulat:“二房同售I冥#$CTtEKMOUMC

.I^nor«Bl<nk=Tru«

EndWith

EndIf

IndSub

PrivateSubWorkbook_SheetBe£oreDoubleClick(ByVcd.ShAsObject,ByVcd.TarcetAsRange,CancelAsBoolean)

DinMenuAsStriae

DirtMonuUipA:String

DinHoniiAAsString

DioW«nuBMString

D>oM«nut»pBAsSiring

DBGRovscountAsIntec«r

D>t»jiaceAsInteger

DinRovsHenuAsInteger

IfActiveSheet.Hane=单"Then

Rovzcount=ActivaSh««t.[Al].End(xlDwrn)Eg

M«nu2CallsRov,T&r^«tCOIXMA).ZAIUA

Menutnp=L«CQhnu,1)

MenuA=ReplaceGlenu,Henutsp,**)

RovsNenu=0

jiace=0

IfNenutnp=*□**Then

!Unut»p2

C«lls(Twget.R«>v,Target.Column)=H«natnptManuA

Kite

M«nut»p=*□*

Cells(TarcetRw,Tar(el.Column)=Nenatnp&WenuA

EndI£

Fori=2IoRovzcoxmt

McnuB=CellsGz1).Value

MonuspB=L«ft(JHanuB,1)

IfThen

=>“•♦CaicG,2)V«lu«

EndIf

Next

IfSheetsrS**).Cells®wscountz1)◊"台沙’Then

Sheetz(*S***).Cells(Rarzcount*1,1)="合计’

Sh««tzCells(Bwzcount+1,2)=ji«g«

Els«

SheetsC«llsA^rscoun^2)=jiag*

EndIf

EndIf

EndSub

SubAddroom()

DimMenuAsString

DimMenutmpAsString

DimMenuAAsString,MenuBAsString

DimRowscountAsInteger

DimjiageAsString

DimfangjianAsString

DimfangjianEmptyAsBoolean

DimiAsInteger,jAsInteger,totalAsInteger

DimwsAsWorksheet

OnErrorResumeNext

Application.DisplayAlerts=False

fangjian=Sheets("菜单").Cells②5).Value

Iffangjian-""Then

MsgBox(“请选择南目号")

ExitSub

EndIf

Rowscount=Sheets("菜单").：A1].EndCxlDown).Row

j=3

Fori=2ToRowscount-1，判断房间是否有人

Menu=Sheets("菜单").Cells(i,1).Value

Menutmp=Left(Menu,1)

IfMenutmp="■*Then

CallfangjianlsEmpty(fangjianEmpty,fangjian)

ExitFor

EndIf

Next

Fori=2ToRowscount-1，点餐

Menu=SheetsCells(i,1).Value

jiage=Sheets("菜单").Cells(i,2).Value

Menutmp=Left(Menu,1)

IfMenutmp="■〜Then

MenuA=Replace(Menu,Menutmp,

MenuB=&MenuA

Sheets("菜单").Cells(i,1)=MenuB

Sheets(fangjian).Cells(j,1)=MenuA

Sheets(fangjian).Cells(j,2)=jiage

total=total+CInt(jiage)

j=j+1

EndIf

Next

Sheets(fangjian).Cells1)="合计”

Sheets(fangjian).Cells(j,2)=total

Sheet*"菜箪").Cells(Rowscount,1)=""'清空合计和总金额

Sheets("菜单"),CellsSowscount,2)=**

CallfanjianhaoAddOrDel(fangjian)

Sheets("蕖单").Cells(2,5).Value=**

Sheets(fangjian).Activate

SubAddMenus0

DimRowscountAsInteger

DimfangjianRowsAsInteger

DimfangjianAsString

OnErrorResumeNext

Application.DisplayAlerts=False

fangjian=Sheets("菜单"),Cells(3,5).Value

Iffangjian=""Then

MsgBox(“请选择用而号")

ExitSub

EndIf

Rowscount=Sheets(“菜单").[Al].End(xlDown).Row

fan$jiwRows=Sheets(fanfjian),[Al].EndGclDown).Row

Fori=2ToRowsconnt-1，点餐

Menu=SheetsCells1).Value

=sh“t*「菜单”)・C.n*G,2),vau«

Menutmp=Left(Menu,1)

IfMenutmp=Then

M«nuA=Replace(M*nu,hhiur.mp,"")

MenuB=*□**&MenuA

Sheets「菜单").Cells(i,1)=MenuB

Shttts(fangjitn).Ctlls(fanqitnRows,1)=MtnuA

Sheets(fangjian).Cells(fangjianRows,2)=jiage

fangjianRows=fangjianRows+1

EndIf

Next

Fori=2TofangjiaitRows-1

jit(<=Sheets(ftngjitn).Ctlls(i,2)

total=total+C工ntGiage)

Next

Sheets(fangjitn).Cells(fangj:anRows,1)="合计"

Sheets(fangjian).Cells(fangjianRows2)=total

SheetsCgS").Cells(Rowscount,1)=/"'清空合计和总金额

SheetsCellsCRowscount,2)=""

SheetN"菜单p.CeUsG5)=""

Sheets(fangjian).Activate

EndSub

Subre£reshb^>责系统

DimRowscountAAsInteger

DimiAsInteger

RowscountA=Sheets(“房间号").[Al].End(xlDoirri).Row

Fori=2ToRowscoun