none
how do put the code for working all worksheet in workbook RRS feed

  • Question

  • Select all cells (press Ctrl+A, if that selects the current range only, press Ctrl+A again).
    
    Press Ctrl+1 to activate the Format Cells dialog, then activate the Protection tab. Clear the Locked check box, then click OK.
    
    Activate the Review tab of the ribbon. Click Protect Sheet. Tick or clear check boxes to determine what users are allowed to do, and ifd desired specify a password. Click OK.
    
    Right-click the sheet tab, and select View Code from the context menu. Enter the following code in the worksheet module:
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        ActiveSheet.Unprotect ' Password:="secret"
        For Each cel In Target
            If cel.Value <> "" Then
                cel.Locked = True
            End If
        Next cel
        ActiveSheet.Protect ' Password:="secret"
    End Sub
    
    Save the workbook as a .xlsm, .xlsb or .xls workbook, not as a .xlsx workbook (those don't support macros).
    Regards, Hans Vogelaar



    Friday, October 10, 2014 6:51 AM

All replies

  • from this  solution

    lock Excel cell after value is entered

    https://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?referrer=http://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?referrer=http://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?referrer=http://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?referrer=http://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?referrer=http://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?referrer=http://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?referrer=http://social.msdn.microsoft.com/Forums/en-US/c73b6532-973d-414e-97cd-baa298edc629/lock-excel-cell-after-value-is-entered?forum=isvvba



    Friday, October 10, 2014 6:52 AM
  • If your current code is working only for one sheet it means that this one particular Worksheet Object has the code in it's "code behind" file. 

    If you want all worksheets to be able to execute the same exact code the easiest ( not neccessarily the best ) solution would be to copy paste the code into the "code behind" file. 

    You can do that by right clicking each sheet's tab and selecting View Code and then copy - pasting the code.


    Friday, October 10, 2014 9:41 AM
  • Create a new module, make your code public instead of private and you will be able to use it in your entire excel
    Friday, October 10, 2014 1:40 PM
  • now i try this code it work

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Dim cel As Range
        ActiveSheet.Unprotect ' Password:="555"
        For Each cel In Target
            If cel.Value <> "" Then
                cel.Locked = True
            End If
        Next cel
        ActiveSheet.Protect ' Password:="555"
    End Sub

     

    Monday, October 13, 2014 8:27 AM