Run Event Macro On Two Worksheets
Oct 16, 2006
The macro below works great as a conditional format routine on the Input sheet of the workbook, however I would like it to run on the Data sheet as well, but with a different range (E3:CE117). Can this be done?
Private Sub Worksheet_Calculate()
Dim dcell As Range
Dim icolour As Integer
For Each dcell In Range("c15:g27")
Select Case dcell.Value
Case "GA"
icolor = 43
Case "GAC"
icolor = 4
Case "LA"
icolor = 45.....................
View 7 Replies
ADVERTISEMENT
Jun 26, 2007
What is the method to trigger an event on a command button (placed on a worksheet - not in a form) when the return key is pressed? I.e. instead of moving to the next cell clicks the button?
View 9 Replies
View Related
Aug 29, 2006
I am trying to make a macro run automatically based on info entered in a cell. The problem is I need this code on at least three separate sheets in the work book. It doesent work when I try to use the same basic code. The code I am using is below.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lLastRow As Long
Dim rgArea As Range, rgCell As Range
Dim COL_B As Integer
COL_B = 2
Dim COL_X As Integer
COL_X = 24
Dim ROW_FIRST As Long
ROW_FIRST = 5
' Find the last row of date in the "Approved" worksheet
lLastRow = FindLastRow(SZ_WS_APPROVED, COL_B)
' Set a reference to the changed cells in column X
Set Target = Intersect(Target, Range(Cells(ROW_FIRST, COL_X), Cells(lLastRow, COL_X))).........................
View 5 Replies
View Related
Nov 21, 2011
I have a workbook with 33 worksheets (31 date tabs and a Month To Date and Year To Date tab)
I want to have a clickable cell on each of the 1-31 date tabs to point to the MTD
I know I could do this easily with a hyperlink but I would rather have a clickable cell.
I have achieved this on an individual worksheet using the following code
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("$R$3")) Is Nothing Then Sheets("MTD").Select
End Sub
My question is: Do I have to copy this to every single worksheets code or is there some way have all sheets read the same private sub? (I guess it negates the point of a "private sub")
Way to do this as it would make editing any changes easier than having to do it 31 times.
View 3 Replies
View Related
Mar 27, 2009
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies
View Related
Jul 8, 2009
I need a before save event macro in excel that will check if sheet1 cell ref A25 = zero. If it isnt zero I want a msgbox to say "Does Not Equal Zero" to pop up, if it is zero then I do not want a msgbox displayed. In both instances I still want the file to be saved.
View 5 Replies
View Related
Jun 20, 2009
I have a situation where I have a macro that sorts on sheet1. I would like it to run when a cell changes on sheet2 in E9:N21. Since the cells on sheet1 are linked to data on sheet2, I cannot trigger the macro from sheet1 due to the cells being formulas instead of values.
Here is what I have tried and it is not working. I must be doing something wrong with the syntax.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range(Sheets("Games")("E9:N21"))) Is Nothing Then
Exit Sub
Else
ActiveWindow.SmallScroll Down:=33
Rows("6:55").Select
Range("A55").Activate
Selection.Sort Key1:=Range("A55"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A3").Select
End If
End Sub
View 9 Replies
View Related
Nov 21, 2013
i currently have a workbbok that i have a very simple worksheet event macro that will filter a seperate sheets contents to the corresponding entry number. is there any way to make a dynamic event macro that will filter my second sheet depending on what cell is selceted on the first.
for example record 1 on sheet 1. if cell L1 is selected it will filter sheet 2 to to record 1, record 2 on sheet 1, if cell L2 is selected it will filter sheet 2 record 2 and so on. this list of records will continue to grow and i cant think of any way other than what i currently have, which is to make each filter macro for every cell.
is ther a better way example of CODE HERE
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Address = "$L$5" Then
Call Filter1
[Code]....
View 2 Replies
View Related
Aug 30, 2009
I need my macro to run when the spreadsheet is Saved. Is that process written in the macro code or is there an existing event control in Excel to accomplish this?
Running Excel 2007. I tried searching the forum & found one post that ran the macro on open, but couldn't set that up to work on save.
View 2 Replies
View Related
Nov 9, 2011
with an event macro. Basically I have a workbook with 11 worksheets and have created a macro that increases the value of cell J3 by 1 in Sheet1. However at present it only works from Sheet5, but I now need it to run from Sheet6 and Sheet11 as well and not from any other worksheets. The code I have is as follows
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.CodeName = "Sheet5" Then
With Sheet1
.Range("J3").Value = .Range("J3").Value + 1
End With
End If
End Sub
View 1 Replies
View Related
Jun 28, 2008
Does anyone no a way to trigger a macro by just using the enter key in a difined rage ("A1:A100") Not worksheetchange
View 9 Replies
View Related
May 23, 2009
I have a worksheet called "Roster" with a list of names from D7:D17 each person on the list has a separate sheet that is called by their name. the sheets are not linked up. the names were just manually typed into the list and into each sheet name.
If any name is changed on the list on "Roster!D7:D17" i would like the sheet that has that name to change as well.
This is the code that i was sent, but i do not know enough about VBA code to understand exactly what is happening, thus i cannot diagnose the problem. I placed the following code into the page "Roster" (by right-clicking and then pasting the code) then i closed, but nothing happens. I can mess with the code and get an error message. but that is about it. error messages usually say something about the debugger, or runtime error or something.
there is the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal(0) = OldVal(1)
OldVal(1) = Target(1).Value
End Sub
View 9 Replies
View Related
Oct 9, 2009
how to make a code so that it captures the event of pressing any key of the keyboard and triggers a macro.
I tried using OnKey method but the problem with this is I am getting stick to any particular button whichever i specify in the onkey method wheareas I want to keep it general for any button in the keyboard.
Also if anyone can suggest how can I use OnKey method to use the "S" key to launch a macro.
View 9 Replies
View Related
Nov 13, 2008
I'm using a macro that picks up on changes in a active cell.. This is the code
HTML Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Value <> "" Then
ThisWorkbook.Sheets("Log").Cells(Target.Row, 2).Formula = "=Today()"
End If
End If
End Sub
There's a problem with this code, when select from a range and hit delete, i get a error mismatch. I want to have the macro ignore when i select active cell range of greater then 1.
View 2 Replies
View Related
Mar 31, 2009
I have not used an event macro before and am trying to one update a cell when changing a cell. I am basically copying a number to another cell that is an input for a calculation and then returning the calculated value back. How do I reference r69 in the code to start the event macro?
View 2 Replies
View Related
Jul 31, 2008
In Col D of my spreadsheet, I have a list of security codes, in this list there is a security code "all", i need a macro that will add 1 to the code, so it reads "all1", now i need the macro to run as soon as new data is pasted to sheet "Lending", the ranges in Col D do change on a daily basis therefore cell reference for security codes is not fixed.
Can this be achieved? ............
View 9 Replies
View Related
Nov 23, 2009
I'd like to know if there is a change event that only occurs when a target cell is changed by the user, but does not occur when i'ts changed by a macro. nfortunately, the Worksheet_Change event occurs in both cases.
View 2 Replies
View Related
Nov 11, 2006
I have a real simple question about what command to enter in order to stop a macro. All the macro does is "press the F9 key" every second. I use a button to start the loop but I am unable to find a way to use another button to stop the process. Ive copied my macro below:
Sub Go()
Calculate
Application .OnTime Now + TimeValue("00:00:01"), "Go"
End Sub
Private Sub CommandButton1_Click() "Go Button"
Application.OnTime Now + TimeValue("00:00:01"), "Go"
End Sub
Private Sub CommandButton2_Click() "Stop Button"
'????????????????????????
End Sub
View 2 Replies
View Related
Jan 26, 2007
One of the things it does throughout the course of the run is unprotect the worksheet, do what it needs to and then reprotects the sheet (using a password). On the second run, the sheet protection input box is shown and I am required to input the password before the code will execute.
The code that I have written uses an Add-in that pulls data from a database. As part of that retrieval, the other add-in protects the sheet without a password. Would that be causing my error?
View 6 Replies
View Related
Mar 21, 2007
I have looked at a series of Change Event topics and code but can't see what I need. I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes. I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...
View 4 Replies
View Related
Aug 7, 2006
I have the following code, which works perfectly:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TotalDays As Integer
TotalDays = Range("C65536").End(xlUp).Row + 1
The code points to the next blank cell so the user can input a value. Each time the user enters a value I want to re-run the code so that the colour of the cell changes.
However I also want to perform various calculations on the sheet. However this means the sheet is being changed and so continually repeats my code.
How do I add the following, to my previous code?
Range("E8").Value = Cells(7, 6) * 2.5
View 9 Replies
View Related
Feb 20, 2009
how I would create an event macro that will synchronise the zoom % of 2 sheets?
E.g. if I change the zoom on sheet1 to 80% then sheet2 automatically change to the same? And the same the other way round...?
View 9 Replies
View Related
May 23, 2008
I have a sales spreadsheet, that people on this forum have very kindly helped me with by giving me two macros; one to remind users that they need to update the month cell when an order comes in, and the other to automatically put the date in a cell when any cell in that row is changed.
The final thread is here: Message To Remind That Cell Is Mandatory
and the Macro used is:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
For Each c In Target
If c.Column = 11 Then
If c.Value = "100 - Purchase Order In" Then
MsgBox "Is the Month In correct?"
End If
End If
If c.Column > 1 And c.Column < 18 Then
Cells(c.Row, 1) = Now
End If
Next c
Application.EnableEvents = True
End Sub
However...
We've now found that we cannot undo anything in these spreadsheets. If, for example, a cell is incorrectly copied or deleted, the only way of undoing the change is to shut down the spreadsheet without saving!
Is this just a by-product of using the time macro (a search on other threads suggests that it might be), and, if so, is there any way of changing it?
View 9 Replies
View Related
Jan 5, 2010
Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.
View 2 Replies
View Related
Jun 6, 2006
I have a code and I want to run this macro whan a specific cell change (which has sum formula) and this code also has some calculation. And I m not understanding to overcome this problem through Calculate event.
View 2 Replies
View Related
Aug 5, 2014
Looking for the syntax that will allow me to code an event sub routine, based off the event of a specific function e.g. findnum being run.
View 14 Replies
View Related
Oct 18, 2006
I have a "submit" button macro which user would click after he has finished his input. This macro would update a reference number on the worksheet named "orange" and then print out this worksheet.
My purpose is "orange" is printed out with a reference number.
Below is the 1st code.....
View 9 Replies
View Related
Dec 4, 2008
I have created a worksheet to track/calculate compliance with a number of important items at a number of business locations on a number of questions. This workbook will be posted on a central server that will allow 25+ employees to access it from the field and "double-click" to enter an X in either a "Yes" or "No" column for about 100+ locations (the code also will remove the "X" in either column if another "X" is entered into the other column to prevent double entries). The workbook will be quite elaborate and will be locked down except for those cells unprotected for data entry (identifying location, date, etc.).
Below is the code and it works just fine as written with two columns to chose from to "double-click" and enter an "X" (columns C & D for rows 4 thru 15). I want to "freeze frames" locking columns A & B and allow scrolling to the right to enter each New Location and allow the same code to apply to the same rows but column groupings of E & F, G & H, I & J, ...until I have about 100+ sets of two columns identified in the code.
I have tried a variety of changes to the "Union(Range" and "Intersect(Target, Range" last night and this morning to no avail and have gotten nothing but a headache and a desire to drink heavily from the exercise.
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range
Dim rCell As Range
Set rInt = Intersect(Target, Range("C4:D15"))
If Not rInt Is Nothing Then
For Each rCell In rInt
rCell.Value = "X"
Next
End If
Set rInt = Nothing
Set rCell = Nothing
Cancel = True
View 7 Replies
View Related
Apr 4, 2014
I have a work book with 6 worksheets, sheet2 is called Maersk, sheet3 Mariana, and so on ....
I need a macro so when I type Mariana in sheet1 g20 and hit enter it go's to Mariana sheet .....
View 5 Replies
View Related
May 2, 2014
I am trying to write a macro that takes the value in cell B2 from a workbook called ""numbers" in a worksheet called "summary," and creates a new worksheet in a workbook called "filestransfer" that is named the same thing as the value in cell B2 from "numbers." That part is done.
View 2 Replies
View Related
