problem with combo boxes

Hi All

Firstly, question regarding combo boxes... which one should I use: the Form version or the Active X one? (I'm using Excel 2007, but I want the file to be compatible with the 2003 version, and am working in Compatibility mode).

All I want is to happen is that depending on which option a user selects from the drop down box (a total of 10 options), a select number of rows and columns should be hidden/visible (as you'll see from the below code..)

The other issue I'm having is that with the below code, the workbook seems to be very "busy" constantly calculating something or the other. I need the Sub to be run ONLY when a user changes a selection in the combo box, but it appears that every few seconds the workbook will be calculating something.

Finally, I introduced the GoTo Endo line after every option (1 to 10) to try and speed up the Sub when a user makes a selection, but this appears to make no differnce - it still takes forever to run the Sub.

I am not an experienced programmer at all, so please feel free to criticise my methods altogether!!

Thanks

Option Explicit

Private Sub ComboBox1_Change()
Dim Col As Range

On Error Resume Next

Application.ScreenUpdating = False
'No recursion
Application.EnableEvents = False

   
    If Range("B2:B2") = 1 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("21:23").EntireRow.Hidden = False
        Rows("25:26").EntireRow.Hidden = False
        Rows("29:30").EntireRow.Hidden = False
        Rows("32:32").EntireRow.Hidden = False
        Rows("38:41").EntireRow.Hidden = False
        Rows("43:43").EntireRow.Hidden = False
        Rows("45:46").EntireRow.Hidden = False
        Rows("49:49").EntireRow.Hidden = False
        Rows("53:55").EntireRow.Hidden = False
        Rows("58:63").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R19:HQ19").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If
    
    If Range("B2:B2") = 2 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("121:123").EntireRow.Hidden = False
        Rows("125:126").EntireRow.Hidden = False
        Rows("129:130").EntireRow.Hidden = False
        Rows("132:132").EntireRow.Hidden = False
        Rows("138:141").EntireRow.Hidden = False
        Rows("143:143").EntireRow.Hidden = False
        Rows("145:146").EntireRow.Hidden = False
        Rows("149:149").EntireRow.Hidden = False
        Rows("153:155").EntireRow.Hidden = False
        Rows("158:163").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R119:HQ119").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If

    If Range("B2:B2") = 3 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("221:223").EntireRow.Hidden = False
        Rows("225:226").EntireRow.Hidden = False
        Rows("229:230").EntireRow.Hidden = False
        Rows("232:232").EntireRow.Hidden = False
        Rows("238:241").EntireRow.Hidden = False
        Rows("243:243").EntireRow.Hidden = False
        Rows("245:246").EntireRow.Hidden = False
        Rows("249:249").EntireRow.Hidden = False
        Rows("253:255").EntireRow.Hidden = False
        Rows("258:263").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R219:HQ219").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If

    If Range("B2:B2") = 4 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("321:323").EntireRow.Hidden = False
        Rows("325:326").EntireRow.Hidden = False
        Rows("329:330").EntireRow.Hidden = False
        Rows("332:332").EntireRow.Hidden = False
        Rows("338:341").EntireRow.Hidden = False
        Rows("343:343").EntireRow.Hidden = False
        Rows("345:346").EntireRow.Hidden = False
        Rows("349:349").EntireRow.Hidden = False
        Rows("353:355").EntireRow.Hidden = False
        Rows("358:363").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R319:HQ319").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If
    
    If Range("B2:B2") = 5 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("421:423").EntireRow.Hidden = False
        Rows("425:426").EntireRow.Hidden = False
        Rows("429:430").EntireRow.Hidden = False
        Rows("432:432").EntireRow.Hidden = False
        Rows("438:441").EntireRow.Hidden = False
        Rows("443:443").EntireRow.Hidden = False
        Rows("445:446").EntireRow.Hidden = False
        Rows("449:449").EntireRow.Hidden = False
        Rows("453:455").EntireRow.Hidden = False
        Rows("458:463").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R419:HQ419").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If

    If Range("B2:B2") = 6 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("521:523").EntireRow.Hidden = False
        Rows("525:526").EntireRow.Hidden = False
        Rows("529:530").EntireRow.Hidden = False
        Rows("532:532").EntireRow.Hidden = False
        Rows("538:541").EntireRow.Hidden = False
        Rows("543:543").EntireRow.Hidden = False
        Rows("545:546").EntireRow.Hidden = False
        Rows("549:549").EntireRow.Hidden = False
        Rows("553:555").EntireRow.Hidden = False
        Rows("558:563").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R519:HQ519").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If
    
    
    If Range("B2:B2") = 7 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("621:623").EntireRow.Hidden = False
        Rows("625:626").EntireRow.Hidden = False
        Rows("629:630").EntireRow.Hidden = False
        Rows("632:632").EntireRow.Hidden = False
        Rows("638:641").EntireRow.Hidden = False
        Rows("643:643").EntireRow.Hidden = False
        Rows("645:646").EntireRow.Hidden = False
        Rows("649:649").EntireRow.Hidden = False
        Rows("653:655").EntireRow.Hidden = False
        Rows("658:663").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R619:HQ619").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If
    
    If Range("B2:B2") = 8 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("721:723").EntireRow.Hidden = False
        Rows("725:726").EntireRow.Hidden = False
        Rows("729:730").EntireRow.Hidden = False
        Rows("732:732").EntireRow.Hidden = False
        Rows("738:741").EntireRow.Hidden = False
        Rows("743:743").EntireRow.Hidden = False
        Rows("745:746").EntireRow.Hidden = False
        Rows("749:749").EntireRow.Hidden = False
        Rows("753:755").EntireRow.Hidden = False
        Rows("758:763").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R719:HQ719").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If
    
    If Range("B2:B2") = 9 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("821:823").EntireRow.Hidden = False
        Rows("825:826").EntireRow.Hidden = False
        Rows("829:830").EntireRow.Hidden = False
        Rows("832:832").EntireRow.Hidden = False
        Rows("838:841").EntireRow.Hidden = False
        Rows("843:843").EntireRow.Hidden = False
        Rows("845:846").EntireRow.Hidden = False
        Rows("849:849").EntireRow.Hidden = False
        Rows("853:855").EntireRow.Hidden = False
        Rows("858:863").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R819:HQ819").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If
    
    If Range("B2:B2") = 10 Then
        Rows("18:999").EntireRow.Hidden = True
        Rows("921:923").EntireRow.Hidden = False
        Rows("925:926").EntireRow.Hidden = False
        Rows("929:930").EntireRow.Hidden = False
        Rows("932:932").EntireRow.Hidden = False
        Rows("938:941").EntireRow.Hidden = False
        Rows("943:943").EntireRow.Hidden = False
        Rows("945:946").EntireRow.Hidden = False
        Rows("949:949").EntireRow.Hidden = False
        Rows("953:955").EntireRow.Hidden = False
        Rows("958:963").EntireRow.Hidden = False
                
        With ActiveWorkbook.Sheets("1TLA")
            For Each Col In .Range("R919:HQ919").Columns
                Col.EntireColumn.Hidden = _
                Application.Sum(Col) = 0
            Next
        End With
        GoTo endo
    End If

endo:

    Set Col = Nothing
    Application.ScreenUpdating = True
    Application.EnableEvents = True

On Error GoTo 0
End Sub