Print area and text box problem

[Paulo H]

Hi I have a spreadsheet with a table that works fine but I have added to the end of it a text box containing terms and conditions. the issue I have is that when the table expands it mof coursemoves the text box down a line at a time which is fine. What I want to acheive is to 1. Set th print area via vba to the row after the where the textbox finishes and two prevent the text boxt being split over two pages. If the text box was always the last page that would be great. Many thanks

 

[Paulo H]

Hi
Still not quite right

It does move down as suggested but again if row 33 at correct row height it moves to 34 to small but the moves down ok

So problem is row 33 remains too big and the new row is too small

I will have a play with page setting etc to see if it is anything to do with page breaks etc

Thanks

 

[Yongle]

I am convinced that you are doing something different to me
- we need to work out what that is

Did you set the print range ?
- I set the print range to include everything that should be printed (including the row containing the textbox


The pictures below demonstrate that the method works:

THE WORKSHEET:
- see the broken line which shows the page break appearing
- textbox was in "tall" row 33
- insert row 30
- row 33 becomes row 34
- the textbox is now in "tall" row 34
BEFORE

AFTER

PRINT PREVIEW:

 

[Paulo H]

Hi

I tried your method but still have the same issue.It appears to work for one row then the previous issues Still occur.

so I tried it on a fresh sheet and things work as you explained

however I may have spotted the problem

All works fine if you “insert“ a row but my table is being populated via a Userform, which means the table expands/adds a row when data is added . You would assume that this would be the same as inserting a row but it seems not to like it.

Thanks for your help

 

[Yongle]

It had to be something that was different between what was in your workbook and mine - well spotted

I have been able to replicate the issue now
The problem only happens when a new row is added at the BOTTOM of the table - it does not happen when a row is inserted anywhere else in the table
The reason the problem occurs is that the new row added to the table simply occupies the next available row in the worksheet - a row is NOT inserted

Solution = amend the code VBA inserting the required row in the worksheet BEFORE the row is added to the table

You need to use something like this - amend sheet reference and textbox name

Sheets("Name of Sheet").Shapes("Textbox 2").TopLeftCell.EntireRow.Insert

If this does not solve the issue, then please post the code which adds the row to the table

 

[Paulo H]

Thanks for this I will have a go later

 

[Paulo H]

Sorry meant to ask this line of code should it be inserted in the module that transfers the data from the userform to the table? Yes this could be the problem as at the moment it will only add Data to the bottom row of the table and I have been trying to workout how to add a row/insert a row in mid table! I can insert a blank/new row no problem Mid table but data that transfers to the table from the userform always is added to the bottom!

 

[Paulo H]

It had to be something that was different between what was in your workbook and mine - well spotted

I have been able to replicate the issue now
The problem only happens when a new row is added at the BOTTOM of the table - it does not happen when a row is inserted anywhere else in the table
The reason the problem occurs is that the new row added to the table simply occupies the next available row in the worksheet - a row is NOT inserted

Solution = amend the code VBA inserting the required row in the worksheet BEFORE the row is added to the table

You need to use something like this - amend sheet reference and textbox name

Sheets("Name of Sheet").Shapes("Textbox 2").TopLeftCell.EntireRow.Insert

If this does not solve the issue, then please post the code which adds the row to the table

 

[Paulo H]

Hi still no success so here is the code that adds data from the listbox to the table1. It adds the data to the last row of the table etc. What would be great is if when I insert a blank row mid table I could get the new data to transfer from the list box to that. I believe this would also resolve the above issue too

cheers


Private Sub Transfer_Click()
ActiveSheet.Unprotect Password:="123"
Dim wrksht As Worksheet
Dim objListObj As ListObject


Dim i As Long
Dim listObj As ListObject
Set listObj = Sheets("Quote Form").ListObjects("Table1")
listObj.ListRows.Add , 1

listObj.DataBodyRange(listObj.ListRows.Count, 1) = ListBox1.List(ListBox1.ListIndex)


For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True And Me.ListBox1.List(i, 1) <> "" Then
'Sheet1.Range("A12345").End(xlUp).Offset(1, 0) = Me.ListBox1.List(i, 0)
Sheet1.Range("A12345").End(xlUp).Offset(0, 3) = Me.ListBox1.List(i, 1)
Sheet1.Range("A12345").End(xlUp).Offset(0, 4) = Me.ListBox1.List(i, 3)
Sheet1.Range("A12345").End(xlUp).Offset(0, 2) = Me.ListBox1.List(i, 2)
Sheet1.Range("A12345").End(xlUp).Offset(0, 6) = Me.ListBox1.List(i, 4)
For x = 1 To 4
'Sheet1.Range("A123").End(xlUp).Offset(0, 1) = Me.ListBox1.List(i, x)
'Sheet1.Range("Table1").End(xlUp).Offset(3, 3) = Me.ListBox1.List(i, 3)

Next x

End If
Next i

End Sub

 

[Yongle]

LINE 1
The line below (provided in post#14) requires the correct sheet and textbox names

Sheets("Name of Sheet").Shapes("Textbox 2").TopLeftCell.EntireRow.Insert

LINE 2
This line in your code adds the row at the bottom of your table

listObj.ListRows.Add , 1

Try inserting LINE 1 above LINE 2 in your code

 

[Paulo H]

LINE 1
The line below (provided in post#14) requires the correct sheet and textbox names

Sheets("Name of Sheet").Shapes("Textbox 2").TopLeftCell.EntireRow.Insert

LINE 2
This line in your code adds the row at the bottom of your table

listObj.ListRows.Add , 1

Try inserting LINE 1 above LINE 2 in your code

It doen't seem to like this

I have change the sheets name to quote form but not sure where I find the name of the textbox as it was just added from excel.