Measuring distances with vba

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,389
Can you use vba to measure distances in excel, such as between any given row at the bottom of the current page?
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,550
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.
 
Last edited:

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,389
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

Well-known Member
Joined
Aug 14, 2018
Messages
1,389
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,550
ActiveWindow.VisibleRange is the range object of the cells currently visible

So, this should give you two numbers to locate your new image.
Code:
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,550
Yes it does. Note that most of the last row might be off the screen.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,389
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:
Code:
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,520
Office Version
2013, 2007
Platform
Windows
Looks like you need to work with the difference of your first horizontal page break and the bottom of your textbox4.

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

Watch MrExcel Video

Forum statistics

Threads
1,098,998
Messages
5,465,900
Members
406,453
Latest member
CeReaLK

This Week's Hot Topics

Top