Run Macro From Event On Second Page
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
ADVERTISEMENT
Jun 29, 2007
I need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
View 3 Replies
View Related
Jun 9, 2009
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
View 3 Replies
View Related
Nov 28, 2006
I need to remove all existing page breaks in a document and add a page break every 72 rows. I've tried some similar codes from this forum with other functions that I don't need in it.
View 7 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
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
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
Apr 1, 2012
I have a macro to print out page 9 but what i whant it to do is to print and fit to full page. how do i do that?
Code:
ExecuteExcel4Macro "PRINT(2,9,9,1,,,,,,,,2,,,TRUE,,FALSE)"
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
View 1 Replies
View Related
Jun 23, 2007
I am inserting a page break with the following macro, however a page break is also inserted when the word " Total Miscellaneous G&A" is found. I am sure there is an easy solution, I am just not very proficient with VBA.
If Not (Application.IsErr(Application. Find("MISCELLANEOUS G&A", UCase( Cells(myCounter, 1))))) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Cells(myCounter - 1, 1)
End If
Next myCounter
View 2 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
May 18, 2006
I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage()
On Error Goto ErrorHandl
Dim strPage As String
Dim webIE As SHDocVw.InternetExplorer
Dim myURL As String
Dim tableBeg As Long
Dim tableEnd As Long
Dim RowBeg As Long
Dim rowEnd As Long
Dim cellBeg As Long
Dim cellEnd As Long
Dim strBeg As Long
Dim strEnd As Long
Dim myCell As Range
Dim rowNum As Integer
With Sheets("INJ")
Set myCell = .Range("A2")
.Range("A:F").Value = vbNullString
rowNum = 2
Set webIE = New SHDocVw.InternetExplorer
myURL = "http://www.sportsline.com/nfl/injuries"
webIE.Navigate myURL
Do Until webIE.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
strPage = webIE.Document.body.innerhtml...................................
View 6 Replies
View Related
Feb 21, 2009
Once a week I need to export data to excel for a report. Some weeks there may be 30 rows and other weeks there may be 100 rows. I created a macro in the attached spreadsheet called 247 formattedV2 that seems to work fine except that it seems to get caught up in the Page Set up area. Here is the things that I have already in the macro that need to be accomplished.
1.All cells should be set to wrap text
2.Bottom justify text on row 1 for wrap text
3.Top justify all rows below it for wrap text
4.Adjust width of columns to exactly what is shown. If text runs over, it should wrap (shown in parenthesis)
5.Center all titles at the top of each column - show with grey background as indicated
6.Center text in columns A, B & C
7.Lighten grey cell in beteen each call (keep them in for all notes, they were accidently removed in this version)
8.Darken grey cell for column headers
Page setup: Set margins to narrow setting at .25 top, bottom, left, right. Set to landscape. Should be 1 page width, as many as necessary for height
Set for 8.5x14 paper (legal size)
Column Widths
•A - Last Call Date (w=10),
•B - Priority (w=7),.............
View 4 Replies
View Related
Oct 19, 2009
I would like to create a macro that will automatically insert a page break based on the contents of the cell.
A1:A4 = has value
A5 = BLANK
A6:A14 = has value
A15 = BLANK
and so on....
I would like to put a page break in A5 and A15....and for those cells that are blanks.
View 11 Replies
View Related
Jun 4, 2012
I have a macro already made to protect all the worksheets upon leaving. One thing I have noticed as I go into these workbooks is it opens on whatever page you left or saved on. I would like it to open up to the first sheet, which is my master.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Delta Crew"
ws.Protect "delta"
[Code] .........
View 5 Replies
View Related
May 20, 2013
I have a very large spreadsheet that is run by a different department. Regularly, I need to go into it do the following:
-filter the sheet by the information in one particular column - - the column is always the same but the number within it by which I filter changes as needed
-double-click each column to autofit the columns
-go to the page break preview to make the document fit one page
-print the results.
My question is can I automate this somehow with a macro or something else?
View 3 Replies
View Related
