Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pagenumber of active cell (vba)
Hi,
how can i get the pagenumber of the selected cell? thanks for any help Ralf merry christmas from germany! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pagenumber of active cell (vba)
Here are some approaches posted in the past:
http://groups.google.com/groups?selm...%40cppssbbsa03 From: Dana DeLouis ) Subject: # of pages and page# View: Complete Thread (4 articles) Original Format Newsgroups: microsoft.public.excel.programming Date: 1999/07/01 Here is something I wrote a long time ago. Not tested for Excel 2k. Maybe this will give you some ideas. I have numbered the pages left to right, then down. Adjust it you want it different. Good luck. Dana Sub Print_Page_of_ActiveCell() '// Prints the page of the active cell only '// Dana DeLouis: '// Not tested completely for Excel 2k Dim pb As Variant Dim Nr As Long, Nc As Integer Dim MaxColumns As Long Dim MaxRows As Long Dim CurrentPage As Long Dim TotalPages As Long If Selection.Cells.Count 1 Then MsgBox "Select 1 Cell Only" Exit Sub End If '// Save Settings ActiveWorkbook.CustomViews.Add _ ViewName:="_temp", _ PrintSettings:=True, _ RowColSettings:=True With ActiveSheet.PageSetup .PrintArea = False .Order = xlOverThenDown End With TotalPages = ExecuteExcel4Macro("Get.Document(50)") MaxColumns = ActiveSheet.VPageBreaks.Count + 1 MaxRows = ActiveSheet.HPageBreaks.Count + 1 ' or MaxRows = TotalPages / MaxColumns For Each pb In ActiveSheet.HPageBreaks If pb.Location.Row <= ActiveCell.Row Then Nr = Nr + 1 Else Exit For End If Next For Each pb In ActiveSheet.VPageBreaks If pb.Location.Column <= ActiveCell.Column Then Nc = Nc + 1 Else Exit For End If Next Nc = Nc + 1 CurrentPage = Nr * MaxColumns + Nc '// Reset what I had With ActiveWorkbook.CustomViews("_temp") .Show .Delete End With MsgBox "Page : " & CurrentPage & " out of " & TotalPages '// Print the selected page 'ActiveSheet.PrintOut From:=CurrentPage, To:=CurrentPage End Sub ----------------------- In the thread below, Dana cautions that setting a print area could affect the results of the code, so he suggested removing the print area and restoring it. This post is in the thread below. --------------------------- http://groups.google.com/groups?thre...%40tkmsftngp02 [ Posted by Ron de Bruin] Laurent Longre posted the following code on April 21 2000, which displays a message box with the page number. To print the page, line added at the end of the macro: Sub PrintCurrentPage() Dim VPC As Integer, HPC As Integer Dim VPB As VPageBreak, HPB As HPageBreak Dim NumPage As Integer If ActiveSheet.PageSetup.Order = xlDownThenOver Then HPC = ActiveSheet.HPageBreaks.Count + 1 VPC = 1 Else VPC = ActiveSheet.VPageBreaks.Count + 1 HPC = 1 End If NumPage = 1 For Each VPB In ActiveSheet.VPageBreaks If VPB.Location.Column ActiveCell.Column Then Exit For NumPage = NumPage + HPC Next VPB For Each HPB In ActiveSheet.HPageBreaks If HPB.Location.Row ActiveCell.Row Then Exit For NumPage = NumPage + VPC Next HPB 'MsgBox "Page number of the active cell = " & NumPage ActiveWindow.SelectedSheets.PrintOut _ From:=NumPage, To:=NumPage, _ Copies:=1, Collate:=True End Sub -- Regards, Tom Ogilvy "Ralf Hörbelt" wrote in message ... Hi, how can i get the pagenumber of the selected cell? thanks for any help Ralf merry christmas from germany! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pagenumber of active cell (vba)
Ralf,
This would work if you don't have vertical page breaks.. Private Sub CommandButton1_Click() Dim obj As Object Dim intRow As Integer Dim i As Integer intRow = ActiveCell.Row i = 1 For Each obj In ActiveSheet.HPageBreaks If obj.Location.Row intRow Then MsgBox i & " is the current page number." Exit Sub End If i = i + 1 Next End Sub Hope that helps. -Brad -----Original Message----- Hi, how can i get the pagenumber of the selected cell? thanks for any help Ralf merry christmas from germany! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row select mode to highlight active row of active cell | Excel Discussion (Misc queries) | |||
set the background color of the current cell(active cell) | New Users to Excel | |||
referring to formula in a non active cell from active cell | Excel Discussion (Misc queries) | |||
HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) | |||
pagenumber | Excel Programming |