Forum Discussion

José Neves's avatar
José Neves
Copper Contributor
Sep 29, 2017

inserting new lines

Team, I´m creating a table where i have a line with cells that users can fill and other to display the results (in grey with formulas). The idea is to give that table with a single line and, whenev...
excel
José Neves's avatar
José Neves
Copper Contributor
Sep 30, 2017

Sergei,

Once again thank you very very much.

One more help please.

I haven't yet understood what is the "mechanism" to trigger the creation of the new line. On the new sample I'm sending you the cells "Event" should be the one trigering the new line. However, the content/value of that cell (text) is not used for any formula.

Again the grey cells will be locked for edit from end users.

 

Thanks

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 30, 2017

Jose, i missed the protection. Yes, to unprotect for new column you need the macro. But in any case better to use Tables.

 

I'll update your sample in a while.

  • José Neves's avatar
    José Neves
    Copper Contributor
    Sep 30, 2017
    Thanks
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 30, 2017

      Jose, I took this https://www.extendoffice.com/documents/excel/4382-excel-lock-sheet-but-keep-tables-expandable.html as the sample, here is quite good explanation how to do.

       

      For your concrete table the macro is

      Sub Button1_Click()
Dim pswStr As String
    pswStr = "123"

On Error Resume Next
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pswStr
Range("TimeTable[[#Headers],[Extended hours (08:00-20:00)]]").Select
Selection.End(xlDown).Select
Selection.Offset(1, -8).Select
ActiveCell.FormulaR1C1 = "xpto"
ActiveSheet.Protect DrawingObjects:=False, _
    Contents:=True, Scenarios:=False, _
    AllowFormattingCells:=True, AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, AllowInsertingColumns:=True, _
    AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
    AllowDeletingColumns:=True, AllowDeletingRows:=True, _
    AllowSorting:=True, AllowFiltering:=True, _
    AllowUsingPivotTables:=True
Selection.ClearContents
Application.ScreenUpdating = True

End Sub

      In addition on formula cells i hided formulas for the protection sheet. (check Hidden in Protection tab of cells formatting).

       

      If you unprotect the sheet and change table unlock cells without formula before protection only within the table.

      Not sure how to do without password, looks more complex, at least for me.

       

      Sample file is in archive, macro enabled files are declined here.

       

      You may add your vote for adding above functionality without macro workaround https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16452913-get-tables-working-on-protected-sheets-add-rows

       

      Above is one of patterns, you may find more, idea is similar. 

       

       

Resources