Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning the activecell's page number
How would I get the selected cell's page number, for example if ive selected
cell C345, i may be on page 5 depending on the print area. I would like it that VB would return me 5 and i could assign this to a variable |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning the activecell's page number
X = 0
For Y = 1 to Worksheets.Count If Worksheets(Y).Name = ActiveSheet.Name then X = Y Next Y "gill" wrote: How would I get the selected cell's page number, for example if ive selected cell C345, i may be on page 5 depending on the print area. I would like it that VB would return me 5 and i could assign this to a variable |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning the activecell's page number
I want to know about the page itself in that specific sheet, not the number
of sheets "Brad E." wrote: X = 0 For Y = 1 to Worksheets.Count If Worksheets(Y).Name = ActiveSheet.Name then X = Y Next Y "gill" wrote: How would I get the selected cell's page number, for example if ive selected cell C345, i may be on page 5 depending on the print area. I would like it that VB would return me 5 and i could assign this to a variable |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning the activecell's page number
On Thu, 6 Jul 2006 12:16:02 -0700, gill
wrote: I want to know about the page itself in that specific sheet, not the number of sheets "Brad E." wrote: X = 0 For Y = 1 to Worksheets.Count If Worksheets(Y).Name = ActiveSheet.Name then X = Y Next Y "gill" wrote: How would I get the selected cell's page number, for example if ive selected cell C345, i may be on page 5 depending on the print area. I would like it that VB would return me 5 and i could assign this to a variable So, you would like to count the pagebreaks and add one to the count? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning the activecell's page number
As well as the horizontal and vertical page breaks, it would also depend on
if you are printing across-then-down, or down-then-across. And also .FirstPageNumber NickHK "Bookreader" wrote in message ... On Thu, 6 Jul 2006 12:16:02 -0700, gill wrote: I want to know about the page itself in that specific sheet, not the number of sheets "Brad E." wrote: X = 0 For Y = 1 to Worksheets.Count If Worksheets(Y).Name = ActiveSheet.Name then X = Y Next Y "gill" wrote: How would I get the selected cell's page number, for example if ive selected cell C345, i may be on page 5 depending on the print area. I would like it that VB would return me 5 and i could assign this to a variable So, you would like to count the pagebreaks and add one to the count? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning the activecell's page number
Gill,
This eems to work, but not well tested: Public Function GetMyPage() As Variant Dim CallerCell As Range Dim ThisWS As Worksheet Dim VertBreak As VPageBreak Dim HorizBreak As HPageBreak Dim VertBreakCount As Long Dim HorizBreakCount As Long Dim PageNumber As Long Set CallerCell = Application.Caller Set ThisWS = ThisWorkbook.Worksheets(CallerCell.Parent.Name) With ThisWS For Each VertBreak In .VPageBreaks If VertBreak.Location.Column < CallerCell.Column Then VertBreakCount = VertBreakCount + 1 Else Exit For End If Next For Each HorizBreak In .HPageBreaks If HorizBreak.Location.Row <= CallerCell.Row Then HorizBreakCount = HorizBreakCount + 1 Else Exit For End If Next Select Case .PageSetup.Order Case xlDownThenOver PageNumber = (.HPageBreaks.Count + 1) * VertBreakCount + (HorizBreakCount + 1) Case xlOverThenDown PageNumber = (.VPageBreaks.Count + 1) * HorizBreakCount + (VertBreakCount + 1) End Select If .PageSetup.FirstPageNumber < xlAutomatic Then PageNumber = PageNumber + .PageSetup.FirstPageNumber Else PageNumber = PageNumber ' + 1 End If End With GetMyPage = PageNumber End Function NickHK "gill" wrote in message ... How would I get the selected cell's page number, for example if ive selected cell C345, i may be on page 5 depending on the print area. I would like it that VB would return me 5 and i could assign this to a variable |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning the activecell's page number
Actually,
You don't need the objects set. That was just provide the Intellisense whilst writing. So it can be simplified to: Public Function GetMyPage() As Variant Dim VertBreak As VPageBreak Dim HorizBreak As HPageBreak Dim VertBreakCount As Long Dim HorizBreakCount As Long Dim PageNumber As Long With ThisWorkbook.Worksheets(Application.Caller.Parent. Name) 'VertBreakCount = 1 For Each VertBreak In .VPageBreaks If VertBreak.Location.Column < Application.Caller.Column Then VertBreakCount = VertBreakCount + 1 Else Exit For End If Next 'HorizBreakCount = 1 For Each HorizBreak In .HPageBreaks If HorizBreak.Location.Row <= Application.Caller.Row Then ....etc It seem some what slow, but I suppose Excel needs to process a kind of preview each time to assess the pagebreaks. Note that if you change the PageSetup and/or column widths/row heights, so function will NOT automatically update, so the results will get out of sync. NickHK "NickHK" wrote in message ... Gill, This eems to work, but not well tested: Public Function GetMyPage() As Variant Dim CallerCell As Range Dim ThisWS As Worksheet Dim VertBreak As VPageBreak Dim HorizBreak As HPageBreak Dim VertBreakCount As Long Dim HorizBreakCount As Long Dim PageNumber As Long Set CallerCell = Application.Caller Set ThisWS = ThisWorkbook.Worksheets(CallerCell.Parent.Name) With ThisWS For Each VertBreak In .VPageBreaks If VertBreak.Location.Column < CallerCell.Column Then VertBreakCount = VertBreakCount + 1 Else Exit For End If Next For Each HorizBreak In .HPageBreaks If HorizBreak.Location.Row <= CallerCell.Row Then HorizBreakCount = HorizBreakCount + 1 Else Exit For End If Next Select Case .PageSetup.Order Case xlDownThenOver PageNumber = (.HPageBreaks.Count + 1) * VertBreakCount + (HorizBreakCount + 1) Case xlOverThenDown PageNumber = (.VPageBreaks.Count + 1) * HorizBreakCount + (VertBreakCount + 1) End Select If .PageSetup.FirstPageNumber < xlAutomatic Then PageNumber = PageNumber + .PageSetup.FirstPageNumber Else PageNumber = PageNumber ' + 1 End If End With GetMyPage = PageNumber End Function NickHK "gill" wrote in message ... How would I get the selected cell's page number, for example if ive selected cell C345, i may be on page 5 depending on the print area. I would like it that VB would return me 5 and i could assign this to a variable |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning page-count to a cell | Excel Worksheet Functions | |||
Return activecell's column header in Alphabet | Excel Programming | |||
How to get ActiveCell's address? | Excel Programming | |||
How do I use the ActiveCell's contents as a string for a web query | Excel Programming | |||
Returning actual page field changed in pivottable | Excel Programming |