Using Application Events With An Add-In

May 23, 2006

I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).

I have created a class module with the following code (exactly as the Excel help):

Public WithEvents App As Application

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
SortE1Output 'This is my procedure that determines whether the
'spreadsheet is of the correct format and then reformats it
End Sub

"After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."

When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".

View 6 Replies


ADVERTISEMENT

UserForm Textboxes Triggering Events - Even After Events Have Been Disabled?

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

Could Two Change Events (Conditions) Be Possible With Worksheet Change Events

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

Application.DisplayAlerts Freezes Application

Jul 14, 2006

Im copying and pasting data from one workbook to another but when I want to close the source workbook, it comes up with this message that I have much data and if I want to keep this in a clipboard. I thought I could disable this with Application.DisplayAlerts = False but when I do this, Excel freezes. Im I doing something wrong. How can I supress this window?

Public path As String
Sub Get_data()
path = "\Nlchoosa.nlOPS_Processes$OPS_ProcessesReports Sector performance"
Workbooks.Open Filename:=path & "ReportsSector Performance Reporting week.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Windows("Sector Performance report Week.xls").Activate
ActiveSheet.Paste
Application.DisplayClipboardWindow = False
Windows("Sector Performance Reporting week.xls").Activate
Application.DisplayAlerts = False
End Sub

View 6 Replies View Related

Events

Jun 20, 2006

some times i choice (Initialize , active, open, click
Workbook_BeforeSave)
and so on

tell me as the differences and usage of these strings in order to improve our(my) programming skills.

View 3 Replies View Related

Two Events In A Workbook?

Oct 21, 2008

I am trying to write a code where user has to respond to input box option depending on yes or no selection. There are two types of responses to different questions. One needs to respond between 1 and 100 (if yes) and for some other questions needs between 1 and 4 (if yes). A "no" will enter zeo value. But it is not working. I am using two named ranges "VALIDCELLS" (for 1 to 100) and "FREQVALIDCELLSS" (for 1 to 4). Here is the code;

View 6 Replies View Related

Events Before Printing

Jan 30, 2009

I have early made a macro that will change some of the cells before printing and then changing back again to initional state after the print out.

http://www.excelforum.com/excel-misc...-printing.html

But now I have to do let people also print a copy a regular way, without the macro I made before. But if they go through the regular way iof printing I would like to insert at header in red color, to notify them that this is just a preview and not the way to print. And here we come to the problem ....

View 13 Replies View Related

Can't Create Events In VBA

Nov 14, 2013

why am I unable to create events on my worksheets? They were usually available to me when I selected "Worksheet" on the dropdown in vba.

When I open Excel, only "General" comes up in the drop down menu. What am I missing?

View 4 Replies View Related

Averaging Events

Apr 29, 2007

I have a spreadsheet,3 columns are shown in the attached.The first column gives the date of the event,the second column the time and the third a rating.I want to put the average rating for each event in each cell in column 4.

View 9 Replies View Related

Multiple Change Events

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

Add The Total Time Of 643 Events?

Mar 2, 2014

I am trying to add the total time of 643 events. I have the start time subtracted from the end time to give me each individual events total time, at the bottom I am trying to put a total time for all events but it keeps displaying 14 hours and 13 minutes even though it should be somewhere around 500 hours.

View 4 Replies View Related

Trapping Userform Events

Jan 9, 2009

I'm trying to trap the events of a user form in a class of mine. At the top of my class I have the standard:

View 2 Replies View Related

Sorting And Ranking Events

May 6, 2008

I have a worksheet that I use on a track and field day to enter times and distances and then can allot points based on performance. That is not the problem.

My question is: Is there a function or formula to then sort through the data and rank the participants in order of their finish in a different worksheet? I imagine I would somehow need to link the data to each name. I have attached a copy of the actual worksheet.

View 9 Replies View Related

Timeline With Recurring Events?

Jul 2, 2014

How can I get EXCEL to display recurring events on one line? In the example below, I want all events of type PLAN to be on the same line.

Phase
Time
Duration
End

[Code].....

View 1 Replies View Related

Control Events And SetFocus

Feb 3, 2006

I have been working for 8-1/2 hours to get the focus to move to a specified control on my user form if criteria are met in two other controls on the same user form. It's not responding as I expect. I think I know what is happening; I just don't know why.

