Measuring distances with vba

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Can you use vba to measure distances in excel, such as between any given row at the bottom of the current page?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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:
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
But how would I compare that to the image height to see if it fits?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top