problem with combo boxes

**sunilmulay** · 05-16-2009, 07:33 AM

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

**royUK** · 05-16-2009, 07:55 AM

Before you go any further, if you want a program to be compatible with an earlier version you should develop in the earliest version. You will probably have to make some slight changes according to the version with 2007.

I generally use the Controls Toolbox activeX controls.

It migh help to see an example workbook

**sunilmulay** · 05-16-2009, 09:14 PM

Thanks Roy for your reply.

i started to prepare a slimmed down workbook to post on the forum, deleting all the unnecessary code and sheets and what I found is that the problem went away. So it appears that the problem may actually be coming from another sheet.....

I'm going to try and find out the source of the problem and get back to you. I can't post the whole workbook - there are too many sheets and code.

I'll back soon.

Thanks
Sunil