Help needed on my code.

RobbertH

Active Member
Joined
Apr 29, 2008
Messages
310
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:
Code:
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
Try replacing this:

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

With this:

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

Note that the IIf is completely unnecessary.
 

RobbertH

Active Member
Joined
Apr 29, 2008
Messages
310
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

Active Member
Joined
Apr 29, 2008
Messages
310
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.
Code:
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

Well-known Member
Joined
Jan 15, 2008
Messages
3,543
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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:

Code:
ws.Cells(2, c)

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

Code:
Cells(2, c)

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

Code:
c = 10

to

Code:
c = 1

Also, previously you had:

Code:
Range("F2:AK2")

now you're using:

Code:
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

Active Member
Joined
Apr 29, 2008
Messages
310
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,654
Members
414,325
Latest member
kfg1287

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