Worksheet Change - Shape not appearing until after code has run.

[Chewyhairball]

Hi

When cell BG3 contains the text "All Results" the following code runs. It can take up to 5 seconds to run so thought i would just have a textbox popup saying in progress as it was running.
I have placed it in the code where i thought it would be the first thing to run, followed by screenupdating = false to hide the rest of the code while running.

It doesnt seem to matter where i put it the shape only ever appears after the whole code has run! Its driving me mad

Have tried adding code to stop calculations, added application wait time and different iterations of screen updating ture/false but i cannot get the shape to be the first thing that happens when the cell say
"All Results".

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myrange As Range

Set myrange = Range("bg3")



If myrange.Value = "All Results" Then

ActiveSheet.Shapes("Rounded Rectangle 27").Visible = True

Application.ScreenUpdating = False



Range("Results1[[#Data],[Comments]]").Select

Call ConvertToComment

Range("Results2[[#Data],[Comments]]").Select

Call ConvertToComment

Range("Results3[[#Data],[Comments]]").Select

Call ConvertToComment

Application.ScreenUpdating = True

End If

End Sub

 

[johnny51981]

Have you tried:
If myrange.Value = "All Results" Then

ActiveSheet.Shapes("Rounded Rectangle 27").Visible = True
DoEvents

Application.ScreenUpdating = False

 

[rollis13]

Hi to all.
You need to use DoEvents twice without using Application.ScreenUpdating, like this:

If myrange.Value = "All Results" Then
DoEvents
ActiveSheet.Shapes("Rounded Rectangle 27").Visible = True
DoEvents
'Application.ScreenUpdating = False   '<- do not use

Remember to add ActiveSheet.Shapes("Rounded Rectangle 27").Visible = False at the end of your event macro.

 

[Chewyhairball]

Thanks guys. Good to know about the DoEvents thing and will be doing a bit of reading to understand what it is doing but It didnt help with this.

It is like the sheet is doing all the calculations (and there are a lot via multiple sheets) and only after it does that will the macro run.
When i change cell B2 the results I can see in other cells dont change straight away but as soon as the calculations are done and the figures have changed then the macro runs.

 

[rollis13]

Sorry, no other idea; tested once again your macro and with my modifications it works exactly as it should. The shape "Rounded Rectangle 27" appears just after changing cell BG3 to "All Results".

 

[Chewyhairball]

I have turned off the calculations to the whole workbook and added a 'calculate' line at the bottom which seems to now work. It must be something to do with the calculations across all the sheets.

I have noticed that without putting Application.screenupdating = false you can see the macro working, changing sheets etc which is untidy. What is the DoEvents actually doing?

thanks

 

[rollis13]

Instead of a shape why not use Application.StatusBar ? I know it's very small in the bottom line of the Excel window but it will work with Application.ScreenUpdating.

 

[Chewyhairball]

Hi

So I changed it to trigger whenever the cell is changed and not just when the text "All Results". I didn't think of that at the time but gives me more options.
Also changed the shape to a userform as i discovered if i wanted this to run on other sheets it would work with a shape without creating more of them.
I have also turned calculations to manual and only calculate when i need it to using application.calculate

So when i change the cell now my message pops up straight away to say 'update is in progress'
the calcs are then run for the whole workbook and finally another message pops up for a few seconds telling me the update is complete.

thanks for all you input with this, much appreciated

rory

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myrange As Range
Set myrange = Range("bg3")

If Not Intersect(Target, myrange) Is Nothing Then

DoEvents
Updateinprogress2.Show
DoEvents

Application.ScreenUpdating = False
Range("Results1[[#Data],[Comments]]").Select
Call ConvertToComment
Range("Results2[[#Data],[Comments]]").Select
Call ConvertToComment
Range("Results3[[#Data],[Comments]]").Select
Call ConvertToComment
End If
Application.ScreenUpdating = True
Application.Calculate
Updateinprogress2.Hide
Updatecomplete.Show
Range("BG3").Select
Application.Wait Now + TimeValue("0:00:02")
Updatecomplete.Hide
End Sub

 

[rollis13]

Glad we were of some help.