I have a program which iterates on command. A plot on the spreadsheet is designed to show the progress of the iteration. However, the plot does not update while VBA is running. What is the command or technique to refresh the plot at an intermediate stage in the calculation?
View 1 Replies
I am using excel2000 on WindowsXP. I have a 2D surface plot pointing to data that can be continuously updated using a scrollbar from the Forms toolbox. For some reason, when I place the plot over the continuously changing cells, the plot does partially update when I scroll, and then fully refreshes once I let go of the scrollbar handle. If I move the plot far from the changing cells, it doesn't refresh at all until I let go of the scrollbar, so I'm forced to use it to page, instead of smoothly scroll. How can I get the whole surface to refresh continuously? Seems some kind of magic "event" logic is at work here....
View 9 Replies View RelatedI 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.
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.
I have created a macro to insert a template worksheet (qm.xlt) into a workbook (wb.xls).
Sub Test_InsertXlt()
Sheets.Add Type:="qm.xlt", After:= ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.count)
End Sub
The template has cells which contain formulas which lookup values in another sheet in the workbook (configData).
For example: template cell F20 has the formula
=VLOOKUP($C20,ConfigData,8,0)
Once the template worksheet is inserted into the workbook, its cell values remain "#Name", which corresponds to a "Error 2029" in the code.
If I manually edit the cell (F2) and then hit 'Enter', the cell value is updated correctly. My question is :how can I do this via code/macro?
I am trying to write a creative procedure that on workbook open will check the name of the worksheet if it includes the day's date in sheetname & if it doesn't rename the sheet & delete current region from A1, else exit the sub.
Then runs a query on an Access DB to place in the above worksheet.
I need this due to novice XL & DB (8)users.
This is what I have at the moment on a test DB courtesy of J Walkenbach's sample files .....
I have a Workbook with multiple PivotTables which are feed from an external data cube. I need to update all of this Pivotables to the same date through VBA. The date will be a known variable and it corresponds to the "Periodo" field. I had thought of doing this by having the date stored in a cell of maybe with a calendar pop-up
View 8 Replies View RelatedI am bringing data in from an ODBC database, I am using ActiveSheet.QueryTables(1) and then .refresh.
I want to run a sub after the data has been brought in which finds the last row and adds a formula.
The problem I have is that the data brought in is not show on the screen until all of the macros are finished.
Is there a way of forcing the screen to update?, I have tried the recalculate and refreshall commands or is there another method I should be using?
Sub autofill()
Dim myobj As Object
If ActiveSheet.QueryTables.Count > 0 Then
Call clearscreen
Application.DisplayAlerts = False
Set myobj = ActiveSheet.QueryTables(1)
I require the data to be on the screen before I can find the last row used which is done in "addmaths"?
I have to append the cell value with a user input string in to the same cell.
i.e, if the user types "Issue" in the cells ranging C1:C200, I would like to make a mandatory issue description to be entered by the user. So a user form with text box and command button was introduced which should append the cell value with the user input in the text box.
The issue that I have is with the cell selection within the code for the form.
ActiveCell.FormulaR1C1 = "Issue (" + TextBox1.Text + ")"
For example if the user tabs out from cell C3, the output from the userform will be written to D3 ? or if the user hits Enter key to come out, the output will be written to C4 and so on.
How can I make ure the output is going to the same cell?
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Application.Intersect(Target, Range("$C$1:$C$200")) = "Issue" Then
Dim MyForm As New UserForm1
MyForm.Show
Else
Exit Sub
End If
End Sub
'Code for the userform is below:
Private Sub CommandButton1_Click()
If TextBox1.Text = "" Then
' the user input is mandatory
MsgBox "Issue Details is mandatory", vbCritical, "Mandatory Field"
TextBox1.SetFocus
Else
' append the cell value with user input in the text box
'****issue is here in the next line
ActiveCell.FormulaR1C1 = "Issue (" + TextBox1.Text + ")"
Hide
End If
End Sub
I inherited a spreadsheet to manage that is linked to a SharePoint table.
It is trying to populate a date that a certain "Tier" is selected (1, 2, 3, or 4).
It works great if I manually type in the tiers, but does not run on existing data (about 400 records) or lines that are updated and new via the SharePoint list.
How can I have this run on all of the existing lines and anything added or changed in the future from the list?
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AD2:AD10000")) Is Nothing Then
Application.EnableEvents = False
[Code] ....
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.)
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.
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 RelatedWill someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies View RelatedI have used the function = now() to have the most updated time but it updates a workbook when I open it in the first place. How I can avoid this?
View 9 Replies View RelatedLet's say that in column A I have numbers,"Yes" and "No". I want in column B to have only the numbers from column A, in the same order without any empty ranges, and everytime I add in column A a new number, column B to update automatically with that number. Let's have an example:
A B
Yes 12
12 13
No 10
13
No
10
Yes
And if I want to add in column A:
A B
Yes 12
12 13
No 10
13 25
No 15
10
Yes
25
15
So the column be will update automatically. I already tried =IFERROR(INDEX($A$1:$A$10,SMALL(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)),ROWS(B$1:B1))-ROW($A$1)+1),") but using this many times get's my file very heavy and the excel is working slow.
I have some VB code which sequentially opens over 200 workbooks to extract data from each and populate another workbook. These workbooks do have links to other workbooks in them.
For some reason when some of these workbooks are opened I get a requestor window asking whether I want to Update or Don't Update the data. I always want to Update the workbook and believe this can be done in VB by hiding the requestor?
I have a vlookup to another workbook. It works fine if both workbooks are open. But if both are not open and I open the workbook with the links and click Update, #VALUE! returns. I have attached the two files. I don't think it is my formula, but here it is anyway. =IF( COUNTIF([Tempozgrid.xls]June!$A$52:$A$83,A3),VLOOKUP(A3,[Tempozgrid.xls]June!$A$52:$L$82,12,FALSE),0)
View 2 Replies View RelatedIn the attached sheet I have data in column F and G which I am plotting using the following
Sub evmplot()
Dim i As Integer
ThisWorkbook.Worksheets("sheet1").Activate
ActiveSheet.ChartObjects("EVM").Activate
ActiveChart.ChartArea.Select
For i = 1 To 15
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(i).XValues = "=sheet1!R[" & 5 + (i - 1) * 13 & "]C6:R[" & 17 + (i - 1) * 13 & "]C6"
ActiveChart.SeriesCollection(i).Values = "=sheet1!R[" & 5 + (i - 1) * 13 & "]C7:R[" & 17 + (i - 1) * 13 & "]C7"
Next i
End Sub
when I =1 , the sereis should be from R5c6:R17C6. But the first series in the plot is from R6C6:R18c6. Same is the case with other series also.
This may sound like a dumb question, but hang with me. I have two series in an xy plot. One is a line (withOUT markers) and one series is just markers (no line).
The line without markers is created in a way such that it forms a box and whiskers plot. The other series, are trial values that go into the box and whiskers.
I have desperately tried to get the second series (just points) to plot BEHIND the first series (line, no markers). Changing the series order does not work unless both series have lines. I have tried this in a variety of ways (including writing a macro which draws the markers first, and then the lines), but that doesn't work either!
How to plot a step graph?
I have attached a drawing by illustrating the graph that I wish to plot.
Attached Image : jpg Capture.JPG
I am planning on creating a map of a badminton court. I am hoping to record each hit of the shuttlecock with a dot on the map. Of course I want this to be inputted by data rather than manually inserting a circle shape and placing it in the correct location each time.
is there any way to do this in excel?
I have a pareto for a manufacturing facility that has a list of nonconformists and their occurrences in columns K and L. My issue is since it is a running total for the year at certain times the values are 0 for the number of occurrences. This calculates fine, but the graph is very large and the failures are all located on one side instead of being evenly distributed. I am trying to get it to only plot for what has occurred and not have a bunch of blank values. To make it a little more complex I'm trying to avoid macros.. I can get them to show up blank just can't get the bar graph to resize itself to fill the chart.
View 3 Replies View RelatedI would like to make a histogram using excel. I have successfully made the bins and frequency functions. Once I want to add the x values, I get an error. There are many examples online, but I can't get it to work.
[Code] .....
I have data that starts from say, 17:50 hours. However, I would like the chart to start from 00:00 to 00:00 so it is a full 24-hour period. So up to 00:00 to 17:50 hours, it would be blank then the data will be plotted from 17:50. How do I do this? If I select the existing data from 17:50 and ask Excel to create a chart, the x axis starts from 17:50 (not 00:00).
View 4 Replies View RelatedOn Histogram, is it possible to plot Bin averages?
In my data there are two columns, 1) File Size 2) Months and then I have created Bin(months) like 0,6,12,18,24
I want to plot average file size in those Bins. For example I would like to plot what was the average file size in past 6 months, past 6-12 months so on.
I have a column, call it column1, with thousands of numbers in it.
What I would like to do is get the min of this range, get the max of this range, and plot in a new column all numbers from the min to the max.
For Example
Column1
1
4
9
3
4
4
5
9
2
2
1 is the min, and 9 is the max
I want a new column, say columnNEW
1
2
3
4
5
6
7
8
9
Any clever way of doing this in VBA?
I have an Excel file with a sheet that has the following columns: Name, Activity, Start Date,
End Date. What I would like to do: I would like to create summary sheet with a calendar-like
matrix, with the names as rows, and dates as columns. I would then like to plot the activity in
the corresponding dates (based on the start and end date). Note: If there are schedule
conflicts, the conflict should show up in another row
- Is this even possible to do in Excel?
Source:
Name Activity StartDate EndDate
PersonA Vacation Jan1 Jan3
PersonA Office Jan4 Jan5
PersonA Field Jan6 Jan7
PersonB Vacation Jan1 Jan3
PersonB Field Jan4 Jan7
PersonB Office Jan5 Jan5
Summary:
Name Jan1 Jan2 Jan3 Jan4 Jan5 Jan6 Jan7
PersonA Vacation Vacation Vacation Office Office Field Field
PersonB Vacation Vacation Vacation Field Field Field Field
PersonB Office
on a macro or vb code that can search a date and shows everything related to the date? My problem is when i select a certain week for example first week of july. I need a macro of VB code that can find all the dates (or days) in an array of dates and data, which are arranged randomly, that falls in that week and copy them to another worksheet. I have attached the file with this message.
View 9 Replies View RelatedI have some weekly data that I need to produce a graph from (100% Stacked Column). Each time I create the graph using the dates as the x axis i only get a thin line which looks like it represents one day, the other space looks like it is for the other 6 days of the week. I know that you could add an apostrophe to the beginning of the date but this is to be an automated report.
View 9 Replies View Related
