• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Protect formula in Excel Table but still need the formula to propagate downwards|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Protect formula in Excel Table but still need the formula to propagate downwards|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Protect formula in Excel Table but …
sp_PrintTopic sp_TopicIcon
Protect formula in Excel Table but still need the formula to propagate downwards
Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
1
July 14, 2020 - 7:42 pm
sp_Permalink sp_Print

Hi,

We can protect formula, columns, particular cell in Excel,   however I got a Excel Table scenarios whereby user is able to continue data entry but the last column (formula) need to be lock  (See Attached)

I highlighted the whole worksheet unchecked locked,  select the formulas in table (CTRL 1,  check the locked, goto Review and protect worksheet)

and when I input S/No 3,   the formula no longer propagate down automatically to next row

Is there is way to fulfill this,  at the same time lock the formula cell and the formula still able to bring down to next row during data entry

 

Thank you

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
July 14, 2020 - 11:44 pm
sp_Permalink sp_Print

Needs a bit more work but you could do it with a small Macro

Sub UpdateFormula2()

Dim LastRow As Integer
Dim tbl As ListObject

ActiveSheet.Unprotect Password:="X"

Set tbl = ActiveSheet.ListObjects("Table1")

tbl.Resize tbl.Range.CurrentRegion

ActiveSheet.Protect Password:="X"

End Sub

sp_AnswersTopicAnswer
Answers Post
Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
3
July 15, 2020 - 10:18 pm
sp_Permalink sp_Print

Hi Purfleet,   your VBA is alien to me,  can explain the meaning of 

Set tbl = ActiveSheet.ListObjects("Table1")

tbl.Resize tbl.Range.CurrentRegion

why only the formula row is protect ?

So this is not possible for other excel method ?

 

=========================================================

My version as follows:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheet1.Unprotect "1234"
If Target.Column = 1 Or Target.Column = 3 Or Target.Column = 5 Then
   If VBA.IsEmpty(Target.Value) Then
       Target.Locked = False

  Else

      Target.Locked = True

  End If

Sheet1.Protect "1234"
End If

End Sub

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
July 16, 2020 - 4:46 am
sp_Permalink sp_Print

Set tbl = ActiveSheet.ListObjects("Table1") - is just setting tbl as a short cut for the next line

tbl.Resize tbl.Range.CurrentRegion - this resets the rows in the orginal table to pick up the new rows

The code i pasted in locks the whole page, cells are locked by default once protection is turned on. So i unlocked the input cells and then locked and unlocked the whole page

Not sure what this part of your post means

why only the formula row is protect ?

So this is not possible for other excel method ?

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sherry Fox, Dana Friedt, Nada Perovic
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Forum Stats:
Groups: 3
Forums: 24
Topics: 6218
Posts: 27265

 

Member Stats:
Guest Posters: 49
Members: 31901
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.