Help needed on my code.

[RobbertH]

Hi guys,

I desperately need help on my code that should simply be hiding some columns. I cant get it to work and I dont understand where i go wrong.

The code I have now looks like this:

Private Sub CommandButton1_Click()
    Dim Choice As String
 
 
    'determine which button is checked?
    If Me.OptionButton1.Value = True Then Choice = 1
    If Me.OptionButton2.Value = True Then Choice = 2
    If Me.OptionButton3.Value = True Then Choice = 3
 
    'process the option button
    Select Case Choice
        Case Is = 1
Dim Cell As Range
Dim ws As Worksheet
Dim c As Long
For Each ws In ThisWorkbook.Worksheets
ws.Activate
c = 10
For Each Cell In ws.Range("F2:AK2")
Cell.EntireColumn.Hidden = IIf(Cells(2, c).Value = Sheets("Command").Range("$I$11").Value, True, False)
c = c + 1
Next Cell
Next ws
 
        Case Is = 2
        ' do something
        Case Is = 3
        ' do something
    End Select
End Sub

What it should do is actually quite simple. Whenever in row 2 the value of the cell is equal to cell I11 in the Command sheet it should hide the row. The reason i am working with cases is that the same code would also have to work for different values in row 2 refereing to different cells in the command sheet. However i am sure that when i get the first case to work the others would be easy.

For now the code doesnt work other than that it loops through all the sheets. I have the feeling that it is testing the value only in the command sheet but i dont know how to change the code...

Thanks for your help on this.

Robbert

 

[iliace]

Try replacing this:

Cell.EntireColumn.Hidden = IIf(Cells(2, c).Value = Sheets("Command").Range("$I$11").Value, True, False)

With this:

Cell.EntireColumn.Hidden = (ws.Cells(2, c).Value = Sheets("Command").Range("$I$11").Value)

Note that the IIf is completely unnecessary.

 

[RobbertH]

Iliace,

Thanks for your reply but your suggestion does the same as my IIF and therefore the code still does not work. The code just runs a loop through all the worksheets but it fails to hide the specified columns. If i break the code after running the first worksheet i performed the hiding action well. However when i leave it to run the whole code nothing has happened. Also not to the first sheet. I am lost.... probably it is something rather simple that i am not seeing at the moment but i cant figure out what this is.

Hope you excel gurus can help this relative newbee on coding. Thanks a lot!

 

[RobbertH]

After a long struggly i finally figured out how to solve this. It was indeed a rather simple thing i was just overlooking. Here is the working code for those trying the same.

Dim Cell As Range
Dim Rng As Range
Dim c As Long
Dim i As Long

For i = 2 To ThisWorkbook.Worksheets.Count
        
    
    Worksheets(i).Select
    Set Rng = Sheets(i).Range("A2:AI2")
    Criteria = Sheets("Command").Range("$I$11").Value
    c = 1
    For Each Cell In Rng
    Cell.EntireColumn.Hidden = (Sheets(i).Cells(2, c).Value = Criteria)
    c = c + 1
    Next Cell
    
Next i

 

[iliace]

I'm not sure why my suggestion wouldn't work. Besides removing the IIF, which was sort of an afterthought, the code adds a scope resolution to the ws in question at each step of the For loop. In other words, it's:

ws.Cells(2, c)

instead of simply this, which refers to the ActiveSheet by default:

Cells(2, c)

In essence, the code does the same thing as your revised version. The one differences I'm seeing are, you changed

c = 10

to

c = 1

Also, previously you had:

Range("F2:AK2")

now you're using:

Range("A2:AI2")

could that have been the problem all along?

And, as per my first comment, you don't need to select the sheet you're working with if you're scoping it specifically as in your code.

 

[RobbertH]

No the change of c and the range is just fine tuning of the code. I was working on the code refering to one sheet but other sheets appeared to have larger ranges.

your suggestion definately helped me in the right direction as my revised code is indeed very similar. However, when i just inserted your suggestion the code wasnt working. It was checking the first sheet but failed to do the same for the next couple of sheets.

Anyway, Many thanks for your reply as it sure helped me to get it right.