Disable a cell in Excel using VBA

user_9811274 0
Viewed 9989

I am trying to do few calculations and update the result of the same in a cell upon clicking the Calculate button (Active X button).

When the Excel sheet is opened, I want few of the cells to be disabled and greyed out. To implement the same, I have written the below lines of code under the Workbook_Open() Sub. All the conditions written under the sub are working fine, except, the statement that I have written to disable the cell (written the locked function to disable the cell).

After some surfing, I came to understand that sheet needs to be protected if a cell needs to be disabled/locked. So, I added an extra line in the code to protect the sheet. However, this stopped the result to be updated in a cell.

Upon clicking the calculate button, excel states that the "Sheet is protected". So, is there anyway to enable/ disable the cell using VB Script and without protecting the sheet please?

Private Sub Workbook_Open()

    Dim b1 As Variant
    Set b1 = Sheets("Calculation Tool").CommandButton22
    b1.Enabled = False

    Range("B4:C4") = ""
    Range("E4:O4") = ""
    Range("E9:F9") = ""

    'This is the code written to disable the cells N4 and O4
    Sheets("Calculation Tool").Range("N4:O4").Locked = True
    Sheets("Calculation Tool").Protect

End Sub
vba excel
2 Answers

You cannot "disable" a worksheet cell per-se; it's not like a textbox control.

You can, however:

  1. set the cell's fill color to gray (Ctrl+1 to enter Format Cells > Fill tab)
  2. lock the cell to prevent changes (Ctrl+1 to enter Format Cells > Protection tab)
  3. protect the worksheet (Review tab > Protect Sheet) so the cell locking takes effect.

If you need to change the locked cell you can programmatically unprotect the worksheet, make the change, and then re-protect it. Also note that there are several options available when protecting the worksheet.

Alternatively, you could instead use text boxes, and then disable/lock it like you would other controls.

See Also: Lock or unlock specific areas of a protected worksheet

(Based on your "greyed-out" description, I believe you were thinking of textboxes on Microsoft Access Forms.)

user_81127760

try this code for a range:

Range("A1:B2").Locked = True

user_14046420
Related