Measuring distances with vba

[dpaton05]

Can you use vba to measure distances in excel, such as between any given row at the bottom of the current page?

 

[mikerickson]

What units do you want the result to be in?
By distance between a given row, do you mean distances between two given ranges?
Are these physical distances (like measuring on the computer screen) or distances in a virtual metric (so many rows up, so many columns over)?

To answer your question, yes. Each cell has a .Left and a .Top property that can be used for physical measurements.

 

[dpaton05]

Thanks for the reply Mike. I have a button to insert an image automatically and one to insert a custom image, both images are to appear below the contents of a textbox. The textbox is the lowest thing on the sheet. I basically want to see if the inserted image would fit between a text box and the bottom of the current sheet/page.

 

[dpaton05]

I thought I could find the last row the textbox extended down to and measure the distance between that row and the end of the page. I think I would need it in the same units that the measurements for the image were in. I don't know what those measurements are,

 

[mikerickson]

ActiveWindow.VisibleRange is the range object of the cells currently visible

So, this should give you two numbers to locate your new image.

Dim tbxBottom as Single, lastRowTop as Single

With ActiveSheet.Shapes("TextBox 1")
    tbxBottom = .Top + .Height
End With

With ActiveWindow.VisibleRange
    lastRowTop = .Rows(.Rows.Count).Top
End With

 

[dpaton05]

With ActiveWindow.VisibleRange
lastRowTop = .Rows(.Rows.Count).Top
End With[/CODE]

So this finds the top of the last row of the current page?

 

[mikerickson]

Yes it does. Note that most of the last row might be off the screen.

 

[dpaton05]

I am going round and round in circles with my problem solving on this Mike. Could you help me please with the code for the custom image?

I need to insert it below a textbox but if the image gets split between 2 pages, the image needs to be pushed to the second page.
This is the code I have so far:

Sub cmdCustomSig()
Dim fNameAndPath As Variant
Dim img As Picture, shp As Shape
Set shp = ThisWorkbook.Worksheets("quote_sheet").Shapes("textbox4")

fNameAndPath = Application.GetOpenFilename(Title:="Select Picture To Be Imported")
If fNameAndPath = False Then Exit Sub
    Set img = Worksheets("quote_sheet").Pictures.Insert(fNameAndPath)
    With img
       .Top = shp.Top + shp.Height + "50"
       .Left = "0"
    End With     
End Sub

 

[jolivanes]

Looks like you need to work with the difference of your first horizontal page break and the bottom of your textbox4.

Sub Find_First_Hor_PageBreak()
Dim iRow
iRow = Sheets("Sheet1").HPageBreaks(1).Location.Row
MsgBox iRow
End Sub

 

[dpaton05]

But how would I compare that to the image height to see if it fits?