• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

printing counter

N

nader assaleh

Member
Hi.
is it possible to have printing counter as the following
the first time i print worksheet it starts with 01 then if i change the value of any particular cells say A1,C2,D7 the counter increase to number 02 if the value of any other cell (other than A1,C2,D7) changes the counter remains 01. and same logic applies for next printing numbers

many thanks
 
Hi, nader assaleh!

Give a look at the uploaded file. It has VBA code split in 3 parts:

1) Workbook class module.
a) Sets variable that holds changes to false
b) Sets print page number accordingly
Code: 
Option Explicit

Private Sub Workbook_Open()
    ' constants
    ' declarations
    ' start
    ' process
    [Hoja1].gpbChanged = False
    ' end
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ' constants
    ' declarations
    ' start
    ' process
    With ActiveSheet
        If .Name = [Hoja1].Name And [Hoja1].gpbChanged Then
            With .PageSetup
                If .FirstPageNumber = xlAutomatic Then
                    .FirstPageNumber = 1
                Else
                    .FirstPageNumber = .FirstPageNumber + 1
                End If
            End With
            [Hoja1].gpbChanged = False
        End If
    End With
    ' end
End Sub

2) Worksheet Hoja1 class module.
a) Holds the variable to reflect changes
b) Changes its status when yellow areas (defined in constant ksRng) change
Code: 
Option Explicit

' global declarations
Public gpbChanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    ' constants
    Const ksRng = "A1 C2 D7"
    ' declarations
    Dim rng As Range, arr As Variant
    Dim I As Integer
    ' start
    If Target.Cells.Count > 1 Then Exit Sub
    Set rng = Nothing
    arr = Split(ksRng)
    For I = LBound(arr) To UBound(arr)
        If I = LBound(arr) Then
            Set rng = Range(arr(I))
        Else
            Set rng = Application.Union(rng, Range(arr(I)))
        End If
    Next I
    If Application.Intersect(Target, rng) Is Nothing Then Exit Sub
    ' process
    gpbChanged = True
    ' end
    Set rng = Nothing
End Sub

3) Standard module
Macro to reset the page number to its default.
Code: 
Option Explicit
Sub ResetPageCount()
  ' constants
  ' declarations
  ' start
  ' process
  [Hoja1].PageSetup.FirstPageNumber = xlAutomatic
  [Hoja1].gpbChanged = False
  ' end
End Sub

Just advise if any issue.

Regards!

EDITED (SirJB7)

PS: 3rd part of the code pasted correctly as it was the same as 2nd. Thanks to @NARAYANK991 for the catch.
 

Attachments

  • printing counter (for nader assaleh at chandoo.org).xlsm
    20.8 KB · Views: 4
Last edited:
Hi , sirJB7
good day
thank you much, maybe i could not express myself well. i did not mran the page counter rather the counter will be loacated in a certain cell B16 and its value is based on other cells (b3,b4,b5).
sorry for inconveneince
 

Attachments

  • book2_Interpolate(1).xlsx
    27.4 KB · Views: 3
Hi, nader assaleh!

Check the behavior of 2nd worksheet in the fixed and uploaded file. Added a named range for cell B16, PrintPageCell.

VBA code modifications:

1) Workbook class module (change)
Code: 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
    ' constants
    ' declarations
    ' start
    ' process
    With ActiveSheet
        If .Name = [Hoja1].Name And [Hoja1].gpbChanged Then
            With .PageSetup
                If .FirstPageNumber = xlAutomatic Then
                    .FirstPageNumber = 1
                Else
                    .FirstPageNumber = .FirstPageNumber + 1
                End If
            End With
            [Hoja1].gpbChanged = False
        ElseIf .Name = [Hoja2].Name And [Hoja2].gpbChanged2 Then
            [Hoja2].Range(gpksPageNumber).Value = [Hoja2].Range(gpksPageNumber).Value + 1
            .PageSetup.FirstPageNumber = [Hoja2].Range(gpksPageNumber).Value
            [Hoja2].gpbChanged2 = False
        End If
    End With
    ' end
End Sub

2) Worksheet Hoja2 class module (new)
Code: 
Option Explicit

