Getting a 1004 error on an unprotected sheet

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
141
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

Capture.JPG
Error 1004.JPG


My coding from the beginning is as follows:

VBA Code:
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):

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Where is that code located?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
The code you posted in your op.
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
141

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,181
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

Board Regular
Joined
Apr 28, 2003
Messages
141
That did it perfectly. I need to learn that method of handling entry on sheets.

Thank you so much for your help, Fluff!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
141
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,571
Members
410,852
Latest member
WernerS
Top