Select last used Row in Table

[AndyEd]

I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which require the Row to expand to fit.

Due to everything being locked down the user cannot manually adjust the Row height or via the Autofit function. A VBA enabled button allows the user to create new Rows as required.

I've managed to incorporate code to Autofit the Row height, as long as the user stays within the last Row prior to hitting the button. If they tab across fine, however if they hit <Enter> and move down a Row (out of the Table and into the sheet) or click anywhere else, the new Row is adjusted only. All the functionality is then locked out, as is necessary.

Can someone advise how I can ensure a cell in the last Row containing information is selected?

Thanks,

 

[sparky2205]

Can you not just allow formatting of rows when protecting?

 

[AndyEd]

I've tried that but it doesn't work.

 

[jkpieterse]

Can you please show the code that does the autofitting?

 

[AndyEd]

I've simply used

Selection.Rows.Autofit

Once the protection is removed. The protection is then reinstated.

 

[jkpieterse]

Please show the entire sub that uses this, for instance if this is the SelectionChange event then show us that event's code.

 

[sparky2205]

I've simply used

Selection.Rows.Autofit

Once the protection is removed. The protection is then reinstated.

When you reinstate the protection are you allowing formatting of rows?
.Protect Password = "MyPass", AllowFormattingRows:=True

This is why we need to see your code.

 

[AndyEd]

Sorry for the delay, the code is as follows,

 'VBA Add New Row to Table
Sub Add_Row_to_Table()

    ' Unprotect the Worksheet
    ActiveSheet.Unprotect Password:="password"
    
    ' Adjust the current Row to fit entries
    Selection.Rows.AutoFit
        
    'Declare Variables
    Dim oSheetName As Worksheet
    Dim sTableName As String
    Dim loTable As ListObject
    
    'Define Variable
    sTableName = "Table1"
    
    'Define WorkSheet object
    Set oSheetName = Sheets("Audit table")
    
    'Define Table Object
    Set loTable = oSheetName.ListObjects(sTableName)
    
    'Add New row to the table
    loTable.ListRows.Add
    
    ' Protect the Worksheet
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="password"

sparky2205, are you suggesting that even when I allow Row formatting before protecting the sheet by not coding for it as you've suggested that it's turning the formatting 'off'?

 

[AndyEd]

ok I see that by including

AllowFormattingRows:=True

it keeps the function 'live' so that the user can return to the Row and use the Autofit Row Height, however it still means that they have to 1) Select the Row and 2) Select Autofit Row Height.

What I'd like to happen is that regardless of which cell is selected, on pressing the 'New Row' button a cell in the last used Row will be selected, the Autofit function is fired and the cursor then moves to the new Row for data entry.

 

[AndyEd]

Anyone got any suggestions?