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

Chewyhairball

Active Member
Joined
Nov 30, 2017
Messages
312
Office Version
  1. 365
Platform
  1. Windows
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".

VBA Code:
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Have you tried:
If myrange.Value = "All Results" Then

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

Application.ScreenUpdating = False
 
Upvote 0
Hi to all.
You need to use DoEvents twice without using Application.ScreenUpdating, like this:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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".
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top