Results for "lock formulas"

king

I'm not sure, if its what u need ... but try this

Private Sub Workbook_Open()
    For i = 1 To Sheets.Count
     Sheets(i).Protect Password:="pass"
    Next
End Sub

Thanks Vince1512 for the quick response!

I do not want to enter a password in the box.

For example the password is "A1B2" - my intent is a permanent password for all sheets.

Hi ,

maybe u should try this (need to be adapted)

Private Sub Worksheet_Deactivate()
    Sheets(2).Rows("1:6").EntireRow.Hidden = True 'adapt it
End Sub

Private Sub Worksheet_activate()
    If InputBox("Your password : ", "password") = "pwd" Then 'adapt it
        Sheets(2).Rows("1:6").Select
        Selection.EntireRow.Hidden = False
        [A1].Select
    Else
        Sheets(1).Activate
    End If
End Sub

This code will ask for a password each time you open 2nd sheet and hide 6 first line.

Hello everyone!

I would like to help, I need a VBA code to protect cells with formulas and will then lock the sheet with a password.

So far I've been able to write code that goes through all the sheets in the file and lock the cells with the formulas.

For password protection I could not

Thanks to the tortured.

This is the code I wrote:

Sub Lock()
'
' lock מאקרו
'

'
Dim i As Integer
Dim WS_Count As Integer

Dim x As Long

  With ActiveWorkbook
    'Loop through all sheets in the workbook
    For x = 1 To .Sheets.Count
      'Check if the sheet is visible
      If .Sheets(x).Visible = xlSheetVisible Then
        'Activate/select the sheet and exit the loop
        .Sheets(x).Activate
        Exit For
      End If
    Next x
  End With

WS_Count = ActiveWorkbook.Worksheets.Count

For i = 1 To WS_Count

    ''''הסרת ההגנה הקיימת בגיליונות//Remove the existing protection in the sheets
    ActiveSheet.Unprotect
    '''''

   Cells.Select
    Range("DA1").Activate
    Selection.Locked = False
    Selection.FormulaHidden = False
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("A1").Select

   ActiveSheet.Next.Select

 Next i

End Sub

Hi, I totally agree with Saboh. But if you still want to use VBA, here is some starting points :

Last row based on column A :

LR = Sheets("SheetName").Range("A" & Rows.Count).End(xlUp).Row

Writing formula with VBA :

Sheets("SheetName").Range("A1").Formula = "=SUM(B1:B" & LR & ")"

You can also use generic formulas like =SUM(B:B), but those ones use quite more ressources.

Hello Noura and welcome to the forum,

If I may, you're not following the right approach. There are native Excel tools that allow you to do this in a simple and intuitive way, without using VBA (old, security concerns, subject to bugs…).

Based on your description, it sounds like you're looking for tables. Here's a great tutorial that gives a comprehensive overview of their features: Excel table: comprehensive tutorial with examples.

For your information, in addition to automatically extending your formulas, this will also apply to pivot tables.

Hi everyone,

I'm an investment analyst and new to using VBA. I have a spreadsheet where I add new data every month (one new row each time). I want to use VBA to:

• Find the last row with data
• Automatically extend formulas to include the new row
• Update named ranges or charts so they stay current

What is the best way to do this in VBA?
Any simple examples or advice would be really helpful.

Thank you!

Best regards,

Noura