Getting a 1004 error on an unprotected sheet

[melodramatic]

I'm running a macro to simply put data into a table quickly, and am getting a Run-time error '1004': stating I've got a protected sheet. No, I dont'!

While the sheet is protected, I clearly run the code to unprotect it before the input, then re-protect it after. And that code is working. When the macro bombs ou

My coding from the beginning is as follows:

Private Sub CommandButton1_Click()
'This macro adds to the bank
Dim pwd As String
Dim nextrow As Long
Dim playername As String
Dim money As Long

pwd = Range("K2")
nextrow = Range("L2")
playername = Range("B2")
money = Range("B3")

Worksheets("Activity").Visible = True
Worksheets("Activity").Unprotect Password:=pwd
Worksheets("Activity").Select
MsgBox nextrow
Range("A" & nextrow).Value = playername
Range("B" & nextrow).Value = Date
Range("C" & nextrow).Value = money
Range("D" & nextrow).Value = "Add"
Range("F" & nextrow).Value = Environ("username")
ActiveSheet.Protect Password:=pwd
Worksheets("Balances").Select
Worksheets("Activity").Visible = False

I first tried running the unprotect code as the following (but that didn't work, either):

Worksheets("Activity").Visible = True
Worksheets("Activity").Select
ActiveSheet.Unprotect Password:=Pwd

 

[Fluff]

Where is that code located?

 

[melodramatic]

Which code? I'm confused.

 

[Fluff]

The code you posted in your op.

 

[melodramatic]

OK, the larger strand of code is within a command button on the master page (Input).

The smaller strand of code is no longer there - it's showing what I had originally used (unprotecting the ActiveSheet as opposed to Worksheets("Activity").

 

[Fluff]

Ok the problem is that when code is in a sheet module, unqualified ranges will refer to the sheet that contains the code, not the active sheet.
Try it like

Private Sub CommandButton1_Click()
'This macro adds to the bank
Dim pwd As String
Dim nextrow As Long
Dim playername As String
Dim money As Long

pwd = Range("K2")
nextrow = Range("L2")
playername = Range("B2")
money = Range("B3")

With Worksheets("Activity")
   .Visible = True
   .Unprotect Password:=pwd
   MsgBox nextrow
   .Range("A" & nextrow).Value = playername
   .Range("B" & nextrow).Value = Date
   .Range("C" & nextrow).Value = money
   .Range("D" & nextrow).Value = "Add"
   .Range("F" & nextrow).Value = Environ("username")
   .Protect Password:=pwd
End With
Worksheets("Balances").Select
Worksheets("Activity").Visible = False

 

[dmt32]

Hi,
You don't need to select sheets - Try qualifying your ranges to your worksheets to ensure that your variables have been correctly intialized

Private Sub CommandButton1_Click()
'This macro adds to the bank
    Dim pwd As String, playername As String
    Dim nextrow As Long, money As Long
    Dim wsBalances As Worksheet, wsActivity As Worksheet
   
    With ThisWorkbook
        Set wsBalances = .Worksheets("Balances")
        Set wsActivity = .Worksheets("Activity")
    End With

    With wsBalances
        pwd = .Range("K2").Value
        nextrow = .Range("L2").Value
        playername = .Range("B2").Value
        money = .Range("B3").Value
    End With

With wsActivity
    .Visible = False
    .Unprotect Password:=pwd
    .Range("A" & nextrow).Value = playername
    .Range("B" & nextrow).Value = Date
    .Range("C" & nextrow).Value = money
    .Range("D" & nextrow).Value = "Add"
    .Range("F" & nextrow).Value = Environ("username")
    .Protect Password:=pwd
End With
End Sub

I am assuming that you have a method to increment Nextrow variable?

Hope Helpful

Dave

 

[melodramatic]

That did it perfectly. I need to learn that method of handling entry on sheets.

Thank you so much for your help, Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[melodramatic]

Oh, and I just saw your question about incrementing the nextrow variable. In this case, it's not needed. I only do one entry at a time, and nextrow only goes to figure out the next row # to add to the entry table.

Thanks, though!