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?

 

[jolivanes]

Add this to the code of Post #9 to see the HPageBreak.

MsgBox Rows(iRow).Top

The difference between the bottom of your textbox4, which you have figured out, and the pagebreak should not exceed the height of your picture.
So If the height of the picture > than this difference, it'll need to go below the page break = top of iRow. Otherwise it can go below the bottom row of your textbox4.

 

[dpaton05]

I get a subscript out of range error with the following line highlighted

iRow = Sheets("sheet1").HPageBreaks(1).Location.Row

Even though you have told me what to do, I am not very good with the syntax of how it is to be written. Could you help me with it please?

 

[jolivanes]

Needs cleaning up but you should be able to do that I assume.
Change references, like Sheet Names etc, if and where required

Sub AAAAD()
Dim img As Object
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Submit"
        .Title = "Select an image file"
        .Filters.Clear
        .Filters.Add "JPG", "*.JPG"
        .Filters.Add "JPEG File Interchange Format", "*.JPEG"
        .Filters.Add "Graphics Interchange Format", "*.GIF"
        .Filters.Add "Portable Network Graphics", "*.PNG"
        .Filters.Add "Tag Image File Format", "*.TIFF"
        .Filters.Add "All Pictures", "*.*"
        If .Show = -1 Then
        Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))
        End If
    End With
With img
.Left = 0
If img.Height > Rows(Sheets("Sheet2").HPageBreaks(1).Location.Row).Top - Sheets("Sheet2").Shapes("TextBox 1").Top + Sheets("Sheet2").Shapes("TextBox 1").Height Then
.Top = Rows(Sheets("Sheet2").HPageBreaks(1).Location.Row).Top
Else
.Top = Sheets("Sheet2").Shapes("TextBox 1").Top + Sheets("Sheet2").Shapes("TextBox 1").Height
End If
End With
End Sub

For Post #12 . Do you have a "sheet1"?

 

[dpaton05]

For Post #12 . Do you have a "sheet1"?

Is called something else, I just called it sheet1 to desensitize it.

 

[dpaton05]

Although, the sheet that has the images is called sheet2.

 

[dpaton05]

Needs cleaning up but you should be able to do that I assume.
Change references, like Sheet Names etc, if and where required

Sub AAAAD()
Dim img As Object
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Submit"
        .Title = "Select an image file"
        .Filters.Clear
        .Filters.Add "JPG", "*.JPG"
        .Filters.Add "JPEG File Interchange Format", "*.JPEG"
        .Filters.Add "Graphics Interchange Format", "*.GIF"
        .Filters.Add "Portable Network Graphics", "*.PNG"
        .Filters.Add "Tag Image File Format", "*.TIFF"
        .Filters.Add "All Pictures", "*.*"
        If .Show = -1 Then
        Set img = ActiveSheet.Pictures.Insert(.SelectedItems(1))
        End If
    End With
With img
.Left = 0
If img.Height > Rows(Sheets("Sheet2").HPageBreaks(1).Location.Row).Top - Sheets("Sheet2").Shapes("TextBox 1").Top + Sheets("Sheet2").Shapes("TextBox 1").Height Then
.Top = Rows(Sheets("Sheet2").HPageBreaks(1).Location.Row).Top
Else
.Top = Sheets("Sheet2").Shapes("TextBox 1").Top + Sheets("Sheet2").Shapes("TextBox 1").Height
End If
End With
End Sub

Thanks for this code, I will try it and let you know.

 

[jolivanes]

Please don't quote unless there is a need for it. I find it clutter.
You can always refer to Post numbers.

Make sure to change references like Sheet names, TextBox names and whatever pertains to your Workbook/Worksheet.

 

[dpaton05]

I changed the references (sheet2 and textbox 1) and it still says subscript out of range. It inserts the image at a fixed point, regardless of where the text box is. Are all the sheet2 references meant to be changed to the name of the sheet I am putting the image in?

 

[dpaton05]

This line is highlighted

If img.Height > Rows(Worksheets("sheet2").HPageBreaks(1).Location.Row).Top - Sheets("sheet2").Shapes("TextBox4").Top + Sheets("sheet2").Shapes("TextBox4").Height Then

 

[mikerickson]

Are you talking about the size of a printed page or what you see on the screen?