Multiple VBA Calculation Events To Just One Worksheet
Oct 14, 2009
I use VBA's WorkSheet_Calculate event to detect when a cell of particular interest calculates to a specified value. It works well as long as there is only one worksheet (tab). However, when I duplicate that tab (and the final app could have up to 10 copies) to run a different set of data concurrently, I get bad results. The data is online, real time trading data, with each selected stock being tracked in a different tab.
Apparently both (or all) tabs in the workbook react to the same event, whereas I would assume that the event routine in each tab would react only to a calculation in that tab. If this is in fact the case, is there a way to a) make the event routine in each tab respond only to a calculation in that tab, or b) upon a calc event anywhere, determine which tab it occurred in?
Here is the present event code; right now it reads exactly the same in every tab. As long as nothing is happening in any other tab, it does its job correctly.
View 4 Replies
ADVERTISEMENT
Apr 21, 2009
I am trying to have multiple change events work in one worksheet and can not get it to work at all.
The simple setup is, I have a column for each month of the year.
I have several rows of numbers beneath each monthly column.
What I want to do is if I enter a date in say January (my change field), I want it to "ClearContents" out all of the data in the August column.
This works fine currently.
But say the next month I enter a date in February, I want to "ClearContents" in the September column. And so on for each month.
My various attempts at this does not recognize the second change agent, so nothing occurs.
Should I try to "call" each section, or use "Else If", or some other idea?
I've tried attaching the worksheet in case that helps to see what I am attempting.
There are extra "items" on the side and below the table that will be removed if I get this "change event" working. So this worksheet is a rough draft so far.
View 10 Replies
View Related
Jan 23, 2010
I realize that it’s not possible to have on one sheet, multiple Worksheet Change Events, so I tried to come up with an alternative method of handling it, but quickly realized that it’s beyond my capabilities at this time.
I would like to set up a worksheet change event on each of a number of identical sheets. Each Change Event will run exactly the same macro. The exception is the “DeptStr” string value that is relevant to the sheet calling the macro .
Each sheet will be a different department.•If the Target in either range has data added, then data will be added to the cell offset one column to it’s right.
•If the Target in either range has data cleared, then data will be cleared in the cell offset one column to it’s right.
•There can only be one sheet active at a time.
•Only one sheet has data entered at a time.
•The two non-contiguous ranges are of equal length. Ex. (C3:C52) and (E3:E52) and are uniquely named in this case,
but they are not a necessary requirement in order to solve this problem if there is a more practical method.
View 3 Replies
View Related
Oct 9, 2008
I have 2 codes that I'd like to run on the same worksheet, but i don't know how to paste them in together. This may happen many times in the future, so I'd like to know what rules to follow or how to configure 2 codes such as this to get them to work. For example, here's 2 codes I need ran on the same worksheet and I tried pasting one after the other and it won't let me do both.
View 2 Replies
View Related
Dec 13, 2012
I am aware that I can use single changing events in worksheet change events. For instance, if column 1, or A is changed, do something. This is only a single If statement, i.e. either the condition is true, or not. What I am not sure is if I can use two changing events, i.e. two conditions. For e.g. I would like if Column A value is X and Column B is "Active", action it, but only if two conditions are true.
For.e,g. The below syntax does not work. If it is only column A, it does work, but I want both A and B to be true, then copy and paste the target does not anything.
VB:
If Target.Column = 1 Then
If Target.Column = 2 Then
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
If Target.Value = "X" And Target.Value = "Active" Then
View 4 Replies
View Related
Jun 12, 2007
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
View 3 Replies
View Related
Jul 26, 2009
I have a worksheet that utilizes a Worksheet Change Event. I created a macro to copy the sheet and that works fine except for the fact that it doesn't contain the Worksheet Change event.
View 5 Replies
View Related
May 30, 2006
2 Woksheet Change Events? I have a question similar to one that was posted about 2 worksheet change events which I found out is not possible and so I have to merge the codes together and I don't know how. I'm facing a problem in the advanced filter when I remove the last item to be filtered and takes forever to calculate.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$F$2" Or _
Target.Address = "$F$3" Or _
Target.Address = "$F$4" Or _
Target.Address = "$F$5" Or _
Target.Address = "$F$6" Or _
Target.Address = "$F$7" Or _
Target.Address = "$F$8" Or _
Target.Address = "$F$9" Then
Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria"), Unique:=False
Exit Sub
End If
If Target.Column = 3 Then
If Target.Value = "YES" Then........................
View 5 Replies
View Related
Feb 12, 2007
If a cell in some parts of the worksheet (SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo in the code) are selected the input (4 digits) should be converted into time (00:00).
Cells in some other parts (Util,Assis) require 6 digits and should be converted into time (00:00:00).
I had the code working in seperate worksheets but combined (as is shown in code bellow) only part of it works.
Only the input entered into the 4 digit part (SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo) is converted correctly.
The input in the other parts (Util,Assis) is not converted (so when 800 is entered I do not get 00:08:00 but I get the hour equivalent of 8 days (19200:00:00) and logically the message from the EndMacro "You did not enter a valid time".
The cell formats are correct [u]:mm and [u]:mm:ss.
To me it looks like it does not jump to the 6 digits part when need but I can't figure why.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo,Util,Assis")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then..........................
View 9 Replies
View Related
Jul 3, 2009
I have a previously working _chnage event that I am now trying to nest a similar event in. Neither event works now. What am I doing wrong?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim refrange
If Target.Address(0, 0) "C55,G107" Then Exit Sub
refrange = [MATCH(C55,lst_AgentType,0)]
With Sheets("NewInput").Range("d63:r63")
If refrange = 1 Then
.NumberFormat = "#,##0"
ElseIf refrange = 2 Then
.NumberFormat = "#,##0.00"
Else
.NumberFormat = "0.00%"
End If
End With
If Target.Address(0, 0) = "G107" Then
refrange = [MATCH(G107,lstCommRev,0)]
With Sheets("NewInput").Range("E107")
If refrange = 1 Then
.NumberFormat = "#,##0.00"
Else
.NumberFormat = "0.00%"
End If
End With
End If
End Sub
View 9 Replies
View Related
Jun 25, 2007
I have a workbook with 4 sheets "Sheet1","sheet2","sheet3" and "sheet4". There is a macro "execute" in "sheet4". How to use the worksheet events, so that if anything on "sheet1", "sheet2" or "sheet3" changes, the macro "execute" in "sheet4" is excuted.
View 5 Replies
View Related
Oct 20, 2012
I 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?
View 6 Replies
View Related
Jul 8, 2013
In both instances I want to put a date stamp of the column immediately after the defined columns below.
The first issue is that when it runs the 1st change event, it only puts the date stamp in the top row of the column. The change in the "Inportedshapeall" column would almost always be brought about from range being copied from another column, however, I would still like a date stamp in every cell of the column.
The second issue comes about when I try to run the second change event. It simply seems to get stuck. I have tried Else, Else if, removing End ifs. I am not sure how best to define the events in order to ensure it loops through both.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("IMPORTEDSHAPEALL")) Is Nothing Then
With Target(1, 2)
[Code] ......
View 2 Replies
View Related
Dec 20, 2007
I have code the following code that is working great:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C2:C" & Rows.Count)) Is Nothing Then
Application.EnableEvents = False
If IsDate(Target) Then
Target.Offset(0, -1).Value = Month(Target)
ElseIf IsEmpty(Target) Then
Target.Offset(0, -1).ClearContents
End If
Application.EnableEvents = True
End If
End Sub
But I also need to create the same type of code that will copy the cell entry from column E and paste that data into column A along the data from column B that was created from the above code. This data is a sort of key for a vlookup formula on another worksheet. How do I "nest" this second bit of code into the original code?
View 3 Replies
View Related
Jul 14, 2006
If I have a button on each of numerous sheets, how do I get all of the buttons to refer to the same bit of code, without having to copy it to the click event of each?
View 4 Replies
View Related
Feb 2, 2012
I would like to create a calendar within excel that would be linked to a series of recurring events. Those events are happening every 15 days, and expected to occur for a period of up to 15 months (so 30 times in a row). We have about 1000 events, each named with an ID code (a01,a02,a03...) We have a database that contains the id code, the date of first manipulation and the recurring dates after that, this file is updated daily, as some schedule might have been moved 1 day as it is meant to be flexible. The difficulty being that a single day may have up to 50 events, hence 50 ID code.
The database (Sheet DB) is organized as follows first row for the headings, data starting from row 2 till row 1205.
Column A is blank
Column B is for the Department ID (Drop Down Menu).
Column C is for the ID code
Column D for the first date (encoded by coordinator).
Column E and forth (AF) for the due dates (formula based on column B).
Now for some instance we have events up to column CC (schedule is flexible and prone to change).
Due dates are linked and will update themselves automatically if any previous date is modified.
For the result (Sheet Cd), the Coordinator needs to see for a specific date all ID codes due for manipulation.
Cd is arranged as follows:
Column A is blank
Column B is the date range that is from 7 days prior today() (row 2) till today()+30 days (row 39).
Column C to BN is supposed to allow the coordinator to see a whole month schedule, due events of manipulation, and monitor past week schedule (in case of delayed manipulation as I stated a possibility of schedule being moved). If this would work, the coordinator would need not update the summary but merely update the database.
A conditional formatting with the formula $A2=TODAY() as condition in order to highlight the present day (faster to spot) is used.
The formula used in C2 is as follows:
=IFERROR(INDEX(DB!$B$2:$B$459, SMALL(IF(Cd!$B2=DB!D$2:D$459, ROW(DB!D$2:D$459)-MIN(ROW(DB!D$2:D$459))+1, ""), ROW($A$1))),"")
I stopped at row 459 here, but eventually it should go to row1205. Above formula being an array is entered as "command+ return" (using a mac, excel v14.1.4).
I was hoping to have each corresponding ID code returned for a specific date regardless of their position in the table DB. DB is sorted per ID code.
Right now the formula is working, as long as we have NO duplicated in Sheet Cd Column D. Also the results are scattered along the rows, and will require scrolling.
View 1 Replies
View Related
May 2, 2008
Broadly, my workbook contains 5 worksheets. Worksheets 1, 2 and 3 contain calculations, worksheet 4 contains a summary of calculations from worksheets 1, 2 and 3. Worksheet 5 is a data table used for worksheets 1, 2 and 3.
I need the worksheets to calculate in the following order:
1. Worksheets 1 and 2 extract data from worksheet 5.
2. Worksheet 4 captures that data.
3. Worksheet 3 uses the data from worksheet 4 and extracts data from worksheet 5.
4. worksheets 1 and 2 recalculate using the calculation from worksheet 3.
5. Worksheet 4 captures the refreshed data from worksheets 1 and 2.
Is there a way, using formulas in the worksheets, to ignore worksheet 3 on the first iteration of worksheets 1 and 2, and prevent recalculation on the second iteration of worksheet 1 and 2?
In my mind I can see Excel simply recalculating over and over again, or will it stop. Is there actually a "problem" here or am I perceiving something that will not occur?
View 4 Replies
View Related
Dec 22, 2009
I am trying to do is trigger an event when a cell changes, and this cell is updated via a linked drop-down box.
The issue is that the code seems to work fine (in about 10 seconds or less) if a manual calculation is entered into the sheet - but when it's done via the drop-down box, it takes about 3 minutes to complete the macro.
View 3 Replies
View Related
Nov 9, 2006
I have a workbook that has many simple functions moving data around to different sheets and processing some if statements on some of the data. The problem that I am running into is all of the data is initially pulled in from a SQL DB, and I believe that the workbook is calculating many of it's if statements, prior to all of the SQL data being loaded, therefore, many of the worksheet formulas are coming up with the wrong result. Is there a way to delay the calculation of all the basic worksheet functions, so that I can ensure that all the data is in the workbook from SQL, before they all fire. I have attempted to use Tools--Options--Calculation--Manual Calculation, but it appears that even though I have things set that way, all of the formulas in the workbook have already pulled their values through.
View 2 Replies
View Related
Feb 14, 2013
I have a rather large spreadsheet that takes a long time to calculate. One sheet has the data (about 2800 rows by 650 columns), with samples in rows and data for each sample in the columns. A second sheet has functions to group the data according to predefined patterns. That second spreadsheet is about 800 columns wide and as many rows as I need it to be, as the samples don't need to be analyzed all at once.
Ideally I would like to do all samples at once by making the second spreadsheet 2800 rows high, but the calculation time is just way too long. And there's the problem. The calculation time seems to increase exponentially with the number of rows I calculate at a time. I ran a few tests making it different sizes, and here are the results:
rows
time(seconds)
100
3.5
150
12
200
23
[Code]...
I tried both HLOOKUP and INDEX/MATCH functions and it didn't make a lot of difference. These numbers are for INDEX/MATCH.
Graphing these numbers show that it follows an exponential curve pretty closely. What is interesting is that the progress indicator at the bottom of the screen finishes in a few seconds, about 5 seconds for 350 rows, and I can see the new data after those few seconds. But then it sits and appears to do nothing for the rest of the time. So, for 350 rows, it looks to be done with the calculations within 5 seconds, but then it freezes for another 2 minutes with CPU usage maxed out before I can do anything with the program. That actual calculation time seems to scale linearly with the number of rows I calculate, but the time after it finishes calculating before it finishes whatever else it's doing scales exponentially.
The spreadsheet does have a macro and a couple of other sheets and a macro that do some later analysis and I wondered if that had something to do with it. To check that, I created a new spreadsheet and set it up the same as the original one, with the data in one spreadsheet and the INDEX/MATCH formulas in the other. I copied the data over, but setup the formulas new without copying/pasting anything. It didn't make any real difference.
View 2 Replies
View Related
Apr 21, 2009
Is there a way that I can set a single worksheet in a workbook to always be on manual calculation, but keep all other sheet in the workbook set to automatic?
I want to be able to open the workbook, any calculations to perform automatically and then i will select the 'manual' sheet and perform these calculations manually. I also need to these settings to always apply each time i open the workbook.
View 13 Replies
View Related
Jan 29, 2010
I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:
View 3 Replies
View Related
Jun 1, 2008
I have workbooks in which summary tables are generated by lookup formulas. Tables are on different sheets but all use the same lookup value by referring to a cell on the 'master' sheet. The lookup value appears in cells on all sheets, by reference to the master sheet (e.g. [formula] = mastersheet!$B$2).
The master sheet contains the main summary table and is copied and detached for distribution, using a macro. For practical reasons, this is done in two stages, first copying the sheet within the workbook (to make minor alterations), then moving it to a new book to save and distribute.
This is where the problem arises. After the sheet has been detached, we find that if we now change the lookup value on the master sheet in the original file, the tables on that sheet will update normally, but the cells on other sheets remain frozen at the previous value and the tables on those sheets do not update.
The only way round the problem is by Shift + Ctrl + Alt + F9. (Maybe I should add that all my workbooks are always set to automatic calculation.)
The macro itself is not the cause of the problem; if we follow the same procedure manually, the result is the same. However, if we move the sheet in one step, eliminating the intermediate copying stage, the problem does not arise. But this is evading the problem, not solving it, and I would be reluctant to have to resort to this.
The original problem remains as stated, viz. failure of automatic calculation.
View 5 Replies
View Related
Jun 13, 2008
I have wind speeds and elevation data for 200 points. Each variable needs to go into a calculations spreadsheet on another worksheet. Once each variable gets put in the calculations automatically spit out the result. Now I need to use macro to automate this process so that it will repeat the steps I took for every line of data.
In English code terms, I want the Macro to say, take wind speed number and input in wind speed cell in calculations spreadsheet. Then take elevation number and input in calculations spreadsheet. Then take result number from calculations spreadsheet and input in cell. Then loop to go down the 200 lines of data. Is this possoble? Sorry for the non-technical wording.
View 3 Replies
View Related
Dec 30, 2007
This is about the attached sheet where I have a command button and I would like to have the below results.
When the command button "Calculate" is pressed then
Using the amount from col "G" and the reduction from col "H" in the amount in col "A" and the reduction in col "B" for all the lines.
Identfy the lowest value in col "A" -greater than zero- along with the related duration to be placed in the corrosponding columns "I" and "J"
View 9 Replies
View Related
Mar 10, 2014
Attempting to calculate assignment hours based on three fields -
A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)
I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5
My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.
View 2 Replies
View Related
Dec 10, 2013
Here is what I have:
A B C D
Date ReceivedApproved DateDenied DateTurn Time
1/1/14 1/13/14 9
Turntime is calculated with this formula:
=IF(A4<>"",IF(COUNT(B3,C3),NETWORKDAYS(A3,IF(B3<>"",B3,C3)),""),"")
What I need is to not count the first day it is received in the calculation for the Turn Time. So in this instance I need the Turn Time to say 8 instead of 9. If I just subtract a 1 then I get an error if the Approved Date or the Denied Date are not filled in yet.
View 4 Replies
View Related
Jun 27, 2014
I have Excel 2003 and use the following code for 50 consecutive columns, using each column's Row 1 as the multiplier. Each column has over 20,000 rows. It works, but I'd like to know if there's a quicker and / or neater way to do it.
Code:
Range("bo1").Copy
Range("bo2", Range("bo" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Range("bp1").Copy
Range("bp2", Range("bp" & Rows.Count).End(xlUp)).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
View 5 Replies
View Related
Aug 10, 2009
I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA).
{=IF(ROW($B49)-ROW($B$48)>COUNTIF(SAPDATA,$D$4),"",INDEX(DATA,SMALL(IF(SAPDATA=$D$4,ROW(SAPDATA)),ROW($B49)-ROW($B$48)),2))}
All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.
View 9 Replies
View Related
Jun 17, 2009
an event macro to change the font colour of a cell whose value changes as a result of a calculation.
View 9 Replies
View Related
