Capture Print Event To Run Some Code
Jul 21, 2014Is there a way of capturing the print event to run some code?
I.e. The 'Print' button is clicked and then code executed in the sheet.
ADVERTISEMENT
Capture Cell Value Before Change Event
Mar 3, 2009I am trying to capture the value of a cell before a change even. The attached Macro will report the value before the change but does not store it so that I can use it in another module. What I am trying to do is capture the value before the change and then look that value up in another worksheet (in the same workbook) so I can make the same change in the second workbook.
The values will always be in Column B and will always be string characters.
The code I am using for the change event is as follows: ....
Capture Sheet Delete Event
Jan 23, 2007I wonder how to capture de event when one choose to delete a sheet. Similar to vbyes or vbno?? Now it inmediatley goes to my sheet_deactivate_event, but then I don't know if it is beiing deleted? I mean capture if you do righclick sheettab
View 2 Replies View RelatedCapture Print Zoom Value Before Printing
Apr 19, 2008In a previous, expired thread, the following code was suggested as a way to capture the page-setup zoom value after setting PagesTall and PagesWide. It works fine when single-stepping in the VB Editor and when run directly by the user, via a button click or Tools=>Macros...=>Run, but fails when run under a Worksheet_Activate() event call.
Does anyone understand why that is and/or have a fix or workaround?
Sub X()
'
Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{1,#N/A})"
Application.ExecuteExcel4Macro "PAGE.SETUP(,,,,,,,,,,,,{#N/A,#N/A})"
MsgBox "Zoom factor is " & ActiveSheet.PageSetup.Zoom
End Sub
Stop Print Macro Firing Before Print Event
Oct 18, 2006I 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.....
2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others
Jan 27, 2009I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies View RelatedVB Code To Capture Pathname And Filename
Nov 9, 2011I want to have a macro (because i do so often) that for any active workbook that I'm using will....
capture the full pathname and file name and place into clipboard for pasting elsewhere.
eg run macro and the following is added to clipboard.....C:Test DirectoryTest_File.xls
Adjustment To VB Code To Capture A Range
Aug 22, 2007The code below puts a green border around the cell that is beneath 10 in my chosen range, however I wish to add the border to the row of information instead of just the cell. My columns of data are from columns E to M, but the criteria for whether or not the data gets a green border is in column D....so lets say D15 is less than 10, I would want a border to go around E15:M15.
Sub Test()
For Each c In Range("D2:D350")
If c < 10 Then
c.select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
Before Print Event Not Responding
Jan 9, 2007I have a Word document that I'd like to print first, when I click Print on a specific worksheet (not on Print of other worksheets in the workbook, just the one). The worksheet contains customer information and is essentially a cover page for the Word document. the Word document already prints in reverse order, as does the worksheet, so I just need the Word doc to print prior to the worksheet/cover, to give me the total print package in the correct order. I've tried using the following code in the Sheet object for the worksheet in question;
Private Sub Worksheet_BeforePrintEvent()
Dim appWd As Word.Application
Set appWd = CreateObject("Word.Application")
appWd.Visible = True 'Change to False when code is good
'Open Doc
appWd.Documents.Open Filename:="S:BryanQuoting ToolsP'Binder Leading Pages.doc"
'Print Doc
appWd.ActiveDocument.PrintPreview 'Change to PrintOut when code is good
'Close Doc
Set appWd = Nothing
End Sub
Print Event Macro To Run From 3 Sheets Only
Nov 9, 2011with 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
Before Print Event That Shows A Userform
Aug 28, 2008I have a before print event that shows a userform - the user can then select which worksheets they want printed and select a command button. I then have a loop set up that loops through the selected workbooks and prints them. The problem is I am not sure how to tell VBA to print without it going back to the Before Print event userform.
View 9 Replies View RelatedExcel 2013 :: VBA - Trigger Event On Print Screen
Jun 27, 2014I have a project report, which is user-configurable, allowing the user to customise the data that is displayed based on a number of parameters.
To ensure the report always prints neatly (the number of visible columns and rows varies, depending on the above parameters), so I have a macro that dynamically sets the print area of the worksheet.
At present, I call the macro from the workbook.beforeprint event, which works fine in terms of printing. Ideally however, I would like the macro to be called when CTRL+P is first pressed - i.e. the resulting 'print screen', containing a preview of the print out should reflect what will subsequently be printed, when the 'Print' button is then pressed.
I am not sure if this makes sense (Excel 2010 and 2013 are different from previous versions).
Dynamic Print Ranges & Worksheet BeforePrint Event
Jan 27, 2008I have a dynamic print range defined as follows:-
=OFFSET(Sheet1!$A$1,0,0,MATCH("PRINT_AREA_ROWS",Sheet1!$A:$A,0),MATCH("PRINT_AREA_COLUMNS",Sheet1!$1:$1,0)), lets say this equates to range("A1:D12")
I also have a workbook beforeprint event defined as follows:-
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "Pete"
End Sub
My problem is that after the Print Event has fired , the dynamic range has been converted into a static one i.e range("A1:D12"). I have tried this on all version of Excel from 97 to 2007 and it behaves the same so premably this must be by design rather than a bug?
Am I doing something wrong? What is the work around? I'd rather not have to create the dynamic range within the print event procedure unless I have to as my workbook has many, many tabs!
Vb Code To Set Print Area And Print Out
Aug 24, 2007with creating vb code that will select certain rows in a spreadsheet, set the print area to those rows and print them out, and then reset the print area back to default.
The data is all on one sheet, but is split into 5 different areas, each below the next, with the column headers at the top of each.
e.g.
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
and so on. At the press of a button it'll set the print area to the first section and print (the section includes the headers as well as the data). Then at the press of another button it'll set the print area to the next section. There could be any amount of rows of data in each section so it needs to cope with that.
Deleting Event Code With Code
Sep 2, 2006Is is possible to use VBA to remove/delete a macro and also remove code like this on worksheets:
Private Sub Worksheet_Change
End Sub
Private Sub Worksheet_Activate()
End Sub
VBA Code To Add Event To New Worksheet
Feb 27, 2008I need to to use VBA to copy a worksheet (which i've managed to do!), but I need the new worksheet to have a Worksheet_Change event. Now when I copy the worksheet, the event doesn't copy over (obviously as its a cut and paste jobby). Any ideas on what code I need to add in the Worksheet_Change event just after the new worksheet is automatically created?
View 14 Replies View RelatedVBA For Worksheet Event Code
Jan 17, 2007I have seen many examples posted here that are close to what I need, but I am not experienced at writing code, so I am not sure how to make the changes to this code that apply to what I need. So I will try to explain what I am looking for & hopefully not be too long winded:
I have an excel workbook that has several worksheets within it - 10 of the worksheets are identical as far as the formulas that are in each of the cells, however, they are all VLOOKUP cells that refer to another excel workbook (used as a "database")which lists all of our projects - there is a cell in each of the 10 sheets that can be changed that will allow that particular worksheet to access the information in the "database worksheet" for the particular project name that is entered in this cell.
There is currently an event worksheet code for each of these worksheets which allows for a picture to be displayed "floating" above cell (L13) based on the project name that is showing in this cell- however it is based on all of the project pictures "living" in each worksheet (the picture that is called up by the project name is displayed while the rest are hidden - as per the formula)- the code is shown below:
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("L13")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
However - this option works fine when there are 5 or 10 pictures/projects - but we are looking to grow our project database. So, I was hoping to be able to store the pictures in another location (such as another worksheet or in a file on the server - I would also appreciate input if anyone has an opinion on which would work better?) and have some type of worksheet event code that can be written in to each worksheet that would access the picture in this "central" location and have them appear in cell (L13) of each worksheet based on the project name displayed.
VBA Code Triggered By An Event
Jan 25, 2008I have 2 worksheets ('pathways', 'pathway events').
'pathways' has unique rows with a unique ID i.e. [Pathway ID] whilst 'pathway events' has the same initial column [Pathway ID] but with multiple values of the same [Pathway ID] value.
If an [Pathway ID] value is selected in 'pathways', I want it to trigger some code which will open a new worksheet and copy the multiple rows in 'pathway events' with the same [Pathway ID] value and paste them into the new worksheet.
Is this possible to do in Excel. I normally use Access and you can have triggered events.
Add Code To Worksheet Event Using VBA
Jan 26, 2010i need to add a DoubleClick event to about 40 workbooks.
each has 6 sheets and the code will be added to two of them.
i can cycle thru the folders/subfolders and open the correct files.
but how do i put the code into the specific sheets using a program?
Userform: Run Code Under Another Event
Oct 19, 2006In a Userform, is there a way to run code that's listed under another UserForm event subroutine?
Example:
I have a Listbox and several Labels. When I click on a selection in the listbox, it populates the labels with various data from a spreadsheet. This code is in a "list_AfterUpdate()" subroutine.
If I change a piece of data and click a CommandButton1, I would like the labels to automatically update.
The only way I can think to do that (at present) is to run the code listed in the "list_AfterUpdate()" subroutine.
Is there a way to run that code without duplicating it in the "CommandButton1_Click" subroutine?
Event Code Firing More Than Once
Nov 2, 2006i try with what limited knowledge i have, if you dont mind take a look at the code below, i read your article and added the appropriate line, the code works fine except the msgbox has to be ok'd twice before it exits sub any ideas why?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim MyCell
Set rng = Range("A2:A100")
If Not Intersect(Target, rng) Is Nothing Then
With rng
For Each MyCell In rng
If MyCell = "" Then
MyCell.Select
MsgBox "Please use this next blank cell"
Exit Sub
End If
Next
End With
End If
End Sub
Add New Worksheet With Event Code
Aug 20, 2007Is it possible to copy a "Worksheet_change event" macro to a new worksheet by macro? Like when I insert a new worksheet, a certain macro, for example "run macro on data entry", to be already written in its worksheet_change event.
View 7 Replies View RelatedAdd Event Code To New Worksheet
Oct 3, 2007I'm having trouble copying a macro to a newly created sheet. I do like this:
ThisWorkbook.VBProject.VBComponents("Kopier1").Export filNavn
For Each kopSheet In ThisWorkbook.Worksheets
If Left(kopSheet. Name, 8) = "Inddata-" Then
kopSheet.Copy
ActiveWorkbook. SaveAs ThisWorkbook.Path & "" & kopSheet.Name
ThisWorkbook.VBProject.VBComponents("Kopier1").Export filNavn
Workbooks(kopSheet.Name & ".xls").VBProject.VBComponents. Import filNavn
ActiveWorkbook.Close
End If
Next kopSheet
I copy 6 sheets, named "Inddata-*", and i wan't to copy a module named "Kopier1" with it. I know that i can use an add-in, but that is unfortunately not a good idea in this project. It does export the module "Kopier1", but, it doesn't import it to the newly created workbook!
Unexpected SelectionChange Event Code
May 28, 2008time-to-time an unexpected eventcode appears in my worksheet modules
View 10 Replies View RelatedAdding Buttons And Event Code
Dec 17, 2009How do I make a Command Button and put event handling code (in the VBE) to handle the click event?
View 2 Replies View RelatedEvent Triggered Code Not Working ?
Mar 20, 2008I have a manually calc'd workbook with the following code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("VAL1CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("VAL2CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("CHOICE")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("$L$36")) Is Nothing Then Me.Calculate
If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")
End Sub
Everything works as it should other then the part that is
If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")
When the user selects VAL1CELL This is cell B2 and is a drop down, I want VAL2CELL which is C2 and also a drop down to show what is in Y41 (i.e the first name that appears in the drop down...not a thing happens ? is there a flaw to my code ?
Clear Event Handler Code
Dec 23, 2009I wonder if there is a way to clear event handler code in a userform programmatically?
Haven't quite managed it yet.
Something like:
dim x as integer
With ThisForm.CodeModule
x = .CountOfLines
For 1 to x
.line = ""
Next x
End With
Validation Code For Change Event
Jul 20, 2006change the below code from a worksheet_change to a worksheet_calculate method. and still do the same action. the reason I am changing methods is due to the fact that the validation will not trigger the worksheet_change event to fire. this is my attempt to find an alternative way to fire off the macro.....
View 9 Replies View RelatedMultiple Targets In Event Code
Jul 28, 2006I found this great macro here but I need it target more than column 1. does anyone knoe how to target column 1 and 3 at the same time?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If IsEmpty(Target) Then
Target.Offset(0, 1).Value = Empty
Else
Target.Offset(0, 1).Value = Now()
End If
End If
End Sub
Copy Event Code To Other Workbooks
Nov 1, 2006Am trying to copy some code from the ThisWorkbook object into about 100 other Workbooks. I know how to Import and Export Modules, but when I export code from ThisWorkbook in saves it as a CLS file. Upon export a new class module is created. I simply want to create code that will automatically copy the code from one VBA project ThisWorkbook object to another VBA project ThisWorkbook.
I have the code to open all the files etc just need to figure out how to import the code from ThisWorkbook.