Writing a string to a cell in excel

Question

I am trying to write a value to the "A1" cell, but am getting the following error:

Application-defined or object-defined error '1004'

I have tried many solutions on the net, but none are working. I am using excel 2007 and the file extensiton is .xlsm.

My code is as follows:

Sub varchanger()
On Error GoTo Whoa
Dim TxtRng  As Range

Worksheets("Game").Activate
ActiveSheet.Unprotect

Set TxtRng = ActiveWorkbook.Sheets("Game").Cells(1, 1)
TxtRng.Value = "SubTotal"

'Worksheets("Game").Range("A1") = "Asdf"

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.number
    Resume LetsContinue
End Sub

Edit: After I get error if I click the caution icon and then select show calculation steps its working properly

Answer 1

I think you may be getting tripped up on the sheet protection. I streamlined your code a little and am explicitly setting references to the workbook and worksheet objects. In your example, you explicitly refer to the workbook and sheet when you're setting the TxtRng object, but not when you unprotect the sheet.

Try this:

Sub varchanger()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim TxtRng  As Range

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet1")
    'or ws.Unprotect Password:="yourpass"
    ws.Unprotect

    Set TxtRng = ws.Range("A1")
    TxtRng.Value = "SubTotal"
    'http://stackoverflow.com/questions/8253776/worksheet-protection-set-using-ws-protect-but-doesnt-unprotect-using-the-menu
    ' or ws.Protect Password:="yourpass"
    ws.Protect

End Sub

If I run the sub with ws.Unprotect commented out, I get a run-time error 1004. (Assuming I've protected the sheet and have the range locked.) Uncommenting the line allows the code to run fine.

NOTES:

1. I'm re-setting sheet protection after writing to the range. I'm assuming you want to do this if you had the sheet protected in the first place. If you are re-setting protection later after further processing, you'll need to remove that line.
2. I removed the error handler. The Excel error message gives you a lot more detail than Err.number. You can put it back in once you get your code working and display whatever you want. Obviously you can use Err.Description as well.
3. The Cells(1, 1) notation can cause a huge amount of grief. Be careful using it. Range("A1") is a lot easier for humans to parse and tends to prevent forehead-slapping mistakes.