How to lock cells in and let Macros ovverwrite anyway

**kbdb** · 08-24-2017, 10:33 AM

I want to lock certain cells so users of the woorkbook wont be able to make changes but at the same time I want macros to be able to make changes.

For instance I want to lock range "A1" so no user can type things in A1. But the following macro should be able to write and overwrtite in cell A1

Sub Macro1()
a = Date
Range("A1") = a
End Sub

**davesexcel** · 08-24-2017, 10:42 AM

You need to unprotect the sheet first

Sub Button1_Click()
    Dim sh As Worksheet
    Set sh = Sheets("Sheet1")
    sh.Unprotect
    Range("A1") = Date
    sh.Protect
End Sub

Lots of example to unprotect the sheet with a password as well

activesheet.unprotect "password" 
sh.unprotect "password"

How to lock cells in and let Macros ovverwrite anyway

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to lock cells in and let Macros ovverwrite anyway

Re: How to lock cells in and let Macros ovverwrite anyway

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Lock rows unless they are hidden or unhidden by macros?

Are there any macros to lock a colum after one pane had been freezed?

Lock a row when not blank but also lock cells marked as locked

Can I lock cells or do I have to lock the worksheet?

[SOLVED] How can I lock worksheets while macros can still enter into them?

[SOLVED] How can I lock worksheets while still allowing macros to operate?

[SOLVED] How can I lock worksheets while still allowing macros to operate?

Bookmarks

Bookmarks

Posting Permissions