How to stop the flashes and shakes of the tabs?

[feipezi]

Hi,
I have a few ComboBoxes on each tab. The contents of dropdown lists are hierarchical. For instance, State ->cities -> boroughes and so on. What I want is if I pick a state, I want to see the cities only in that state; if pick a city, I want to see all the boroughes in that city instead of all the boroughes. Ex: NY->NYC->(Bronx, Manhattan, Brooklyn and etc.)
I used the following code to get what I want:


Private Sub distbox_click()
Application.EnableEvents = False
Application.ScreenUpdating = False
Select Case distbox.Text
Case "CMAA"
regbox.Text = "CMA"
terrbox.ListFillRange = "terr_cmaa"
Case "CMAB"
regbox.Text = "CMA"
terrbox.ListFillRange = "terr_cmab"
Case "CMAC"
regbox.Text = "CMA"
terrbox.ListFillRange = "terr_cmac"
Case Else
regbox.Text = ""
terrbox.ListFillRange = "territory_name"
End Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


It was working fine until I clicked the Graph buttons. The selected tab started shaking and trembling; if I was on the Graph tab, the curves on the tab would come out column by column instead of show itself all at once.
I feels like there is some conflict among the events but Application.EnableEvents won't help.
If I get rid of the code above, everything is back to normal.

I am sure it has something to do with the code that creates the graphs but I cannot find the problems. The charts are to graph the 12 month trending of some products. Here is the main code for the graphs:


Sub GraphCore(showtab, maintab, product, mkttc As String, cnum As Double)
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheets(showtab).Visible = True
Dim headr As Variant, i
headr = Range("a13:dz13").Value
For i = 1 To UBound(headr, 2)
If LCase(headr(1, i)) = mkttc Then
mkttccol = i
ElseIf LCase(headr(1, i)) = product Then
startcol = i
End If
Next
Sheets(maintab).Select
srw = Selection.Row
totsmtc = Range(Cells(srw, mkttccol), Cells(srw, mkttccol + cnum))
gheader = Range(Cells(12, 1), Cells(12, 4))
grtitle = Range(Cells(srw, 1), Cells(srw, 4))
For x = 0 To mkttccol - startcol - cnum Step 12
For i = 0 To 11
scl_prd = startcol + i + x
scl_mtc = mkttccol + i
'MsgBox srw & "*" & scl_prd & "*" & scl_mtc
pct = Cells(srw, scl_prd) / Cells(srw, scl_mtc)
z = x / 12 + 24
Sheets(showtab).Cells(z, i + 3) = pct
Next
Next
Sheets(showtab).Select
Range("aa1:al1") = totsmtc
Range("aa2:ad2") = gheader
Range("aa3:ad3") = grtitle
Range("az1:az1") = maintab
Range("C24:N31").NumberFormat = "0.00%"
Range("a32:a38").EntireRow.Hidden = False
Application.ScreenUpdating = True
Application.EnableEvents = True
On Error GoTo 0
End Sub


Thanks in advance.

 

[SkipVought]

Hi,

I strongly advise against using the Activate and Select methods, except for displaying what you want the user to see.

    Sheets(maintab).Select[b]
    srw = Selection.Row[/b]

How can you be sure that the Selection is? How could ANYONE know by looking at you code? It's a crap shoot! Eventually you loose!

Rather...

    srw = Sheets(maintab).Cells(r,c).Row

and

    with Sheets(showtab)
      .Range("aa1:al1") = totsmtc
      .Range("aa2:ad2") = gheader
      .Range("aa3:ad3") = grtitle
      .Range("az1:az1") = maintab
      .Range("C24:N31").NumberFormat = "0.00%"
      .Range("a32:a38").EntireRow.Hidden = False
    end with

However, not knowing how your chart is constructed, what ranges are source data for your chart, it's kinda hard to give specific advice for the problem you described. I don;t see any reference to a chart in your code.

FYI, the ways that I use to dynamically change chart source data are...

If the x-axis is fixed, use formulas, like SUMPRODUCT, to return the correct data for each series.

Use MS Query to return a plot resultset, based on the selection criteria.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

You may also want to use

Application.Screenupdating = false

as the 1st line of your code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[feipezi]

Hi,
Thanks for the input.
xlbo, I've already put what you suggested in the code and I don't think it matters if it's the first or second line.

I know it's hard for me to explain, and for you to understand how the charts are created without attachments in the email. But let me try.

I also know that I'm not supposed to use Select unless I have to. But duty calls. The part of the data is like:
[blue]...productA1,...,productA12...productB1,...productB12...
(12: the latest month; 1: 11 months back). [/blue]

If the user wants to see a graph, he has to SELECT one of the rows in the main tab. As for which row the user will select, as a designer, I have no idea. It's totally random. The number of rows ready to be selected is from 1 to 14,000. The user can select any row he wants. In another word, the graphs are based on user's selection.

As the user selects one row, then he has to press one of the graph buttons (CommandButton). My Sub GraphCore will put the product metrics in the table of a graph tab
[blue]
... productA1, productA2,...,productA11, productA12
... productB1, productB2,...,productB11, productB12
... productC1, productC2,...,productC11, productC12
...
[/blue]
The chart on top of the table will graph based on the numbers of those products.

This is basically the story about how the graphs are created.

From GraphCore, you can see there is no summation (aggregation) involved, but I do need the calculation of percentage (shares).

Hope this help you understand what I am doing and what I asked for.
Thanks again to all the folks.