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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Watch MrExcel Video

Forum statistics

Threads
1,127,763
Messages
5,626,727
Members
416,201
Latest member
brianhf

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