Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I have a button with a macro that selects a value from a hidden column. If I protect the sheet, the button macro doesn't run.

    Must be something simple (but not obvious to me) to permit the macro to run from the button while the sheet is protected.

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does the macro select one or more cells? If so, try to rewrite it so that it does not select cells.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    What's wrong with this?
    Columns K and L are hidden and unlocked

    B9 is initially locked and I want it to remain locked after the button runs this macro.

    ActiveCell.FormulaR1C1 = "=INDEX(RC[-1]:R[51]C[-1],RANDBETWEEN(1,52))"
    Range("B9").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("L1").Select
    Selection.Copy
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Locked = True
    Selection.FormulaHidden = False

    [quote name='kweaver' post='797264' date='10-Oct-2009 15:15']I have a button with a macro that selects a value from a hidden column. If I protect the sheet, the button macro doesn't run.

    Must be something simple (but not obvious to me) to permit the macro to run from the button while the sheet is protected.

    Thanks in advance.[/quote]

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try replacing this:

    Range("L1").Select
    Selection.Copy
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    with this:

    Range("L1").Copy
    Range("B9").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  5. #5
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    You could do all of this without using Select at all, by replacing

    Code:
    Range("B9").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    		 Range("L1").Select
    		 Selection.Copy
    		 Range("B9").Select
    		 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    			 :=False, Transpose:=False
    		 Application.CutCopyMode = False
    		 Selection.Locked = True
    		 Selection.FormulaHidden = False
    with
    Code:
    	 With Range("B9")
    		 .Locked = False
    		 .FormulaHidden = False
    		  Range("L1").Copy
    		 .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    		SkipBlanks:=False, Transpose:=False
    		 Application.CutCopyMode = False
    		 .Locked = True
    		 .FormulaHidden = False
    	 End With

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I've tried both of previous suggestions but when the sheet is protected, I still have the macro bomb.

    I now have this:

    ActiveCell.FormulaR1C1 = "=INDEX(RC[-1]:R[51]C[-1],RANDBETWEEN(1,52))"
    With Range("B9")
    .Locked = False
    .FormulaHidden = False
    Range("L1").Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    .Locked = True
    .FormulaHidden = False
    End With

    __________________________________________________

    [quote name='StuartR' post='797289' date='10-Oct-2009 18:26']You could do all of this without using Select at all, by replacing

    Code:
    Range("B9").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    		 Range("L1").Select
    		 Selection.Copy
    		 Range("B9").Select
    		 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    			 :=False, Transpose:=False
    		 Application.CutCopyMode = False
    		 Selection.Locked = True
    		 Selection.FormulaHidden = False
    with
    Code:
    	 With Range("B9")
    		 .Locked = False
    		 .FormulaHidden = False
    		  Range("L1").Copy
    		 .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    		SkipBlanks:=False, Transpose:=False
    		 Application.CutCopyMode = False
    		 .Locked = True
    		 .FormulaHidden = False
    	 End With
    [/quote]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't unlock a cell while the sheet is protected. You have two alternatives:

    1. Unprotect the sheet at the beginning of the macro, then run your code, and finally protect the sheet again at the end of the macro.
    You won't have to unlock and lock cell B9.

    - or -

    2. Protect the sheet for the user interface only. This has to be done in VBA:
    - Activate the sheet.
    - Activate the Visual Basic Editor (Alt+F11).
    - Activate the Immediate window (Ctrl+G).
    - Type

    ActiveSheet.Unprotect

    - Press Enter.
    - Type

    ActiveSheet.Protect UserInterfaceOnly:=True

    - Press Enter.
    The sheet is now protected for the end user, but VBA code can freely manipulate it.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Phew. Thanks...finally got it working the way I wanted it to...again, thanks to all.

    [quote name='HansV' post='797314' date='11-Oct-2009 07:13']You can't unlock a cell while the sheet is protected. You have two alternatives:

    1. Unprotect the sheet at the beginning of the macro, then run your code, and finally protect the sheet again at the end of the macro.
    You won't have to unlock and lock cell B9.

    - or -

    2. Protect the sheet for the user interface only. This has to be done in VBA:
    - Activate the sheet.
    - Activate the Visual Basic Editor (Alt+F11).
    - Activate the Immediate window (Ctrl+G).
    - Type

    ActiveSheet.Unprotect

    - Press Enter.
    - Type

    ActiveSheet.Protect UserInterfaceOnly:=True

    - Press Enter.
    The sheet is now protected for the end user, but VBA code can freely manipulate it.[/quote]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •