1

I am very new to VBA and have basically taught myself while building my current Excel 'contract'. My goal is have a list of contract options which are shown or hidden depending on their representative check boxes. There are 12 total options with ranges that I show/remove across 4 worksheets.

In terms of organization, I have utilized modules based on each action. I also named all my ranges

Prior to me protecting my worksheet, when I select a checkbox, all 4 ranges across all 4 worksheets immediately show. When I unselect, they immediately clear their contents and hide. Yay!

Once I protect my worksheet, however, things either slow down to a crawl or I get an error. In my ProtectWorksheet module below, the commented out lines work, but from reading other stack overflow articles it seens better to use the code I have. Unprotected, it works great. Protected I get the "Error 1004': Unable to set the Hidden property of the Range class". If I instead use my commented out code while protected, it works but is super slow.

Technically I can get everything to work...but from a user interface stance it's terrible.

Below is the 1st contract option I have been testing. Please and thank you for any and all help!

under the Excel Objects - sheet2(Data Input)

Private Sub chkDomesticHotWater_Click()

ProtectOFF

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

  If chkDomesticHotWater = True Then
    AddDomesticHotWater
  Else
    'Remove the lines, clear the data, and move the mouse to the top
    RemoveDomesticHotWater
    ClearDomesticHotWater
    Range("A1").Select
  End If

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

ProtectON

End Sub

under the Module: Checkboxes

 Sub AddDomesticHotWater()
    [DataInput_DomesticHotWater].EntireRow.Hidden = False
    [Contract_DomesticHotWater].EntireRow.Hidden = False
    [Invoice_DomesticHotWater].EntireRow.Hidden = False
    [ExpectedCost_DomesticHotWater].EntireRow.Hidden = False
 End Sub
 Sub RemoveDomesticHotWater()
    [DataInput_DomesticHotWater].EntireRow.Hidden = True
    [Contract_DomesticHotWater].EntireRow.Hidden = True
    [Invoice_DomesticHotWater].EntireRow.Hidden = True
    [ExpectedCost_DomesticHotWater].EntireRow.Hidden = True
 End Sub

Under the Module ClearData

Sub ClearDomesticHotWater()
  Range("DataInput_DomesticHotWater").Select
  For Each cell In Selection
    If cell.Interior.Color = RGB(226, 239, 218) Then
      cell.ClearContents
    End If
  Next
  Range("DomesticHotWaterStart").Select
End Sub

under the Module ProtectWorksheet 

Sub ProtectON()
Dim ws As Worksheet
Dim pwd As String

pwd = "123" ' Put your password here
For Each ws In Worksheets
  ws.Protect Password:=pwd, UserInterfaceOnly:=True
Next ws

'Worksheets("Data Input").Protect Password:="123"
'Worksheets("Contract").Protect Password:="123"
'Worksheets("Invoice").Protect Password:="123"
'Worksheets("Expected Cost").Protect Password:="123"
End Sub

Sub ProtectOFF()
Dim ws As Worksheet
Dim pwd As String

pwd = "123" ' Put your password here
For Each ws In Worksheets
  ws.Unprotect Password:=pwd
Next ws
'Worksheets("Data Input").Unprotect Password:="123"
'Worksheets("Contract").Unprotect Password:="123"
'Worksheets("Invoice").Unprotect Password:="123"
'Worksheets("Expected Cost").Unprotect Password:="123"
End Sub

EDIT I was able to speed it up just a tiny bit by updating my Protect On/Off code below, but it's still a 3-5 second delay when I click on my check boxes:

Sub ProtectON()
    Dim ws As Worksheet
    Set WSArray = Sheets(Array("Data Input", "Contract", "Invoice", "Expected Cost"))
    For Each ws In WSArray
         ws.Protect Password:="123"
    Next
End Sub

Sub ProtectOFF()
    Dim ws As Worksheet
    Set WSArray = Sheets(Array("Data Input", "Contract", "Invoice", "Expected Cost"))
    For Each ws In WSArray
     ws.Unprotect Password:="123"
    Next
End Sub

EDIT - SOLUTION? So I don't think this is best practice, nor have I really 'solved' my delay, but I found a workaround. I eliminated the delay when clicking my check boxes by turning on protection yet allowing row formatting. Technically my sheet is no longer 100% protected from user tinkering, but I think that risk is worth removing such an annoying wait time after clicking. 

Sub ProtectON()

Dim ws As Worksheet
Set WSArray = Sheets(Array("Data Input", "Contract", "Invoice", "Expected Cost"))
For Each ws In WSArray
     ws.Protect Password:="123", AllowFormattingRows:=True
Next

End Sub
Haley
  • 13
  • 7
  • You can't hide rows when the sheet is protected, unless you explicitly set that parameter when protecting to allow you to do so. – Kyle Nov 29 '16 at 15:53
  • I turn off the protection at the beginning of my checkbox-has-been-clicked code. How would I set the parameter to all me to do so even if protected? Would I need to allow the formatting of rows? Thanks! – Haley Nov 29 '16 at 16:02
  • I was able to speed it up just a tiny bit by updating my Protect On/Off code below, but it's still a 3-5 second delay when I click on my check boxes or other buttons: Sub ProtectON() Dim ws As Worksheet Set WSArray = Sheets(Array("Data Input", "Contract", "Invoice", "Expected Cost")) For Each ws In WSArray ws.Protect Password:="123" Next End Sub – Haley Nov 29 '16 at 18:28

1 Answers1

0

It should not be that slow, although I really have no clue how fast is your PC and how big is the data. However, here is something you can make better:

Sub ClearDomesticHotWater()

    For Each cell In [DataInput_DomesticHotWater]
    If cell.Interior.Color = RGB(226, 239, 218) Then
        cell.ClearContents
    End If
    Next

End Sub

and remove all selects, they are slowing you down. Go around them like this: How to avoid using Select in Excel VBA macros

Community
  • 1
  • 1
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • The size of my data is the above multiplied by 12 sections, excluding the ProtectWorksheet module. I also have a ton of formulas within the cells I am showing/hiding. Thank you for the removing selects article, I am reading through that now – Haley Nov 29 '16 at 15:59