|
1、在UserForm1里,，UserForm_Initialize窗體初始化： Dim arr()Dim LvItem As ListItemDim tbTitle()Dim arrPrice()Private Sub UserForm_Initialize() Dim ws As Worksheet Dim lastRow As Integer, lastCol As Integer Dim iWidth() Set ws = ThisWorkbook.Sheets("打印數(shù)據(jù)") With ws .Activate lastRow = .UsedRange.Rows.Count lastCol = .UsedRange.Columns.Count arr = ws.Range(Cells(2, 3), Cells(lastRow, lastCol)).Value End With k = 0: m = 0 For i = 1 To UBound(arr, 2) If arr(1, i) <> "" Then ReDim Preserve tbTitle(k) tbTitle(k) = arr(1, i) k = k + 1 End If If arr(2, i) <> "" Then ReDim Preserve arrPrice(m) arrPrice(m) = arr(2, i) m = m + 1 End If Next tbTitle(0) = "下單時(shí)間" tbTitle(1) = "下單地區(qū)" tbTitle(4) = "送貨地址" tbTitle(10) = "商品總價(jià)" tbTitle(11) = "備注說明" tbTitle(12) = "支付狀態(tài)" iWidth = Array(60, 60, 60, 60, 60, 60, 60, 60, 60, 60, 60, 60, 60, 60, 60, 60, 30) For i = 0 To UBound(tbTitle) Me.LvDetail.ColumnHeaders.Add , , tbTitle(i), iWidth(i) Next With Me.LvDetail .View = lvwReport .Gridlines = True '.Sorted = True .CheckBoxes = True .LabelEdit = lvwManual .FullRowSelect = True .ForeColor = vbBlue End With For i = 3 To UBound(arr) If arr(i, 1) <> "" Then Set LvItem = Me.LvDetail.ListItems.Add LvItem.Text = arr(i, 1) For j = 1 To Me.LvDetail.ColumnHeaders.Count - 1 LvItem.SubItems(j) = arr(i, j + 1) Next End If NextEnd Sub
（1）Line1~4，定義模塊級數(shù)組arr,，tbTitle,，arrPrice用來存放明細(xì)訂單、表頭字段,、單價(jià),。
（2）line10~15，把“打印數(shù)據(jù)”表存入數(shù)組arr,。（3）line17~28,，循環(huán)數(shù)組arr，把表頭字段存入tbTitle,，單價(jià)存入arrPrice,。 （4）line29~34，“打印數(shù)據(jù)”表字段與“小票模板”字段不一致,，進(jìn)行調(diào)整,。（5）line35,，一個(gè)設(shè)置寬度數(shù)值的數(shù)組，要改變ListView字段的顯示寬度,，在這里調(diào)整,。（6）line36~38，設(shè)置ListView的表頭字段,。（7）line39~47,，設(shè)置ListView的屬性。（8）line48~56,，把a(bǔ)rr中的數(shù)據(jù)添加到ListView,。2、在UserForm1里,，CmdPrint打印按鈕點(diǎn)擊事件： Private Sub CmdPrint_Click() On Error Resume Next Dim pos1 As Integer, pos2 As Integer Dim num As Integer, arrStr() Dim ws As Worksheet, wsPrint As Worksheet Set ws = ThisWorkbook.Sheets("小票模板") Set wsPrint = ThisWorkbook.Sheets("Print") pos1 = Pxy(tbTitle, "主機(jī)") pos2 = Pxy(tbTitle, "音箱") With Me.LvDetail For i = 1 To .ListItems.Count If .ListItems(i).Checked Then k = k + 1 End If Next If k = 0 Then MsgBox "未勾選任何小票,！" Exit Sub End If If Not wContinue("即將打印所選小票！") Then Exit Sub If Application.Dialogs(xlDialogPrinterSetup).Show = False Then Exit Sub End If For i = 1 To .ListItems.Count If .ListItems(i).Checked Then num = 0 For j = pos1 To pos2 If Len(.ListItems(i).SubItems(j - 1)) > 0 Then num = num + 1 End If Next memo = .ListItems(i).SubItems(Pxy(tbTitle, "備注說明") - 1) wsPrint.Cells.Clear ws.Range("B2:D14").Copy Destination:=wsPrint.Range("B1") wsPrint.Cells(3, 3) = .ListItems(i).Text For j = 2 To 13 itemname = Replace(wsPrint.Cells(j, 2), "：", "") ipos = Pxy(tbTitle, itemname) If j <> 3 And j <> 8 And j <> 9 Then wsPrint.Cells(j, 3) = .ListItems(i).SubItems(ipos - 1) End If Next If num > 0 Then If num > 1 Then wsPrint.Rows(10 & ":" & 10 + num - 2).Insert Shift:=xlDown End If k = 0 For j = pos1 To pos2 If Len(.ListItems(i).SubItems(j - 1)) > 0 Then wsPrint.Cells(9 + k, 2) = tbTitle(j - 1) wsPrint.Cells(9 + k, 3) = arrPrice(j - pos1) wsPrint.Cells(9 + k, 4) = .ListItems(i).SubItems(j - 1) k = k + 1 End If Next wsPrint.PrintOut Copies:=1 Application.Wait Now + TimeSerial(0, 0, 0.5) ElseIf Len(memo) > 0 Then For j = pos1 To pos2 If InStr(memo, tbTitle(j - 1)) Then ReDim Preserve arrStr(num) arrStr(num) = tbTitle(j - 1) num = num + 1 End If Next If num > 1 Then wsPrint.Rows(10 & ":" & 10 + num - 2).Insert Shift:=xlDown End If For j = LBound(arrStr) To UBound(arrStr) wsPrint.Cells(9 + j, 2) = arrStr(j) wsPrint.Cells(9 + j, 3) = arrPrice(Pxy(tbTitle, arrStr(j)) - pos1) Next wsPrint.PrintOut Copies:=1 Application.Wait Now + TimeSerial(0, 0, 0.5) End If End If Next End With MsgBox "打印完畢,！" Unload MeEnd Sub
（1）Line3~5,，定義一些變量、數(shù)組,、工作表對象等,。
（2）line8~9，取得第一個(gè)與最后一個(gè)商品名稱的位置,。 （3）line11~19,，判斷是否勾選訂單記錄。 （4）line21~23,，選擇打印機(jī),，如果取消則退出程序。 （5）line27~31,，統(tǒng)計(jì)訂單商品的種類數(shù),。 （6）line32，備注字段的值,。 （7）line33~42,，清除工作表“Print”的內(nèi)容，根據(jù)小票模板中的字段,，把當(dāng)前訂單的相應(yīng)字段值填入打印模板,。 （8）line43~57，如果訂單有數(shù)量,，如果商品種類數(shù)大于1,，則插入行。再循環(huán)商品名稱,，查找有數(shù)量的記錄,，把商品名稱,、單價(jià)、數(shù)量填入打印模板,。打印。 （9）line58~74,，如果訂單商品的種類數(shù)為0,，但備注有內(nèi)容，則檢查備注字段中商品名稱的種類數(shù),，寫入一個(gè)數(shù)組arrStr,。如果商品種類數(shù)大于1，同樣地,，需要插入記錄,。循環(huán)arrStr，把商品名稱寫入打印模板,，把其對應(yīng)的單價(jià)寫入打印模板,。打印。 3,、在UserForm1里,，全選、退出按鈕及兩個(gè)自定義函數(shù)： Private Sub CmdSelectAll_Click() With Me.LvDetail If Me.CmdSelectAll.Caption = "全選" Then For i = 1 To .ListItems.Count .ListItems(i).Checked = True Next Me.CmdSelectAll.Caption = "全消" Me.CmdSelectAll.BackColor = RGB(176, 224, 230) Else For i = 1 To .ListItems.Count .ListItems(i).Checked = False Next Me.CmdSelectAll.Caption = "全選" Me.CmdSelectAll.BackColor = RGB(143, 188, 143) End If End WithEnd Sub
Private Sub CmdExit_Click() Unload MeEnd Sub
Function Pxy(arr() As Variant, searchValue As Variant) As Long t = LBound(arr) t = 1 - t For i = LBound(arr) To UBound(arr) If arr(i) = searchValue Then Pxy = i + t Exit Function End If Next Pxy = -1 ' 如果未找到值,，則返回 -1End Function
Function wContinue(Msg) As Boolean '確認(rèn)繼續(xù)函數(shù) Dim Config As Long Config = vbYesNo + vbDefaultButton2 + vbQuestion Ans = MsgBox(Msg & Chr(10) & "是(Y)繼續(xù)?" & Chr(10) _ & "否(N)返回,！", Config, "請確認(rèn)操作！") wContinue = Ans = vbYesEnd Function
（1）Line1~17,，CmdSelectAll按鈕點(diǎn)擊事件,，按鈕的Caption在“全選“、”全消“,，之間切換,，同時(shí)ListView的Listitems也相應(yīng)地在“全部不選”和“全部選中”的狀態(tài)之間切換，方便選擇,。這段代碼直接復(fù)制以前案例中的代碼,，修改一個(gè)Listview的Name即可。
（2）line19~21,，退出用戶窗體,。 （3）line23~33，數(shù)組元素字段定位函數(shù),，這個(gè)函數(shù)在我們的案例中使用得也非常多,，直接復(fù)制過來。這個(gè)函數(shù)的方便之處是,，我們可以根據(jù)字段名稱取得其位置,，作為數(shù)組,、ListView的Subitems的下標(biāo)，不用到表格中去數(shù)數(shù),，另外,，即便表格字段的位置發(fā)生變化，我們的程序代碼也可以不用修改,。 （4）line35~42,，確認(rèn)繼續(xù)函數(shù)，防止誤操作,，給一個(gè)反悔的機(jī)會(huì),。這個(gè)函數(shù)本身沒有太大的實(shí)質(zhì)性作用，但是增加的程序的靈活性,。這個(gè)函數(shù)我們也用得比較多,。 4、在工作表“打印數(shù)據(jù)”里,，打印命令按鈕： Private Sub CmdPrint_Click() UserForm1.ShowEnd Sub
|
