Excel Forum

Code: Select all

Private Sub UserForm_Initialize()
Dim i As Integer
Workbooks("ฐานข้อมูล.xlsx").Worksheets("Temp").Range("A2:H" & Rows.Count).ClearContents
For i = 1 To 50
    FormMenu.cboTable.AddItem i
Next i
End Sub

Code: Select all

Private Sub txtF1_Change()
Dim r As Range
Dim rTemp As Range
On Error Resume Next
Set r = Workbooks("ฐานข้อมูล.xlsx").Worksheets( _
            "อาหาร").Range("B2:C8")
Set rTemp = Workbooks("ฐานข้อมูล.xlsx").Worksheets( _
            "Temp").Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
With FormMenu.MultiPage1.Pages("Page1")
    If .txtF1 = "" Then
        .lblF1 = ""
        rTemp.Offset(-1, 0).EntireRow.ClearContents
    Else
        .lblF1.Caption = .txtF1.Text * Application.VLookup("ปลาทับทิมนึ่งมะนาว", r, 2, 0)
        rTemp = "ปลาทับทิมนึ่งมะนาว"
        rTemp.Offset(, 1) = txtF1.Text
        rTemp.Offset(, 2) = .lblF1.Caption
        rTemp.Offset(, -4) = FormMenu.lblDate.Caption
        rTemp.Offset(, -3) = FormMenu.lblTime.Caption
        rTemp.Offset(, -2) = FormMenu.cboTable.Text
        rTemp.Offset(, -1) = FormMenu.txtMember.Text
    End If
End With
End Sub

Code: Select all

Private Sub btnOK_Click()
Dim rs As Range
Dim rt As Range
Dim s As String
s = Application.Text(Date, "dดดดดbb")
With Workbooks("ฐานข้อมูล.xlsx").Worksheets("Temp")
    Set rs = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 7))
End With
With Workbooks("ฐานข้อมูล.xlsx").Worksheets(s)
    Set rt = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With
    rs.Copy
    rt.PasteSpecial xlPasteValues
    MsgBox "บันทึกรายการอาหารเรียบร้อยแล้ว"
    Application.CutCopyMode = False
    Unload Me
End Sub

Code: Select all

Private Sub UserForm_Initialize()
Dim i As Integer
Workbooks("ฐานข้อมูล.xlsx").Worksheets("Temp").Range("A2:H" & Rows.Count).ClearContents
For i = 1 To 50
    FormMenu.cboTable.AddItem i
Next i
    Me.lblDate = Date
    Me.lblTime = Time
With Application
    Me.Width = .Width
    Me.Height = .Height
    Me.Top = .Top
    Me.Left = .Left
End With
End Sub

Code: Select all

rTemp.Offset(, 4) = "รอชำระเงิน"

Code: Select all

Private Sub txtB1_Change()
Dim r As Range
Dim rTemp As Range
On Error Resume Next
Set r = Workbooks("ฐานข้อมูล.xlsx").Worksheets( _
            "อาหาร").Range("F2:G7")
Set rTemp = Workbooks("รายงานประจำเดือน.xlsx").Worksheets( _
            "Temp").Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
With FormMenu.MultiPage1.Pages("Page5")
    If .txtB1 = "" Then
        .lblB1 = ""
        rTemp.Offset(-1, 0).EntireRow.ClearContents
    Else
        .lblB1.Caption = .txtB1.Text * Application.VLookup("เนื้อผัดพริกไทยดำ", r, 2, 0)
        rTemp = "เนื้อผัดพริกไทยดำ"
        rTemp.Offset(, 1) = txtB1.Text
        rTemp.Offset(, 2) = .lblB1.Caption
        rTemp.Offset(, 4) = "รอชำระเงิน"
        rTemp.Offset(, -4) = FormMenu.lblDate.Caption
        rTemp.Offset(, -3) = FormMenu.lblTime.Caption
        rTemp.Offset(, -2) = FormMenu.cboTable.Text
        rTemp.Offset(, -1) = FormMenu.txtMember.Text
    End If
End With
End Sub

Code: Select all

Private Sub btnShow_Click()
Dim rt As Range, rs As Range, rsall As Range
Dim s As String, RowSrc As String, rtp As Range
s = Application.Text(Date, "dดดดดbb")
Worksheets("Temp").Range("A:I").ClearContents
With Workbooks("รายงานประจำเดือน.xlsx").Worksheets(s)
    Set rsall = .Range("C2", .Range("C" & Rows.Count).End(xlUp))
End With
For Each rs In rsall
    If ob1.Value = True Then
            If rs = CLng(Me.TextBox1) And CLng(rs.Offset(0, -2)) = CLng(Date) _
                And rs.Offset(0, 6) = Me.ob1.Caption Then
                Set rt = ThisWorkbook.Worksheets("Temp") _
                     .Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                rs.Offset(0, -2).Resize(1, 9).Copy
                rt.PasteSpecial xlPasteValues
            End If
      ElseIf ob2.Value = True Then
            If rs = CLng(Me.TextBox1) And CLng(rs.Offset(0, -2)) = CLng(Date) _
                And rs.Offset(0, 6) = Me.ob2.Caption Then
                Set rt = ThisWorkbook.Worksheets("Temp") _
                    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                rs.Offset(0, -2).Resize(1, 9).Copy
                rt.PasteSpecial xlPasteValues
            End If
       End If
Next rs
With Worksheets("Temp")
    Set rtp = .Range("A2", .Range("I" & Rows.Count).End(xlUp))
End With
RowSrc = "Temp!" & rtp.Address
Me.ListBox1.RowSource = RowSrc
End Sub

Private Sub UserForm_Initialize()
Me.lblDate = Date
Me.ListBox1.RowSource = ""
With Application
    Me.Width = .Width
    Me.Height = .Height
    Me.Top = .Top
    Me.Left = .Left
End With
End Sub