Error 1004 when Hiding Columns using CommandButton VBA

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
177
Office Version
  1. 365
Platform
  1. Windows
I am having a problem when running a command to unhide or hide columns to allow entry for archival info purposes.

I've tried running the commands in both Macro and CommandButton, and get the same error when I run it.

Here's what I have...

Code:
Private Sub ArchiveEntry_Click()

' HIDES ALL COLUMNS EXCEPT FOR PN & ARCHIVAL COLUMNS, MAKING ENTRY EASIER
' Written by Melody October May
' Created October 8, 2020

Dim pwd As String
pwd = Range("AB1")

'Sheet is in Normal Mode

    If Range("U3") = 0 Then
        ActiveSheet.Unprotect Password:=pwd
        Columns("D:Q").Select
        Range("D:Q").EntireColumn.Hidden = True 'HERE IS WHERE THE ERROR 1004 POPS UP
        Range("U3").Value = 1
        ArchiveEntry.Caption = "Normal View"
        ActiveSheet.Protect Password:=pwd
        Range("R8").Select
 
    Else
 
        ActiveSheet.Unprotect Password:=pwd
        Range("D:Q").EntireColumn.Hidden = False
        ArchiveEntry.Caption = "Archive Entry"
        MissingList.Left = 290.25
        ProtectSheet.Left = 405
        Range("U3").Value = 0
        ActiveSheet.Protect Password:=pwd

    End If

End Sub

I have tried several variations of the line that's throwing me off, as follows:

Range("D:Q").EntireColumn.Hidden = True

Columns("D:Q").EntireColumn.Hidden = True

Dim ColRange As Range
Set ColRange = Columns("D:Q")
ColRange.EntireColumn.Hidden = True


For each of those items, I've tried prefacing the column hidden line with "Worksheets("All Projects")." I've tried ("D:Q").EntireColumn.Hidden, ("D:Q").Column.Hidden, and every other suggestion I've found online.

Does anyone have any idea where I've gone wrong with this? (If it's a simple typo, I'll kick myself for you).

Thank you in advance!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
What is the actual error message?
 
Upvote 0
Run-time error '1004':

Unable to set the Hidden property of the Range class
 
Upvote 0
Is that code in a standard module, or a sheet module?
 
Upvote 0
Is that code in a standard module, or a sheet module?
I don't think I understand the question. The code comes up when I run a commandbutton vba. I just wrote it and dropped it in. I'm not expert enough to even know what you mean by module. My apologies for that.
 
Upvote 0
In that case I suspect that it's in a sheet module.
Is the button on the same sheet you are trying to hide the columns on?
 
Upvote 0
In that case it sounds as though the sheet is not getting unprotected.
If you step through the code using F8, when you get to this lineColumns("D:Q").Selecthave a look at the sheet, is it unprotected?
 
Upvote 0
Yes, I have done the F8 step-through, and the sheet unprotection is working fine.

I apologize for the delays in my answering - I've been working on a jillion things today. Thanks for helping me with working this out!
 
Upvote 0
In that case I have no idea why you are getting that error.
Not sure it will make any difference, but where you have ActiveSheet replace it with Me
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top