After Refresh Where Does Code Go
Jul 21, 2008
My macro appears to be refreshing the data from the queries on my supporting worksheets at the wrong time. It apparently doesn't go in the order it appears in my VBA code (and if anyone can explain that I'd love to know what's going on there).
Anyway, all I want to have happen is for all the queries to refresh, and THEN for the rest of my macro to run. So for example, if the user changes the job number in cell A1, or the Date in cell A2, I want the queries to refresh for this new information, and when that is done I want the rest of the macro to run.
I found the following code, but I don't understand enough about VBA to know where exactly it goes. Could someone please walk me through this? I tried putting in Sheet 1 under Microsoft Excel Objects, and I tried putting it in a Module, but neither worked. I'm sure it's some combo of both, but I can't figure it out.
View 9 Replies
ADVERTISEMENT
Apr 13, 2014
what i am trying to do is...... i have a website link which i kept open. For every after 5 mins my session logsout.
is there any alternate solution or a vba code which keep refreshing the page once in 4 mins.
View 1 Replies
View Related
Jul 15, 2014
I want to put a macro in that will refresh the workbook before this next part of code is run:
[Code] .....
I tried putting this in before:
[Code] ....
But it isn't refreshing the workbook before the next part is run
View 2 Replies
View Related
Sep 9, 2006
I am writing a routine to retrieve multiple stock quotes by looping through a bunch of sheets and refreshing a bunch of querytables that access the web. My data source (Livecharts) is often clogged up and queries will fail or get lost in space so after initiating the queries I wait for a while using a waitable and then check to see if each query has completed or is still refreshing. If they are still refreshing I cancel them and refresh again. Or at least that was the plan. What I have discovered is that as long as the vba code is running the results from the queries do not come back into excel. As soon as the code is exited or I go into break mode in the debugger the queries complete.
Is there some way to get the queries to refresh during code execution? Here is a code snippet that shows what I am trying. I have defined a Class Module defining an "EventfulQTable" and it's associated methods and properties so I can have a QueryTable with events. The sub Wait uses the windows API SetWaiTable command to pause for a specified period of time. It includes a DoEvents command which I thought would allow the returning queries back into Excel but no such luck.
Sub Refresh_Queries()
Dim SheetNum As Integer, ListSheet As Integer
Dim eqtQT As New EventfulQTable
Dim QueriesDone As Boolean
SheetNum = 1
ListSheet = 12
QueriesDone = False
On Error Goto QueryError
View 4 Replies
View Related
May 6, 2007
In cell B9 I have a simple Time formula which can be refreshed manually through F9.
Would like a code that automatically refreshes the time every 30 seconds, if that is possible.
View 9 Replies
View Related
Nov 19, 2008
I have written some simple VBA code so that on Sheet 4 I have a drop down list box to choose the month.
When the month is chosen by the user it moves to the active cell and changes the dates, as well as dates in some other columns (I used activecell.offset ....).
This code runs perfectly fine when I haven't refreshed the pivot tables in the workbook. But straight after a refresh, if I try and change the month it gives a run time error and gets stuck at the first instruction:
Sub cboMonth_Change()
Sheet4.Range("C2").Activate
View 9 Replies
View Related
Mar 30, 2013
I have 2 drop downs that when changed, auto refresh all pivot tables. My problem is getting the columns to auto-adjust based on the refresh or change of the data in the pivot table. Listed below the code I have thus far for the auto-refresh on pivot tables. How to auto expand all columns simultaneously.
The pivot tables are based off of tables on a different sheet(TOS Tables). So the code listed below is in the table sheet, not the pivot table sheet (TOS Customer Level). So followup question will be, which sheet to put the auto-expand columns code?
VB:
Private Sub Worksheet_Calculate()
'If data on this worksheet changes, refresh the pivot table
Sheets("TOS Customer Level").PivotTables("PivotTable2").RefreshTable
Sheets("TOS Customer Level").PivotTables("PivotTable5").RefreshTable
End Sub
View 3 Replies
View Related
Feb 25, 2013
I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.
The working code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N5").Value = 2 Then
Columns("O:R").EntireColumn.Hidden = True
Else
Columns("O:R").EntireColumn.Hidden = False
End If
End Sub
N5 is the linked cell for the option button.
View 4 Replies
View Related
Jan 4, 2008
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
View 9 Replies
View Related
Jul 27, 2006
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
View 4 Replies
View Related
Jan 13, 2009
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?
View 2 Replies
View Related
Nov 27, 2007
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies
View Related
Sep 23, 2007
I can download my data from the web. I can set it to download every so many minutes. But, I have to select run on my vb program to complete calculations and put data in correct format/sheets. I am trying to put a delay/timer in the program so it will refresh on itself and I will not have to keep selecting program run. If I put this in program it appears to refresh in a timely manner however I do not get any data on the sheets ? I have tried the wait routine and the if now = time + routine
View 11 Replies
View Related
Sep 17, 2009
I want to refresh a cell by triggering an event using VBA code. I have tried to refresh cell range value using this below code.
View 7 Replies
View Related
Mar 12, 2009
The below is my problem. I have a excel wokbook that contains 2 reports from an outside source and on top of the reports i have some excel calculations.One of report has drill down capability. Everytime i open my excel workbook and refresh it the excel forumals also get refreshed along with my 2 reports and i get my results. But when ever i drill down something on the report the excel formulas are not getting refreshed automatically.
I end up refreshing the whole workbook to get the correct results which is a very time consuming process considering the amount of data i am trying to pull. And drilldown is something the users do very often and they don't like refreshing the whole workbook.
Is there any way to update the excel functions automatically whenever i drilldown?
View 10 Replies
View Related
Feb 12, 2009
I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.
I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.
I have been working on this spreadsheet for weeks and can't get past this part!
View 9 Replies
View Related
Feb 23, 2009
I have four to five screens being displayed. After 10 hours the screens time out and go into password protection mode.
Is there a way to send a refresh to each of these IE windows to keep them from going into password mode.
They are being watch and security in not the problem. The hours spent logging back into four to five screens are getting really old and time consuming.
I also have MS vb express that I can use but would rather use excel.
View 9 Replies
View Related
Nov 18, 2009
I have a large workbook with about 20 worksheets that I have used for several years without problems. Recently, I have been getting strange behavior when the screen refreshes. The biggest problem is that I get images of other worksheets covering my current worksheet when I change a cell. I can make them go away by scrolling or flipping between another worksheet and back. I have tried using manual recalc and this has helped somewhat but I can still get the images sometimes. The workbook contains VBA that performs various tasks on every worksheet (unlock, update pivots...) but these have been in there forever. There is also one piece of code that performs advanced filtering. I can only think there was something changed with a security patch, but I wanted to know if anyone else has had this issue and might be able to provide some advice on how to prevent it.
View 9 Replies
View Related
May 16, 2006
i have a worksheet and it has 3 Columns ( Date, Amount, Reason), I am trying to create a list box that lists all of the value's under these columns, i have done that successfully by putting the value range in the Properties RowSource area. When i add information to the list, using a Userform i made before, the listbox does not refresh although the excel worksheet does, it only refreshes if i close and rerun the macro. Do i need to change property values, add VB Code to the script.
View 3 Replies
View Related
Nov 29, 2006
I have a Excel form (No1) with a textbox which shows the date in a cell. I am using a calendar form to change the date in this cell, but that change does not show in the textbox form (No1) unless I close the form (No 1) and reopen it. How can I get the change to show, real time, without closing the form?
View 8 Replies
View Related
Dec 13, 2006
I am having problems refreshing my querytable.. I used the standard code that I found here in Ozgrid, it downloads the version, but when I changed it on the website, it still shows the old number. It does this until I refresh the website (via web browser). I thought the query table went to the site, and downloaded what was there, but I guess not? ...
View 5 Replies
View Related
Jan 21, 2010
I have a pivot table being created from multiple sheets in a workbook. This pivot table is created in the same workbook. Now how do I refresh this Pivot sheet automatically when any of the worksheets are updated. Even manually I am not able to refresh since the Refresh data menu is greyed out.
View 14 Replies
View Related
Mar 25, 2013
I have 2 excel tables that are frequently edited on a laptop and saved to a Dropbox folder. What I would like to do is have a second computer that will have these tables open and constantly updates (periodically or when changes are made). This computer and monitor will serve as a display in a common area (think of it kind of like a flight tracker).
What I have tried: I have tried to enable the Shared Workbook with a refresh of 5 minutes but the display computer is not showing the changes. I would like to make it refresh every minute if a periodic refresh is possible.
Note: It is my understanding that if you reopen the file, the data will update so maybe there is a way to have the file reopen every few minutes? I was able to successfully test this method; If I am able to automate this process without the two excel sheets changing the window positions (using a Workspace file perhaps) it would be perfect.
View 1 Replies
View Related
Mar 1, 2007
My macro is called from a command button on a userform. The userform contains 3 command buttons, a label, and an image box with no attached image. When a command button is pressed, the code sets the background colour of the image box to red and changes the label caption to "Processing..". Then screenupdating is set to false, the code runs, screenupdating is set to true, the image turns green and the label says "Ready!".
The problem is that the userform does not refresh to show the changes until the code is finished running, as though the screen does not update the userform before the "screenupdating" is turned off. Can anyone tell me how to ensure that the forms are displayed correctly before the screenupdating is disabled?
View 4 Replies
View Related
Nov 27, 2008
I've recently got into using excel for more complicated work but still have a less than basic knowledge as I'm attempting to teach myself through trial and error. What I'm currently working on is a simple macro that downloads data from a website (which I've done) but ideally I'd like to have this macro recreate the data roughly every 30 seconds/minute and also have this data recorded next to the existing data rather than on top of it. I realise that you can import data from the web using the standard tools and can set it to reset every 60 seconds but I don't think you can get it to reload the data next to it which is why I was considering a macro.
View 10 Replies
View Related
Apr 21, 2009
I have a invoice that I use, thanks to royUK is working pretty good now. I'm not sure if there is a code I can add too refresh my invoice counter. When I clear the page it moves up one number,now that I have two invoices in the same book it does not refresh the invoice number in the other invoice. royUk worked on this a lot yesterday and had some great ideas in cleaning it up. On my one day invoice he was able too give me a warning when I ran out of cells to input my marcos, but not sure how too do it in my two day because it has two pages. Their are so many helpful people here, I wish I could take them all too dinner.
View 14 Replies
View Related
Sep 22, 2009
I'm looking to create a refresh/update button for my userform. What I have is various textboxes, of which the last textbox contains input from the other textboxes. For example, I have 3 textboxes. The first one contains months, the second contains years, the third pulls from both the first two.
First: August
Second. 2009
Third: August 2009
What I need is if I change the text in either the first or second textbox, I can click the refresh button and it'll update the third box.
View 2 Replies
View Related
Sep 23, 2009
I have a workbook that has many Pictures created with the Camera command. I have found that VBA macros in any workbook open at the same time as the one with Pictures run very slowly. Is there a way of temporarily turning off the Picture Refresh within the VBA in the same way as you can turn off automatic formula updating.
Keith
View 7 Replies
View Related
Feb 17, 2013
I found instructions within this forum to successfully create a macro to start continuous recalcs of my excel spreadsheet to keep now() current.
Sub continuousrefresh()
Application.OnTime Now + TimeValue("00:00:05"), "continuousrefresh"
ThisWorkbook.RefreshAll
Calculate
End Sub
I want to be able to set up another macro to STOP this continuousrefresh macro. I think this should be easy but I am just not getting it.
View 1 Replies
View Related
Mar 21, 2013
Is there a way to refresh a sheet every minute? So not on selection change but automatically every minute
I know this code will refresh but i dont know how to do this every minute (Not a selection change as then this could refresh every second which will not be efficient)
Sheets("Notes").Calculate
View 1 Replies
View Related
