Disable manual printing just only done by macro-vba in excel

[sadaqatt]

I want to disable print for all users manually, Just print only from the macro assign button (want to use for small office billing invoices)

Sub PostToRegister()
Dim WS1 As worksheet
Dim WS2 As worksheet
Set WS1 = Worksheets("INVOICE")
Set WS2 = Worksheets("DATABASE")
WS2.Unprotect Password:="123"
WS1.Unprotect Password:="123"


' figure out which row is the next row
nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

' write the important values to DATABASE
WS2.Cells(nextrow, 1).Resize(1, 7).Value = Array(WS1.Range("E11"), WS1.Range("E12"), WS1.Range("B11"), _
WS1.Range("B17"), WS1.Range("E25"), WS1.Range("E26"), WS1.Range("E27"))



WS2.Protect Password:="123"
WS1.Protect Password:="123"


End Sub

 

[DanteAmor]

In the ThisWorkbook events, put the following code:

Option Explicit

Public EnableToPrint As Boolean

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  If EnableToPrint = False Then
    Cancel = True
    MsgBox "To print use the macro button"
  End If
End Sub

Ex:

In a module put the following macro:

Option Explicit

Sub MacroToPrint()
  ThisWorkbook.EnableToPrint = True
  Sheets("INVOICE").PrintOut
  ThisWorkbook.EnableToPrint = False
End Sub

Ex:

In the sheet button assign the macro "MacroToPrint".

----- --

 

[sadaqatt]

Sorry, it showing errors, I am really struggling with this, and i cannot attach the screenshot showing too large,
kindly send me the your email so I can explain you in detail.

 

[DanteAmor]

it showing errors

Which error, on which line, it is not enough to say "it showing errors".
I have attached my test file, which does what you asked for in your initial post:

I want to disable print for all users manually, Just print only from the macro assign button

Try this:

test to print.xlsm

INVOICE header some

docs.google.com

 

[sadaqatt]

Hi, Thanks for you valueable time, I am adding all in one workbook but facing error like "Only comments may appear after End Sub, End Function, or End Property"... I have succeeded in disabling the worksheets but facing issue in printing it from the same macro worksheet which I asign it to the shape button.

Is there any way to upload the excel sheet here?

Sub PostToRegister() FACING ERROR HERE
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("INVOICE")
Set WS2 = Worksheets("DATABASE")
WS2.Unprotect Password:="41297"
WS1.Unprotect Password:="41297"

' figure out which row is the next row
nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

' write the important values to DATABASE
WS2.Cells(nextrow, 1).Resize(1, 7).Value = Array(WS1.Range("E11"), WS1.Range("E12"), WS1.Range("B11"), _
WS1.Range("B17"), WS1.Range("E25"), WS1.Range("E26"), WS1.Range("E27"))

Range("e12").Value = Range("e12").Value + 1
Range("b16:b23").ClearContents
Range("b11:b13").ClearContents
Range("e25").ClearContents

WS2.Protect Password:="41297"
WS1.Protect Password:="41297"

End Sub

Option Explicit

Public EnableToPrint As Boolean

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If EnableToPrint = False Then
Cancel = True
MsgBox "To print use the macro button"
End If
End Sub

Private Sub Workbook_Open()

End Sub

Option Explicit

Sub MacroToPrint() FACING ERROR HERE
ThisWorkbook.EnableToPrint = True
Sheets("INVOICE").PrintOut FACING ERROR HERE
ThisWorkbook.EnableToPrint = False
End Sub

 

[sadaqatt]

images attached

 

[sadaqatt]

and when I assign "Macrotoprint" it work, but it never save all invoice data to another sheet and also doesn't clear the current cells

 

[sadaqatt]

I am now facing an error with the protection sheet can you help?