Excel Forum

Code: Select all

Sub Print_List_Range_Cancelable()
    Dim strValidationRange As String
    Dim rngValidation As Range
    Dim rngDepartment As Range
    Dim startRange As Integer
    Dim endRange As Integer
    Dim Cancelled As Boolean
    

    startRange = InputBox("ลำดับแรก", "ระบุช่วงของจำนวนที่ต้องการพิมพ์")
    If startRange = 0 Then
        Exit Sub
    End If
    
  
    endRange = InputBox("ลำดับสุดท้าย", "ระบุช่วงของจำนวนที่ต้องการพิมพ์")
    If endRange = 0 Then
        Exit Sub 
    End If
    
  
    Application.EnableCancelKey = xlInterrupt
    
    Application.ScreenUpdating = False
    
    strValidationRange = Range("I5").Validation.Formula1
    Set rngValidation = Range(strValidationRange)
    
    For Each rngDepartment In rngValidation.Cells
        If rngDepartment.Value >= startRange And rngDepartment.Value <= endRange Then
            Range("I5").Value = rngDepartment.Value
            On Error GoTo CancelPrint
            ActiveSheet.PrintOut
            On Error GoTo 0
            If Cancelled Then
                Exit For
            End If
        End If
    Next
    
    Application.EnableCancelKey = xlErrorHandler
    Application.ScreenUpdating = True
    
    Exit Sub
    
CancelPrint:
    Cancelled = True 
    Application.EnableCancelKey = xlErrorHandler 
    MsgBox "การพิมพ์ถูกยกเลิกโดยผู้ใช้", vbInformation
    Resume Next
End Sub