="=ROUNDUP("&"50.42"&",0)"
I take this paste text only to get:
=ROUNDUP(50.42,0)
this wont work normally until i open the cell and press enter.
Any way I could do that for more than 1 cell at a time?
How can I refresh a pivot table automatically by changing a cell content (say B2 which contains text, not value) apart from using the refresh button?
Cell B2 is not part of the pivot table.
I'm looking to update a pivot table based on some info in a cell but I keep getting an error message.
View 7 Replies View RelatedI have the following:
One workbook containing 6 worksheets. The first worksheet, let's call it "Alpha", allows a user to enter a name, date, tkt#, issue, etc.
The second third and fourth sheets parse this information out by using formulas and references, no problem there.
The sheet I'm wanting to manipulate is next, "Summary".
Summary has a column that contains "issue counts" which is pulled via formula from the other pages. That part works, with no problem.
What I want to be able to do is to trigger a macro called "Mail_with_outlook" once any cell in the "issue counts" column exceeds a given value (for testing purposes, 8).
The catch seems to be that I want this macro to run when the user enters the information on the "Alpha" sheet, meaning that the "Summary" sheet will not be the active sheet.
I've attempted utilizing the "worksheet_change" and "worksheet_calculate" methods but have apparently wondered down the wrong path without a light it seems... I'd appreciate any assistance.
I have created List box called ListBox10.
Under format control I have put the range of A1:A3.
Cell A1 is text: Order 1
cell A2 is text: Order 2
cell A3 is Text: Order 3
Im trying to trigger different macros from each of the options available in the List Box...here is what I have so far.
Sub ListBox10_Click()
If ListBox10.Text = "Order 1" Then
Application.Run ("Macro1")
If ListBox10.Text = "Order 2" Then
[Code]...
Can seem to get the thing to run getting Block If without end If error.
when I press Delete a value within a Target Range in a sheet, the sheet event is trigered.
how can I press Delete a value within the Target Range without the event being trigered ?
I have several different categories of data, with each list named after the full category title. The data is stored on another worksheet. On the worksheet that will be interacted with, there is a dropdown menu that allows you to choose which category of data you want.
I am trying to get a macro that produces a dropdown menu the next column over of all the items in that category. Here is the code that I have so far.
I have a Multi-Select Active-X Control Listbox and an associated Button. I am trying to create a dynamic Select ALL, Clear All Button. Clear All Function Works, but Select All triggers the Change Event for the Listbox.
figuring out a way to either
1. Disable the Change event until the "Select all" loop is completed or
2. Find a way to write the action for the Button directly, such that the click event is programmed as part of the Change event, and everything is done in one.
Cannot Attach file due to file size, code below:
Option Explicit
Dim i As Long
Dim j As Long
[Code].....
Is there a way to trigger a warning box (ie - must be divisible by 50) when a specific entry is selected from a pull down validation list? The triggering factor is a correct answer to the validation so I do not think the error alert will work in this instance.
View 2 Replies View RelatedI have been working with one sheet (call it tab A) that has 40-50 vlookup formulas that reference another sheet (call it prices) within the same workbook. Everything worked fine. I change my prices tab and everything auto changes in tab A.
Now I copied 'tab A' 22 times creating tabs B - W. I need to slightly modify each tab and based on my prices data, print out the appropriate tab.
Problem is now, when I change my pricing tab, tab A - W do not update. F9 does nothing. Rebooted didn't work. If I go into the formula and hit F2 to edit & return it recalculates to the correct data. But I can't get it to auto update.
I have a worksheet on which i use vba Usedrange to determine all the cells that are being used, but it only picks up all the range after the worksheet has been saved. It seems that the "usedrange" property only holds the actual usedrange at the last time the workbook was saved.
Is this correct?
If so, is there another way to "refresh" the usedrange property without saving the workbook.
If not, is there another way I can determine what the usedrange actually is at any point in time?
I have this workbook to keep track of current and new work for the team - each individual inputs information on their own tab.
In turn, the current information is displayed on a summary page so we can all see who's doing what.
I want to protect that summary page and its formulas from accidental amendment.
However, while the summary page (when unprotected) updates as soon as someone enters new information on their own tab, the summary page won't update when I have it protected.
Is there any way of doing this - preferably without VBA as it's a work situation and the employer doesn't like VBA code running?
I've built a spreadsheet with quasi-animation for a physics class on projectile motion. In it there's a animated chart showing velocity change as a function of distance. The chart has on it an ActiveX textbox that refers to a cell that contains a formula showing "V_y = {velocity}". I've tried a number of things to get the display of the textbox to change during the animation. If I break the code at the line that updates the cell referenced by the textbox the display changes. But I can't figure out how to get the display to update while the code is running.
View 4 Replies View RelatedIm using slicers of a pivot table which has been generated from a sheet containing around 50 rows of data - not a lot of information. I deleted the information, then started re added information. I then refreshed the pivot table which worked but the slicer still contains information that I input a week ago. I deleted the slicer an cretaed a new one but again, it still contains data from a week ago even thought the pivot doesn't. How to make the slicer refresh to show contains of the pivot?
View 1 Replies View RelatedOne of my SUMPRODUCT formulae doesn't seem to be refreshing when i re-open the excel file.
=SUMPRODUCT(--(Data!$B$7:$B$2000="FG"),--(TEXT(Data!$F$7:$F$2000,"mmmm")=A26),Data!$M$7:$M$2000)
If i open the excel all the cells with this similar formula show as 0. If i press Shift+Ctrl+Alt+F9 then they calculate.
I have another SUMPRODUCT formula on another worksheet in the same book that seems to be fine.
=SUMPRODUCT(--(Data!$B$7:$B$2000="FG"),--(Data!$E$7:$E$2000=B7),Data!$M$7:$M$2000)
I can only assume that the reason why it's not calculating automatically is to do with the TEXT "mmmm" reference, is that correct?
Is there anyway i can have it autocalculate so that if i send this as a report i don't have to ask the ppl looking at it to please press Shift+Ctrl+Alt+F9 when they open it :P
Is there a code I can use to update pivot tables e.g every 10 mins?
View 9 Replies View RelatedI have some code that downloads prices for me in excel. However, instead of running this macro everytime I was wondering if it is possible to write a piece of code that would run this macro every x minutes?
View 3 Replies View RelatedI have been working on creating a form in Excel. I have already figured out how to have combo boxes that are dependent on one another. Now I have a question on that. Let's say that my user makes a selection from the first combo box. Clinical Services for example. The second combo box should now show only the values that pertain to Clinical Services (which it does, that part is working fine). So let's say they choose Retail under Clinical Services. However, now let's say that the user changes their mind and they go back and change their selection in the first combo box to Mail instead of clinical programs. The second combo box stays on the selection that they had previously picked (Retail) even though that value does not pertain to their new selection (Mail). How do I refresh this to delete their old selection in the second combo box (Retail)? If they change their mind of the first combo box and select something else the second combo box does display the correct drop down information, it just doesn't clear out the old selection.
View 4 Replies View RelatedI have a bit of code in sheet two of my document that requires user input to produce output. However I have the document set up in a vway that requires the actual input to be placed in a vell in sheet one, and the cell in sheet two is just "=cell in sheet one". This causes the input cell in sheet two to change but the output cell does not produce the new answer.
View 4 Replies View RelatedI have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?
I'm looking for a way to refresh my pivotchart and update my pivot table data every time that a certain macro runs. I could just delete it and make a new one everytime but that runs in to the problem of defining a new sheet every time. Is there a simple way to do this. say I my pivot chart is on "Sheet 7" and it there is only one pivot chart in the workbook and my source sheet is on "Combined Data". and at the moment the data range is "A1:N435" and say next time I run my macro the data range will add 20 rows of data and the next 25.
View 1 Replies View RelatedI have a oval which changes color using the color given in a certain tile, temporary called "Hi" . However the oval doesn't change color automatically with the tile changing, it waits till the user clicks the checkmark for the tile, I'd like it to do it automatic, but it doesn't. Is is possible to animate this script a bit more so it refreshes upon a change in the "Hi" title.... ?
View 14 Replies View RelatedHow do I handle URL connections - such as logging into a web application?
Example 1
When I use .Refresh BackgroundQuery:= False and there is a problem connecting, Excel hangs ... cannot Ctrl-Break ... only option is to kill Excel (and lose changes).
global QT As QueryTable
Set QT = ActiveSheet.QueryTables.Add(Connection:="URL;" & vLoginURL,
[Code]....
Now, if it's still refreshing, it's ok. But if not, it gets stuck on .Refreshing with run time error 424.
Ideally I want to use .Refresh BackgroundQuery:= False as I am calling a number of urls. I just need to provide an "out" when the url fails and hangs on .Refresh BackgroundQuery:= False
I have a pivot table that I refresh using .refresh in the VBA code.
My problem is that when I loop through all the PivotItems in a filter list in order to make all visible, there are PivotItem names that aren't in the drop down menu and so the code breaks.
Do I need to refresh the pivot table slightly differently than just using .refresh?
I have got this in a pivot table
Customer Total sales(USD)
30010 500.00
30041 2000.00
In another column I enter a comment for customer 30010(and leave the comment column for customer 30041 blank) as below
Data 1.xls
Customer Total sales(USD) Comment
30010 500.00 Test 1
30041 2000.00
Later on the content of the file changes as below:
Customer Total sales(USD)
30025 1500.00
30010 500.00
30041 2000.00
When the file is refreshed I would like to have comment as per the former file i.e the comment “Test 1” should appear against customer 30010 and similarly for any other customer that I enter the comment.It should be as below:
Customer Total sales(USD) Comment
30025 1500.00
30010 500.00 Test 1
Customer Total sales(USD) Comment
30041 2000.00
I've referenced a few threads this one in particular [Solved] Pivot Tables : Refresh on protected sheet
I've tried the example given in the link and it still does not work. I want to protect the sheets that contain pivot tables, but still be able to refresh them. When I try I get an error. I've tried this a few ways.
My first attempt was to protect the sheets using allowuserinterfaceonly at a workbook open event. This caused warnings as soon as the workbook was opened. Then I tried Dave's suggestion and I get a runtime error 1004: Cannot edit PivotTable on protected sheet.
I've got a workbook with a several pivot tables. For convenience, I've deleted the pivot table source and left the pivots "self-standing". Problem is now when I try to refresh the workbook, I get a "Reference is not valid" error which prevents the workbook from updating.
View 2 Replies View RelatedIts been ages since I used a pivot table and I cant remember much.
I did one last week - lets pretend on the range A1, B100.
Ive since added some extra info into the range and it now goes from A1, B110.
I go to my pivot table and hit "!" and it updates. How can I be certain its included everything from my range? Is there a way I can check the pivot table to see where the range is from?
I would like to refresh all external links to other xl files (on a network drive) and break all links
I have a code like this:
Code:
For Each l In ActiveWorkbook.LinkSources
Workbooks.Open Filename:=l, updatelinks:=1, ReadOnly:=True
ActiveWorkbook.Close False
Next l
For Each l In ActiveWorkbook.LinkSources
ActiveWorkbook.BreakLink l, xlLinkTypeExcelLinks
Next l
It seems to be working, but if i go step by step (f8) it updates values but sometimes after closing the source workbook and updating next one, the previously updated values disappear...
I cannot open all files because there are many of them and they are like 40mb each.
There are no defined names in the file, except print_area
I have a worksheet with an auto filter to display only non-blank rows. Each row is a ledger account, with a cost. The formula in each cell sums the total of its corresponding gl in another worksheet, but also takes into account the region I am asking it to search based upon another cell. So the cost in a specific can fluctuate based upon which region I am telling the rows to lookup. So it can be blank with one region and populated with another. When I change the cell that tells the row data to look up a new region, the auto-filter does not automatically update the list of non-blank cells based upon the new region being searched, but rather the old one. So what is happening is I am left with an incorrectly auto-filtered result.
Is there a way to get the auto filter list to automatically update for the refreshed list of rows?