# Excel screen updating not working



## mortein (Nov 23, 2008)

Hi,

I have developed an excel spreadsheet with over 100 worksheets. 52 of them relate to the weeks in the year. Therefore I have Week 1 to Week 52. I have a combo box on each page which enables me to click it's dropdown list (Week 1, Week 2 etc up to 52), and go to any week. So if I'm on Week 4 and want to go to week 25 I just click on the combo box select week 25 and I go to week 25.

My problem is that after it goes to week 25 the screen flickers about 20 times. I have included in the code Application.ScreenUpdating = False at the beginning of my code and Application.ScreenUpdating = False at the end but I still get the flickering. The Combo box is called ComboBox1. When I got the flickering I actually renamed the Combobox from 1 to 52 on each worksheet and adjusted the code on each w/sheet but it made no difference. So I'm stuck and would really appreciate help from someone....please!!!

I have copied some of my code below:

Option Explicit


Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

If ComboBox1.Value = "Week 1" Then
Sheets("Week 1").Select
Worksheets("Week 1").Range("A1").Select
End If

If ComboBox1.Value = "Week 2" Then
Sheets("Week 2").Select
Worksheets("Week 2").Range("A1").Select
End If

If ComboBox1.Value = "Week 3" Then
Sheets("Week 3").Select
Worksheets("Week 3").Range("A1").Select
End If

(up to 52 weeks)

Application.ScreenUpdating = False
If ComboBox1.Value = "Week 52" Then
Sheets("Week 52").Select
Application.ScreenUpdating = False
Worksheets("Week 52").Range("A1").Activate
End If

Application.ScreenUpdating = True

End Sub

Looking forward to getting some help. Thank you

Mortein


----------



## etaf (Oct 2, 2003)

you should not need to put 
Application.ScreenUpdating = False
in the areas you have

just once at the begining and then

Application.ScreenUpdating = true

at the end

its possible a case statement may be better for this

Select Case < Expression to test>

Case

Do something

Case Else

Do something else

End Select
------------------------------------------------------

Application.ScreenUpdating = False
Select Case ComboBox1.Value

Case "week 1"
Sheets("Week 1").Select
Worksheets("Week 1").Range("A1").Select

Case "week 2"
Sheets("Week 2").Select
Worksheets("Week 2").Range("A1").Select

etc for the 52
End Select
Application.ScreenUpdating = True


----------



## mortein (Nov 23, 2008)

Hi ETAF,

Thanks for your help but I am still getting substantial flickering (about 30 times) after updating to the code you suggested. I'll explain exactly what I have done:

1. Updated all 52 w/sheets with the following code.

Option Explicit


Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

Select Case ComboBox1.Value

Case "Week 1"
Sheets("Week 1").Select
Worksheets("Week 1").Range("A1").Select

Case "Week 2"
Sheets("Week 2").Select
Worksheets("Week 2").Range("A1").Select

(continued up to week 52) 
End of code is as follows:

Case "Week 51"
Sheets("Week 51").Select
Worksheets("Week 51").Range("A1").Select

Case "Week 52"
Sheets("Week 52").Select
Worksheets("Week 52").Range("A1").Select

End Select

Application.ScreenUpdating = True

End Sub


On Week 1 I have a combobox (called Combobox1), which displays a list of all the weekly worksheets (1 to 52). This same Combobox (1) has been copied to all the other worksheets so that I can move to whatever w/sheet I want. For example, if I am on Weekly Worksheet 10 and want to go to Weekly Worksheet 45, I simply click the Combobox on week 10, select week 45 from the dropdown list and the code takes me to week 45 which is perfect BUT after moving to week 45 the page flickers perhaps 30 to 40 times before it stops.

Any solutions? It's really frustrating to have code that works but I can't cope with waiting 20 seconds while the page flickers. Help Please!!!

Mortein


----------



## mortein (Nov 23, 2008)

Hi ETAF,

Following my message above I experimented with some options I thought might help. What is happening now is that after clicking the Combobox and the week I want, I get a new message ie, 

"The cell or chart you are trying to change is protected and read-only. To modify a protected cell or chart first remove protection using the Unprotect Sheet Command (Tools menu,Protection submenu). You may be prompted for a password."

I then click OK and I WOW!! I go to the worksheet I want..... WITH NO FLICKERING!!!!!! FANTASTIC.

But none of the worksheets are protected. What I do have on each worksheet is a small graph. The data required for the graph is on another worksheet which I have called "CategoryExpenseCalcs" and the cells are definately unprotected.

So where are we at?

The new code you supplied appears to work correctly, I now get no flickering, however I think that because I have a graph on each worksheet it is calling up the error message quoted above. So what I now need help with is how to stop this error message from popping up. 

I did put some code into the first 10 weeks unprotecting the active worksheet at the beginning of the code and the protecting the active worksheet at the end of the code but it did not stop the error box appearing. 

So I'm stuck and would really appreciate your expert help in this matter.

Other than the graphs the worksheets only have simple calcs on them.

Thanking you....very much in advance.

Mortein


----------



## etaf (Oct 2, 2003)

can you upload an example worksheet - With only DUMMY data

perhaps its trying to calculate, you may turn auto calc off 

you can stop auto calculation with 
Application.Calculation = xlManual, 
and return to auto end with 
Application.Calculation = xlAutomatic


----------



## mortein (Nov 23, 2008)

Hi ETAF,

I have everything working properly now thanks to you.

I found the problem. I had the Combobox with it's lit and cell link.
On checking the properties of the cell link I found that it was
protected. As soon as I unprotected it all the flickering stopped. As
you would expect I was "over the moon"!

Thank you ETAF for you help and thanks to this forum that enables 
beginners like me to get terrific help!!

Mortein


----------



## mortein (Nov 23, 2008)

Etaf,

Can you help me with some additional coding.

As you know I have 52 worksheets with 52 comboboxes.
If I am on week 1 and click the combobox as I want to go to
week 10, if I then go back to week 1 the combobox on week 1 
shows week 10.

Is it possible to make the code on week 1 (after it has taken me to week 10)
to default back to week 1 (which is the name of the worksheet)???

So that will mean that from whichever week I am on the weekly worksheet
I select will show in the combobox the week it actually is if you get me.


----------

