VBA hide show group

[TDNova]

Hello, Im still very new to excel and been testing alot of diffrent stuff.

But right now ive been thinking about this simple little vba-code

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
Columns("fj:fs").Hidden = False
End If
If CheckBox1.Value = False Then
Columns("fj:fs").Hidden = True
End If
End Sub

Is it possible to lock it to the columns title name instead of the fj:fs .
because if you change the amount of columns inn the group you need to edit it again.
Is there a way to fix this little problem. so you can move them around and add/remove amount of columns without editing every time ?

 

[Johnny C]

Best (simplest) way is probably a loop.

Private Sub CheckBox1_Click()

Dim intColCount as Integer

If CheckBox1.Value = True Then
    Columns("A:ZZ").Hidden = False
Else
    For intColCount = 1 to 500
        If Cells(1,intColCount).Value = "Example column header 1" Or _
        If Cells(1,intColCount).Value = "Example column header 2" Or _
        If Cells(1,intColCount).Value = "Example column header 3" Or _
        If Cells(1,intColCount).Value = "Example column header 4" Or _
        If Cells(1,intColCount).Value = "Example column header 5" ... (etc) Then
            Range("$A$1").Offset(0,intColCount-1).Entirecolumn.Hidden = True
        End If
    next intColCount
End If
End Sub

The danger is if someone changes a column heading ... you could try locking row 1, unlocking everything else and protecting the sheet

 

[TDNova]

Thanks Johnny c for the fast response! it looks interesting!

But will it work on a big database ? because now it will go over the whole thing and match? or am i totally wrong now? because i was thinking about the power of excel, isn't there a limit on how much it can take ?
but Im going to test it later today or tomorrow.Will report back when i do!

 

[My Aswer Is This]

Explain again what your wanting to do.
Are you saying you have values in Row(1) like:

One Two Three Four Five etc.
And you may want to hide all column from Two to Ten?

If so how do you plan to tell the script what header value to look for?

Using a loop to loop through 500 column to hide or unhide them may not be a easy script to write or to run.

Please explain what you mean by group.

 

[Fluff]

Another option is

Private Sub CheckBox1_Click()
   Dim Fnd1 As Range, Fnd2 As Range
   Set Fnd1 = Range("1:1").Find("abc", , , xlWhole, , , False, , False)
   Set Fnd2 = Range("1:1").Find("xyz", , , xlWhole, , , False, , False)
   If Fnd1 Is Nothing Or Fnd2 Is Nothing Then
      MsgBox "oops"
      Exit Sub
   End If
   Range(Fnd1, Fnd2).EntireColumn.Hidden = CheckBox1

End Sub

 

[TDNova]

Explain again what your wanting to do.
Are you saying you have values in Row(1) like:

One Two Three Four Five etc.
And you may want to hide all column from Two to Ten?

If so how do you plan to tell the script what header value to look for?

Using a loop to loop through 500 column to hide or unhide them may not be a easy script to write or to run.

Please explain what you mean by group.

Okey, i will to to explain better.

ATM im using a checkbox to hide fj:fs, so the code im using is posten further up.

I was thinking it would be better to lock it to header/title instead of fj;fs . because if i make a group fj to fs and use the current vba code. then i add additonal columns it would ruin the whole code and i need to change it.

so im asking if there is a way to use the title/headers instead, because then i can add as many columns i want without ruining the connection.

 

[TDNova]

Thanks for the help Fluff ! i will give it a try and report back when i got time.

 

[My Aswer Is This]

If the user want's to change the range without modifying the script each time like I thought he said:

You would need something like this:
Modified Fluff script:

Private Sub CheckBox1_Click()
   ans = InputBox("Enter Start value")
    anss = InputBox("Enter End value")
   
   Dim Fnd1 As Range, Fnd2 As Range
   Set Fnd1 = Range("1:1").Find(ans, , , xlWhole, , , False, , False)
   Set Fnd2 = Range("1:1").Find(anss, , , xlWhole, , , False, , False)
   If Fnd1 Is Nothing Or Fnd2 Is Nothing Then
      MsgBox "oops"
      Exit Sub
   End If
   Range(Fnd1, Fnd2).EntireColumn.Hidden = CheckBox1

End Sub

 

[TDNova]

Since im not allowed to edit.

Additonal information, there is info inn the cell but it does not matter . the only thing the code needs to do is to hide or show the choosen cells. so it's easier to look around the database.
im making alot of checkboxes, so if i want to see 1 part of 20 i check the one i want and hide the rest. but i also want to have the option to choose more than one.

The one i posted first is working fine but i want to optimize it, i don't want to waste time editing everytime something gets added