• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A nicer (shorter) way to this code

Hello dear helpers
I have code that works but is there no nicer (shorter) code possible?
Code:
Private Sub CheckBox8_Click()
If CheckBox8.Value = False Then
Blad3.Columns("L:L").EntireColumn.Hidden = True
Blad4.Columns("L:L").EntireColumn.Hidden = True
Blad5.Columns("L:L").EntireColumn.Hidden = True
Blad6.Columns("L:L").EntireColumn.Hidden = True
Blad12.Columns("L:L").EntireColumn.Hidden = True
Blad13.Columns("L:L").EntireColumn.Hidden = True
Blad14.Columns("L:L").EntireColumn.Hidden = True
Blad15.Columns("L:L").EntireColumn.Hidden = True
Blad16.Columns("L:L").EntireColumn.Hidden = True
Blad17.Columns("L:L").EntireColumn.Hidden = True
Blad18.Columns("L:L").EntireColumn.Hidden = True
Blad19.Columns("L:L").EntireColumn.Hidden = True
Blad20.Columns("L:L").EntireColumn.Hidden = True
Blad21.Columns("L:L").EntireColumn.Hidden = True
Blad22.Columns("L:L").EntireColumn.Hidden = True
Blad23.Columns("L:L").EntireColumn.Hidden = True
Blad24.Columns("L:L").EntireColumn.Hidden = True
Blad25.Columns("L:L").EntireColumn.Hidden = True
Blad41.Columns("L:L").EntireColumn.Hidden = True
Blad42.Columns("L:L").EntireColumn.Hidden = True
Blad43.Columns("L:L").EntireColumn.Hidden = True
Blad44.Columns("L:L").EntireColumn.Hidden = True
Blad45.Columns("L:L").EntireColumn.Hidden = True
Blad30.Columns("L:L").EntireColumn.Hidden = True
Else
Blad3.Columns("L:L").EntireColumn.Hidden = False
Blad4.Columns("L:L").EntireColumn.Hidden = False
Blad5.Columns("L:L").EntireColumn.Hidden = False
Blad6.Columns("L:L").EntireColumn.Hidden = False
Blad12.Columns("L:L").EntireColumn.Hidden = False
Blad13.Columns("L:L").EntireColumn.Hidden = False
Blad14.Columns("L:L").EntireColumn.Hidden = False
Blad15.Columns("L:L").EntireColumn.Hidden = False
Blad16.Columns("L:L").EntireColumn.Hidden = False
Blad17.Columns("L:L").EntireColumn.Hidden = False
Blad18.Columns("L:L").EntireColumn.Hidden = False
Blad19.Columns("L:L").EntireColumn.Hidden = False
Blad20.Columns("L:L").EntireColumn.Hidden = False
Blad21.Columns("L:L").EntireColumn.Hidden = False
Blad22.Columns("L:L").EntireColumn.Hidden = False
Blad23.Columns("L:L").EntireColumn.Hidden = False
Blad24.Columns("L:L").EntireColumn.Hidden = False
Blad25.Columns("L:L").EntireColumn.Hidden = False
Blad41.Columns("L:L").EntireColumn.Hidden = False
Blad42.Columns("L:L").EntireColumn.Hidden = False
Blad43.Columns("L:L").EntireColumn.Hidden = False
Blad44.Columns("L:L").EntireColumn.Hidden = False
Blad45.Columns("L:L").EntireColumn.Hidden = False
Blad30.Columns("L:L").EntireColumn.Hidden = False
End If
If Columns("L:L").EntireColumn.Hidden = True Then
Label8.Caption = "verborgen"
Else
Label8.Caption = ""
End If
End Sub
Thanks
 
Why make it so complicated using CodeName of the worksheet?
Untested.
Code:
Private Sub CheckBox8_Click()
Dim i As Long
For i = 1 To 45
    Select Case i
        Case 3 To 6,12 To 25,30,41 To 45
            Sheets(ThisWorkbook.VBProject.VBComponents("Blad" & i).Properties("Name").Value).Columns("L").Hidden = Not CheckBox8.Value
    End Select
Next
If Columns("L:L").EntireColumn.Hidden = True Then
    Label8.Caption = "verborgen"
Else
    Label8.Caption = ""
End If
End Sub
 
Hello Jindon,
Thank you for your fast reply, but i get an error on this line
Code:
 Sheets(ThisWorkbook.VBProject.VBComponents("Blad" & i).Properties("Name").Value).Columns("L").Hidden = Not CheckBox8.Value
Thanks
 
Thank you Jindon
My version is 2016 and indeed the error has something to do with trust (in Dutch)Now i have to find out what you mean and where i have to tick, exel is Dutch
 
Mine is 2013

While you are in Excel (Not vbe) go to [File] - [Option] - [Security Center] - [Settings] - [Macro Setting]

Terminology will differ since mine is Japanese version.
 
Hello Jindon,
Can i Combine this piece of your great code
Code:
 Dim i As Long
For i = 1 To 45
    Select Case i
        Case 3 To 6,12 To 25,30,41 To 45
