Snasui Excel Forum

Code: Select all

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = False
End Sub

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Save
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bookpath As String, bookname As String, tBook As Workbook
bookpath = "c:\plan\"
bookname = "planning.xlsm"
Set tBook = Workbooks.Open(Filename:=bookpath & bookname)  ' Add "(" and ")"
tBook.Worksheets("M01").Range("A3:Q4000").Value = _
     Workbooks("planning").Worksheets("M01").Range("A3:Q4000").Value
tBook.Close True
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bookpath As String, bookname As String, tBook As Workbook
Application.EnableEvents = True
bookpath = "c:\plan\"
bookname = "planning.xlsm"
Set tBook = Workbooks.Open(Filename:=bookpath & bookname)  ' Add "(" and ")"
tBook.Worksheets("M01").Range("A3:Q4000").Value = _
     Workbooks("planning").Worksheets("M01").Range("A3:Q4000").Value
tBook.Close True
Application.EnableEvents = False
End Sub

Code: Select all

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim xStr As String
    Dim xCombox As OLEObject
    Application.EnableEvents = False
    Set xCombox = ActiveSheet.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
        .Top = Target.Top
    End With
    If InStr("$C$7$C$12$C$17", Target.Address) = 0 Then
        Application.EnableEvents = True
        Exit Sub
    End If
   If Target.Address = "$C$7" Then
   xStr = "Sheet1!H2:H3"
   ElseIf Target.Address = "$C$12" Then
   xStr = "Sheet1!J2:J51"
ElseIf Target.Address = "$C$17" Then
   xStr = "Sheet1!G2:G10"
End If
    With xCombox
        .Visible = True
        .Left = Target.Left
        .Top = Target.Top
        .Width = Target.Width + 5
        .Height = Target.Height + 5
        .ListFillRange = xStr
        .LinkedCell = Target.Address
    End With
    xCombox.Activate
    Me.TempCombo.DropDown
    Application.EnableEvents = True
    
    End Sub
    
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim InputRange As Range, Target As Range
    Application.EnableEvents = False
    Set InputRange = Range("c2:c17")
    Set Target = Worksheets([C17].Value).Range("b" & Rows.Count).End(xlUp)
    For Each cll In InputRange
        Target.Offset(1, c).Value = cll.Value
        c = c + 1
    Next
    Range("c2:c17").Select
    'Application.CutCopyMode = False
    MsgBox "จัดเก็บข้อมูลเรียบร้อยแล้ว"
    Selection.ClearContents
    Range("C2").Select
    ThisWorkbook.Save
    Application.EnableEvents = True
End Sub