Print data in sheet by userform based on selected item from combobox

[Abdo]

Hi
I have uerform contains combobox , the combobox contains "bigger than zero", " equal zero" , "all"
the data will be in Sheet1 from A1:E
the numeric will be in column E , if I select bigger than zero from combobox1 and click commandbutton1 then should print out the paper for data doesn't contain zero in column E and if I select equal zero from combobox1 and click commandbutton1 then should print out the paper for data contain zero in column E ,and if I select all from combobox1 and click commandbutton1 then should print out the paper for all of data contain zero or not in column E .
so I search for the code does it.
thanks

 

[DanteAmor]

Try this:

Private Sub CommandButton1_Click()
  With ComboBox1
    If .ListIndex = -1 Then
      MsgBox "Select combo"
      .SetFocus
      Exit Sub
    End If
  
    Select Case LCase(.Value)
      Case LCase("bigger than zero")
        Range("A:E").AutoFilter Field:=5, Criteria1:=">0"
      Case LCase("equal zero")
        Range("A:E").AutoFilter Field:=5, Criteria1:="0"
      Case LCase("all")
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    End Select
    ActiveSheet.PrintOut
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  End With
End Sub

 

[Abdo]

Hi
the code works prfectly , but there is case shouldn't do that
it supposes just print from A:E , but also print a second paper when threre is data after column E , how can I fix it,please

 

[DanteAmor]

What do you mean by a second paper? The macro prints the entire sheet. If you have delimited the printing area, you must remove that area so that the entire sheet is printed.


Or change "E" for the letter of last column.
Range("A:E").Autofilter

Or change Range("A:E").Autofilter by Rows(1).Autofilter

 

[Abdo]

I mean when prin the sheet should print the range from A:E , not print whole sheet. if there data are after E should not print . I see when there data are after column H then will be second page after column H . the first page will be A:H based on margin for every page in the sheet .

my goal ignore Any data after column E when print . so will just printing from A:E
I' surprised from the code doesn't do what I want despite of you specify whta exactly the columns should filter in your code! .

thanks again

 

[DanteAmor]

One thing is the filter and another thing is the impression.

To print only up to column E you must establish the printing area.

Or try this:

Private Sub CommandButton1_Click()
  Dim lr As Long
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Mode = False
  lr = Range("A:E").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  With ComboBox1
    If .ListIndex = -1 Then
      MsgBox "Select combo"
      .SetFocus
      Exit Sub
    End If
  
    Select Case LCase(.Value)
      Case LCase("bigger than zero")
        Range("A:E").AutoFilter Field:=5, Criteria1:=">0"
      Case LCase("equal zero")
        Range("A:E").AutoFilter Field:=5, Criteria1:="0"
      Case LCase("all")
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    End Select
    ActiveSheet.PageSetup.PrintArea = "A1:E" & lr
    ActiveSheet.PrintOut
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  End With
End Sub

 

[Abdo]

thanks again.
just I've found the problem for zero . the code doesn't deal with format number like this 0.00 ,but will deal with other numbers like 14,222.00 ,how I fix with zero,please?

 

[DanteAmor]

Try

Private Sub CommandButton1_Click()
  Dim lr As Long
 
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  lr = Range("A:E").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  With ComboBox1
    If .ListIndex = -1 Then
      MsgBox "Select combo"
      .SetFocus
      Exit Sub
    End If
 
    Select Case LCase(.Value)
      Case LCase("bigger than zero")
        Range("A:E").AutoFilter 5, ">0"
      Case LCase("equal zero")
        Range("A:E").AutoFilter 5, 0, xlOr, "0.00"
      Case LCase("all")
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    End Select
    ActiveSheet.PageSetup.PrintArea = "A1:E" & lr
    ActiveSheet.PrintOut
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  End With
End Sub

 

[Abdo]

everything is great !
I appreciate for your time & assistance.
thank you so much .