Error 1004 when Hiding Columns using CommandButton VBA

melodramatic

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

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
What is the actual error message?
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
153
Run-time error '1004':

Unable to set the Hidden property of the Range class
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Is that code in a standard module, or a sheet module?
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
153

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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?
 

melodramatic

Board Regular
Joined
Apr 28, 2003
Messages
153

ADVERTISEMENT

Yes, it is on the same sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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?
 

melodramatic

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,765
Members
416,202
Latest member
donya ba

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
Top