Situation: I have a user form with the following relevant fields in this tab order - LMonthDay, tbMonthDay, SBMonthDay, LYear, tbYear, SBYear, LTime, and tbTime. I used the designations L, tb, and SB in my control names to represent label, text box, and spin button respectively.

Problem: Once the SBMonthDay control has the focus, if I press TAB without changing the control's value, it does not execute the Exit event statement the way I anticipated. I want the focus to move to the tbTime control when the tbMonthDay & tbYear controls are already properly populated. (They will already be populated for 99% of the entries in this case.)

Private Sub SBMonthDay_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Debug.Print "SBMonthDay_Exit Begin"
If ISLIKE(tbMonthDay.text, "####") _
And ISLIKE(tbYear.text, "####") Then
tbTime.SetFocus
Else: tbYear.SetFocus
End If
Debug.Print "SBMonthDay_Exit End"
End Sub ...........................................

View 9 Replies View Related

2 Worksheet Change Events (?)

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

Turn Off Events From Running

Nov 29, 2006

I have a "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" in a worksheetsheet code.

I would like to turn this procedure off while I run another routine and then turn it back on after the routine has run.

View 3 Replies View Related

User Defined Events

Jan 21, 2007

Can you create user-defined events in excel? I ask because the worksheet_change event does not capture pastes or undo/redo.

View 4 Replies View Related

Trigger Click Events Via Vba

Jun 14, 2007

Is it possible, that while running code that the code can say initiate the click event on a command button on another sheet.

Say that I have a button on Sheet1 called "wkscmd_DisplayDEI"

Behind that button is obviously some code. I want to know is it possible that while some code is running ( code does not reside on the module page for Sheet1 that it can send a pseudo click to the button?

View 9 Replies View Related

Cancelling Events Disabled?

Jul 14, 2007

The boolean Cancel suddenly stopped working for me in all workbook and worksheet events. Consider a simple example:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

does not cancel right click, like it has before. Same with BeforeDoubleClick. I can only assume it is some kind of a setting or similar, as there is absolutely no change in the code.

View 4 Replies View Related

Events For Active Workbook Via Add-in

Sep 13, 2007

Is there a way to set up a cell selection event trigger through an addin when it is installed so that I can respond to selections made on user's sheets?

View 3 Replies View Related

Multiple Worksheet_Change Events

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

Call Run Events Between 2 Workbooks

Jan 14, 2008

event of one workbook from another workbook using VBA.

I am able to call the method using call <method name>. eg. call start_method.
But i want to know how to call a event using VBA.

What i exactly want is:

1) Based on some if condition in workbook1 , i want to call the workbook 2 (button123_click) event.So that its code will start executing.

2) I cannot do anything in workbook 2 , VBA code , as its a client file.

View 4 Replies View Related

Create Calendar From Events Table

May 22, 2011

I receive input for events from three regions (East, Central, West) and have to match those against a calendar. Multiple events can happen at the same time. I want to be able to read the input into an area under the calendar dates, create a merged cell with those start-end dates (inclusive), then input the region and event (concatenated) into the merged cell. It would look like this (with example of the input data below the calendar), but over the course of a year or more. There can be as many as twelve events occurring simultaneously.

Example.xlsx

Input from the regions can happen sporadically, so it is a "living" spreadsheet that can change dynamically.

I'm pretty good with formulas, but this seems like it would need a VBA solution - which I'm not good at!

View 9 Replies View Related

BeforeClose And Deactivate Events Not Running?

Oct 14, 2008

I have some code in the BeforeClose event of the workbook - it worked the first time I tried to shut down the wb, but never since. I opened a new wb and copied in the code and again, it worked first time, but not since. The code is just calling a function, nothing heavy. I've tried the Deactivate event as well. I'm more of an Access VBA kind of gal, so don't know if I'm missing something big here.

View 5 Replies View Related

Multiple Change Events In One Worksheet

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

Run Events When Opening Workbook By Code

May 11, 2009

How can i manage that my code in the Workbook_Open() event is run when i open that workbook with some code from an other workbook?

When i manually open that workbook, excel runs all events, including my Workbook_Open() event, but i need to open this one out of an event of an other workbook.

View 13 Replies View Related

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 View Related

Worksheet Multiple Change Events

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

User Form TextBox Events

Feb 22, 2010

I have a UserForm that utilizes the Change event of a Textbox.

The form goes through some validating before any calculations happen.

My problem is if the user wishes to backspace, clear or perform any changes to this textbox, it bugs out.

Here's the

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved