Macro Code With ScreenUpdating Off Turns Back On
Aug 8, 2008
A little context:
Searching this forum for "sleep", "delay" or "animation" will bring up a host of threads referring to the kernel32 function, sleep. This is a great way of putting small delays into code without the potential "synchronisation" problems of application .ontime calls or the "ugliness" of multiple-thousands looping.
My question:
When I call sleep (with values in the hundreds of milliseconds) several times in a macro, it seems like the computer just "gives up" screen updating after a few (maybe 50) iterations. The macro still takes the time I'd expect, accounting for all the sleeps, but it stops showing the intermediate steps and just shows the end result after the macro ends.
Is this something to do with RAM? Is there something about calling this command too much or too frequently that kernel32 doesn't like? Is my computer a useless bag of nails and spit?
View 3 Replies
ADVERTISEMENT
Oct 24, 2007
The following code is part of a much larger module. In the code I've set application.
screenupdating to false; however, it resets itself within 2 lines of unrelated code.
The first message box returns "Screen Updating = False", the second returns "Screen Updating = True". I have a suspicion that this is related to the Cube Analysis Add-in
I recently installed (Excel Add-in for Analysis Services).
Application.ScreenUpdating = False
MsgBox ("Screen Updating = " & Application.ScreenUpdating)
Application.StatusBar = ("Setting up report... Finding you selection...")
'get the user selected text and the column in which it can be found
lookupVal = Sheets("Sales Leads").TreeView1.SelectedItem.Text
lookupCol = Mid(Sheets("Sales Leads").TreeView1.SelectedItem.Key, 3, 1) 'the nodekeys are always "NK" & <column> & <count of nodes> so the third value in the key is the column.
MsgBox ("Screen Updating = " & Application.ScreenUpdating)
View 6 Replies
View Related
May 12, 2009
I am dealing with several very large spreadsheets using VBA to do various things. I found that my code worked well, but was taking a long time to run. The biggest time consumer was my use of the AutoFilter features. I have since turned calculations to manual before my code runs and set it back to auto when my code is done running. What are the potential consequences of my turning calculations to manual and then back to auto?
View 2 Replies
View Related
Jul 20, 2009
I have been trying to get a back button to work on an 2003 excel document that works like a website.
In the web toolbar there is a facility to go back and forward (very much like using internet explorer). I want a button on the actual sheet that does this command. If i record a macro and click the web 'back' button it records the end result of the process, not the process itself -
eg if sheet 1 and sheet 2 link to sheet 3 and I click a hyperlink from sheet 1 to sheet 3 then create a back button on sheet 3 and record a macro by clicking on the web toolbar back button the macro records the end result - eg Application.Goto Reference:="Sheet1!R14C8". However if i then go to sheet 3 through sheet 2, the back button I created takes me to sheet 1 not back to sheet 2.
Anyone know the code I could use to make it actually go 'back' to the previous page?
View 13 Replies
View Related
Mar 31, 2014
Any formula to repopulate the data back to userform for editing. I need the data to populate when a record is selected from a combo box [named URN for unique reference number]
View 5 Replies
View Related
Jun 13, 2014
VBA code in worksheet won't revert a changed cells color back to 0(white) after removing the comment.
Observations: code does set the desired cell colors to Yellow after inserting a comment, however removing said comment, and re-activating the worksheet does NOT reset the color after deactivating/re-activating the worksheet.
[Code] ......
View 2 Replies
View Related
Aug 6, 2012
I am attempting to navigate to a webpage using IE. I have been successful at getting to the appropriate webpage.
However, I would like to open a link within the webpage in the browser in another window(Which is an image). Copy the image then paste it back into excel. I have been successful at opening the webpage using a Userform.
Code:
Sub Anthro()
Application.ScreenUpdating = False
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
[Code] .......
The user input number for example:
If the user inputs the number 25423310 into the txtbox then it navigates to a certain webpage.
On this webpage and all other webpages where different #'s are used are very similar. The number (25423310) is always used to identify an image. However it has more characters. For example the image is always on line 30 of the HTML code. On this example the HTML code lists the image as:
The number used in the txtbox to naviagete to the URL Ex: 25423310) is always within line 30 of code in HTML. Is it possible to run a query or parse this line of code and direct ie to open this image in a new window then copy and paste it based on the criteria of the user input? Ex. 25423310?
Code:
Sub PropInfo()
Dim appIE As SHDocVw.InternetExplorer
Set appIE = New SHDocVw.InternetExplorer
Dim varTables, varTable
[Code] ....
View 9 Replies
View Related
Aug 11, 2012
I have a layout something like the following:
A1
A2
A3
[Code]....
Where each (i.e., A1) represents a location. I have tried to use a coordinate system but this will not work for the back-to-back locations. (Assuming each location is 2 feet wide, For example A1 to C1 is 4 feet apart, not 2 feet (as Euclidean or rectilinear would calculate it as).
Would there be a way to incorporate an if statement for those locations that are back-to-back? As a rectilinear distance calculation would work as long as the locations are not part of the same "block".
Ultimately I am looking to have a matrix which contains all the distances between each location:
A1
A2
A3
B1
[Code].....
View 4 Replies
View Related
Mar 17, 2008
have been using the simple formula below to determine between a friend and myself whos turn it is next.
=IF(Q22
View 9 Replies
View Related
Oct 7, 2008
I have a macro in one file which uses an input box where the user types in a subcontractor code to retrieve address and contact details from another file.
If the user inputs the incorrect subcontractor code, an error message box pops up. Currently the user then has to select a button in the original file to restart the macro at the input box stage.
What I want to happen is when ok is selected on the error message, to return to the input box so in my macro below where the error message says "An error occurred - you asked for a subcontractor that does not exist. Please try again." and the user selects OK, I would like it to return to the 'Enter the Subcontractor line and the "Please enter the Subcontractor code" input box reappears.
View 8 Replies
View Related
Aug 31, 2007
macro which can paste the value from A1, to another B1 and when A1 is edited again, pasted it in B2. This will probably go on and on until B30. I have tried the if else then in Macro, and even the if elseif, but both don't go futher than B2. E.G A1=1 B1=1 A1=4 B2=4 A1=3 then nothing happens to B3. If there is a way to make this work, it will be great. The way i am tryin to get it is to have =if(B1="","",1) for each cell from C1:30 and with C31 the formula "=count(C1:C30)+1". Then i formated C31 to a custom and make it so that before numbers, there is an B. This means that if B1,2,3 and 4 and numbers in them, then C31 will read B5. That will be the cell i want my next record to go. I would prefer it if there is just a code that i can enter that will go to the next cell when it knows the current cell has a digit in it but if i have to, the count way will do,
View 9 Replies
View Related
Sep 17, 2009
I have written a number of macros, and I typically use Application.Screenupdating = False at the start, and set it True at the end of each macro. However, I now have some big macros which call others as subroutines. This results in the screen updating for each pass thru a called macro, as the Application.Screenupdating = True statement is executed. Is there a means to repress this at the top level, or is there a better approach to take in writing code which will stop screen update for a macro, but not restart it each time the macro is called as a subroutine?
View 2 Replies
View Related
Feb 3, 2010
I've added a sort descending With Activesheet in this macro How can I better enable the completion before Application.Screenupdating = True.
Sub InsertRows_Xslopes25cb2()
Dim LR As Long, i As Long
Unload UserForm8
Application.ScreenUpdating = True
UserForm9.Show vbModeless
DoEvents
Application.ScreenUpdating = False
LR = Cells.Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
On Error Resume Next
Range("F9:F" & LR).SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
Range("G9:G" & LR).SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
On Error GoTo myErr
View 9 Replies
View Related
Feb 9, 2010
I have VBA code that hides and display columns. I have it coded in between Application.ScreenUpdating = False and Application.ScreenUpdating = True commands thinking that it will not show all the flashing and movement of columns.
View 9 Replies
View Related
Aug 1, 2013
When I write out ranges in cells like:
1-10
10-20
20-30
30-40
Etc.
Excel turn some of these ranges into dates such as 01-Oct, which is extremely annoying. But trying to change to different date formats but it still keep it to dates. what I can do?
View 5 Replies
View Related
Jan 15, 2008
I have a spreadsheet full of data and in one column the are a set of text codes some of them start with a -... ie -AGEST excel automatically turns this into #NAME?
View 9 Replies
View Related
Jul 13, 2009
I need to copy a changing source cell, paste its value into another specific cell (always the same), and then return to the source cell for continued action (ie range selection, copy & value paste, which I can code).
This action is then followed in the next cell to the R of the first cell copied, etc to end of data. I can code the move to the R.
How do I return to the source cells as part of a loop?
Specific notes included in attached - I hope I've explained it clearly.
View 12 Replies
View Related
Jan 5, 2007
I am using the following code in my Macro
Application.ScreenUpdating = False (I set it to "True" at the end of the sub)
But I can still see Excel changing tabs etc.
View 9 Replies
View Related
Jun 29, 2007
I think it may be somthing to do with the loop?
The macro is run off a toggle button which then selects a range of cells for the source data for my chart.
Sub ChartDynamic()
Dim x As Integer
Dim r1, r2 As Long
Application.ScreenUpdating = False
'H30 is the Period number
x = Range("H30").Value
Range("W:W").Select
Selection.Find(What:=x, After:=ActiveCell, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate........................
View 9 Replies
View Related
Oct 22, 2009
I am using the following code to turn off the screen updating during code execution
Application.ScreenUpdating = False
Is there anything that would still show the screen updating desptite being set to 'False'?
View 9 Replies
View Related
Jul 19, 2006
I have a form that i am using that I would like to update at certain points in the code. I have the screenupdating set to false but it seems that this is keeping the userform from refreshing as well. Is there a way to refresh the userform without turning the screenupdating back on (because the user cannot see what is happening behind the scenes.
View 7 Replies
View Related
Feb 3, 2008
I am trying to paste in a decimal equivalent chart, loacted here:
http://www.advancedtool.com/prod02111.asp
which basically has a mixture of whole numbers and fractions, and how they equate in decimals.
When I paste this in, all the fractions turn into dates. So for instance 1/64 turns into Jan/64, and 1/32 is Jan/32. Now, I could go in and change each one, but I would like to know how to do this with cell format on the entire sheet....
I have tried to change the cell formats to "general" or "text" and of course fraction, but in this case, I have to go in and do each fraction cell individually. Now maybe I am being too lazy, and in the time it took me to look up my login information, and type this out, I likely could have re-done the worksheet, each cell individually, but I would like to know the way to fix this automatically.
View 11 Replies
View Related
Nov 26, 2008
I have a form in Excel that has comments fields. Each comments field is a merged cell consisting of four rows and four columns. My understanding is that Excel should be able to display 1024 characters. The input that's causing me trouble has 360 characters. At around 300 characters, the text box gets filled with ############################# instead of text.
If I size the font down significantly to where there would be absolutely no question that the text would fit, it is still #######. I may have to end up using text boxes, but they are a bit troublesome for the less Excel-savvy users of the form.
View 4 Replies
View Related
May 22, 2013
I have a macro running in a workbook that gathers some data (a date, a string and a few arrays). Towards the end of this macro, I need it to open another workbook and run a macro that sits in this other workbook, using the data from the first workbook. I then need it to return some results (several integers) back to the first macro to be pasted into the first workbook.
I gather that I can't use 'Call' as the second macro is in another workbook.
I've found that I can use Application.Run but I'm unsure how to carry variables back and forth using this.
How to move the variables between macros / workbooks using the Application.Run option, or maybe another way of doing things?
View 1 Replies
View Related
Mar 26, 2009
I want to have a small button at the top of the sheet that when pressed, will automatically reset all filtered autofilters back to show all.
View 9 Replies
View Related
Mar 22, 2006
i have written so code that opens a spreadsheet and runs some macro from the new speadsheet and copys info over to the other spreed sheet i have Application.ScreenUpdating = FALSE at the begininnig and turn it back on at the end but it still making the screen show everything can anyone help.
Sub save()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ChDir "P:OFFICERiskOPTIONSOptions Control FunctionRisk Analysis"
Workbooks.Open Filename:= _
"P:OFFICERiskOPTIONSOptions Control FunctionRisk Analysis emplate euribor risk module.xls"
Windows("Euribor risk module.xls").Activate........
View 9 Replies
View Related
Jan 22, 2009
For some reason Application.ScreenUpdating = False has stopped working for me.
For testing purposes, I have freshly opened Excel (2003 SP3) and into the brand new workbook, added the following macro:
*****************
Sub Test()
Application.ScreenUpdating = False
For i = 1 To 40
ActiveCell = i
ActiveCell.Offset(1, 0).Select
Next i
End Sub
*****************
When I run this macro, I see the individual values being written on the screen.
Stepping through the code and using Watch, I can see that Screenupdating is set to False by the first line, but as soon as the Activecell is changed, Screenupdating is reset to True.
If I turn events off by adding Application.EnableEvents = false to the start of the macro, all works as expected, however I do not want to use this "workaround" as I use event driven macros.
View 9 Replies
View Related
Jul 18, 2006
I've got a listbox which populates a second listbox from cells on a hidden worksheet which is done through macros. Every time I select a value in the listbox, the screen " flickers" to the hidden worksheet. I want to be able to do the whole process without that "flickering". I tried using Application.ScreenUpdating = False, but it still flickers. Have I overlooked something?
Sub GetStaffteamQuery()
Application.Calculation = xlCalculationManual
Dim sConnParams, sSQL As String
Dim dbFile, dbPath, dbName As String
ThisWorkbook.Activate
Sheet3.Select
dbFile = "CounsellorData.mdb"
dbName = "CounsellorData"
dbPath = Sheet3.Range("databaselocation").Value
selectedgroup = Sheet3.ListBox1.Value
If VBA.Right(dbPath, 1) <> "" Then
' if last char in dbPath has no slash.................
View 4 Replies
View Related
Feb 6, 2007
If I'm starting in Sub #1 and I have turned screenupdating off, then later in the procedure I call a function that also has screenupdating off (and also back on at the end of the function), will I get a slight flicker here? I'm noticing a slight one, and I can only imagine that it's where the screenupdating is being triggered again. (removing the updating from the function isn't an option, because another procedure calls the same function & needs it turned off)
View 2 Replies
View Related
Jul 8, 2007
trying to copy both the values and the format of a certain range from one worksheet to another I am faced with two problems:
a) how can I copy something without having to previously add ".select"? (in the end I want to avoid any screen flickering)
b) how can I copy both the values and the format in one line (currently I have to use two lines)
Current problem:
Below you can find a code snippet which is repitetiously launched in order to copy data(+format) from many sheets to one sheet, there is other code as well (not shown here), which is launched in between (dealing with charts and pictures). In order to avoid some screen flickering I have already included the "screenupdating=false" line, and I would like to drop the usage of any ".select" in my code, as any ".select" seems to update the screen irrespectively of "application.screenupdating=false" or not.
However, as soon as I erase any ".select" command, I get a runtime-error 1004 at the SECOND run of this code snippet here. The only way to circumvent this seems to include ".select" prior to the line causing the error. Why at the second run? Well, I think, it is because after the second run, the focus might remain with a chart and not anylonger at any cell. Is there any way executing the below code without the screen-flickering?
Sub copySample(ByVal M As Integer)
Dim wksTarget As worksheet, wksNow As Worksheet
Const maxRowsConsidered=1000
Set wksTarget=Worksheets("target")
Set wksNow=Worksheets(M)
columnNow = ((m-1)*8)+1
Application.ScreenUpdating =False
View 9 Replies
View Related