' global declarations
Public gpbChanged2 As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    ' constants
    Const ksRng = "B3 B4 B5"
    ' declarations
    Dim rng As Range, arr As Variant
    Dim I As Integer
    ' start
    If Target.Cells.Count > 1 Then Exit Sub
    Set rng = Nothing
    arr = Split(ksRng)
    For I = LBound(arr) To UBound(arr)
        If I = LBound(arr) Then
            Set rng = Range(arr(I))
        Else
            Set rng = Application.Union(rng, Range(arr(I)))
        End If
    Next I
    If Application.Intersect(Target, rng) Is Nothing Then Exit Sub
    ' process
    gpbChanged2 = True
    ' end
    Set rng = Nothing
End Sub

3) Standard module (change)
Code: 
Option Explicit
' global constants
Public Const gpksPageNumber = "PageNumberCell"
Sub ResetPageCount()
    ' constants
    ' declarations
    ' start
    ' process
    [Hoja1].PageSetup.FirstPageNumber = xlAutomatic
    [Hoja1].gpbChanged = False
    [Hoja2].Range(gpksPageNumber).Value = 1
    [Hoja2].gpbChanged2 = False
    ' end
End Sub

Regards!
 

Attachments

  • printing counter (for nader assaleh at chandoo.org).xlsm
    23.5 KB · Views: 3
Hi, nader assaleh!
Did you actually printed the worksheet or just visualized it? Must print it...
With Ctrl-P the page number doesn't change.
Regards!
 
Dear SirJB7
i did print each worksheet individually and then i also printed the workbook but the counter still reads two (though i changed the specified cells). may be i am missing something?
 
Hi, nader assaleh!

Could you make it work with my uploaded file? If so then you're missing something while adapting the model to your actual file, the easiest way would be to upload it and get it back fixed, the hardest try to discover the bug. Otherwise I'm a bit confused, it works on my side.

Regards!
 
Hi, SirJb7
i completely shut down the comptuer and then ran the file it is working. im trying to implement it in my project but i could not figure out what is the need for worksheet Hoja1.
thank you
 
Hi, nader assaleh!

The first interpretation of your requirements was done on worksheet Hoja1. The last version after your specs change was done on worksheet Hoja2, just to keep the two alternatives.

To adapt the last uploaded file to your actual file, you can get rid of everything related to WS Hoja1. Briefly you should:
- define named range PageNumberCell as in WS Hoja2
- copy the workbook class module (removing lines referencing to WS Hoja1)
- copy the worksheet Hoja2 class module (renaming the object reference [Hoja2] to your actual WS name)
- copy the standard module (removing lines referencing to WS Hoja1)

If I don't miss anything that's all.

Regards!
 
dear SirJB7
i did try for long time to implement what you instructed me to do but every time i get the message

run-time error 424
Object required

and when i debug it
it goes to the line

[editable].Range(gpksPageNumber).Value = 1

any suggestions how to overcome it.

many thanks
 
HI..
IF YOU DON'T MIND CAN YOU MODIFY THE MACRO AS THE FOLLOWING

print counter cell is E11 in worksheet HOJA1

variable cells are worksheet HOJA2
b3:b13
f3,ff13
e8
g7
 

Attachments

  • printing counter12.xlsm
    19.3 KB · Views: 4
Hi, nader assaleh!

Give a look at the uploaded file. The problem resides that in the code of reset page count (standard module) and before print (workbook class module) the reference to the range holding the printing counter still said:
Code: 
[Hoja2].Range(gpksPageNumber)
instead of saying:
Code: 
[Hoja1].Range(gpksPageNumber)
as you moved that range to worksheet Hoja1 instead of the original placement at worksheet Hoja2.

Please test it and tell us if it now works.

Regards!
 

Attachments

  • printing counter - printing counter12 (for nader assaleh at chandoo.org).xlsm
    16.2 KB · Views: 10
dear SirJB7
since my vba knowledge is very limited i could not implement it in my real project. every time i get an error message. would you please help me to get things fixed.

PageNumberCell is located in e-ls sheet cell AL6
printing counter is based on cell located in editable worksheet cells C7 c8 e8 c9 e9 c10 c11

many thanks
 

Attachments

  • LS.xlsm
    219.5 KB · Views: 2
@nader assaleh
Hi!
Sorry for the late answer but been traveling the last couple of weeks. Let me shake the sand from my feet, get my hands on these things called keyboards and I'll get back to you soon.
Regards!
 
You must log in or register to reply here.
Back
Top