With this piece of code
Code:
For Each r In Range("B10:B122")
If WorksheetFunction.CountIf(Range("D" & r.Row & ":Y" & r.Row), ">0") = 0 And Cells(r.Row, 2) <> "" Then
Rows(r.Row).Hidden = True
End If
Next
 
Not really clear.
Code:
Private Sub CheckBox8_Click()
Dim i As Long, r As Range
For i = 1 To 45
    Select Case i
        Case 3 To 6,12 To 25,30,41 To 45
            with Sheets(ThisWorkbook.VBProject.VBComponents("Blad" & i).Properties("Name").Value)
                .Columns("L").Hidden = Not CheckBox8.Value
                For Each r In .Range("B10:B122")
                    .Rows(r.Row).Hidden = _
                    (WorksheetFunction.CountIf(r(, 3).Resize(, 22), ">0") = 0) * (r.Value <> "")
                Next
            End With
    End Select
Next
If Columns("L:L").EntireColumn.Hidden = True Then
    Label8.Caption = "verborgen"
Else
    Label8.Caption = ""
End If
End Sub
 
That is jindon's point. The sheet with codename Blad3 does not necessarily have tab name Blad3, so you can't assume Sheets("Blad3") will work.
 
Hi Deepak ,

Try the following in the Immediate window :

?Sheets(ThisWorkbook.VBProject.VBComponents("Sheet" & 1).Properties("Name").Value).Name

?Sheets("Sheet" & 1).Name

As long as the worksheet tab name is not changed i.e. as long as the tab name is Sheet1 , Sheet2 ,..., the two will give the same correct result.

Now try by changing the tab name from Sheet1 to anything else.

Narayan
 
You should see OP's original code carefully.

Blad3.Columns("L:L").EntireColumn.Hidden = True
Blad4.Columns("L:L").EntireColumn.Hidden = True
Blad5.Columns("L:L").EntireColumn.Hidden = True
 
Hoi Jindon,
Thank you for your reply, I am trying to make these 2 pieces of code work but my knowledge is to small
Code:
Private Sub CommandButton2_Click()
Dim i As Long, r As Range
For i = 1 To 45
  Select Case i
  Case 5 To 6, 12 To 25, 41 To 45
   
  For Each r In .Range("B10:B122")
  .Rows(r.Row).Hidden = _
  (WorksheetFunction.CountIf(r(, 3).Resize(, 22), ">0") = 0) * (r.Value <> "")
  Next
  End Select
End Sub
Code:
Private Sub CommandButton3_Click()
Dim i As Long
For i = 1 To 45
  Select Case i
  Case 5 To 6, 12 To 25, 41 To 45
  Rows("10:122").Hidden = False
  Next
  End Select
End Sub
Hopefully someone can help me out
 
Your code make no sense in terms of loop.

It is only doing the job to the worksheet that have the code, not to any other sheet.
 
Hello Jindon,
Thank you for your quick reply, that's exactly my problem, I am trying to find a way to do it for 20 sheets at once
All ideas welcome
 
Code:
PrivateSub CheckBox2_Click()
Dim i AsLong, r As Range
For i = 1 To 45
   Select Case i
       Case 3 To 6,12 To 25,30,41 To 45
           with Sheets(ThisWorkbook.VBProject.VBComponents("Blad" & i).Properties("Name").Value)
                .Columns("L").Hidden = Not CheckBox8.Value
               ForEach r In .Range("B10:B122")
                    .Rows(r.Row).Hidden = _
                    (WorksheetFunction.CountIf(r(, 3).Resize(, 22), ">0") = 0) * (r.Value <> "")
               Next
           End With
   End Select
Next
End Sub
Code:
PrivateSub CommandButton3_Click()
Dim i AsLong
For i = 1 To 45
    Select Case i
        Case 5 To 6, 12 To 25, 41 To 45
            Sheets(ThisWorkbook.VBProject.VBComponents("Blad" & i).Properties("Name").Value).Rows("10:122").Hidden = False
    End Select
EndSub
 
Last edited by a moderator:
Hoi Jindon,
Thanks again to be such a patient teacher:)
For the first part, I made this of it
Code:
Private Sub CommandButton2_Click()
Dim i As Long, r As Range
For i = 1 To 45
  SelectCase i
  Case 3 To 6, 12 To 25, 30, 41 To 45
  With Sheets(ThisWorkbook.VBProject.VBComponents("Blad" & i).Properties("Name").Value)
  For Each r In .Range("B10:B122")
  .Rows(r.Row).Hidden = _
  (WorksheetFunction.CountIf(r(, 3).Resize(, 22), ">0") = 0) * (r.Value <> "")
  Next
  End With
  End Select
Next
End Sub
The second part, i did not change anything
But in both i get an error
on line
SelectCase i
If translation is ok it says "sub or function not defined"
 
Hi ,

This is a VBA keyword set , it should be :

Select Case

two separate keywords

Similarly :

End Select

two separate keywords.

Narayan
 
Back
Top