Application.EnableEvents Best Practice

Dec 2, 2008

Am using a number of checks on Worksheet_Change and Worksheet_Calculate events and, depending on the result of the change, resetting a cell value.

For each of these I am using

Application.EnableEvents = False
Range("A1").Value = 0
Application.EnableEvents = True

to avoid that the change of A1's value triggers a calculation event and gets me into a loop.

However, something in my project is setting EnableEvents to False and not resetting it. I've searched the entire project for all instances and each "=False" is swiftly followed by a "=True". The code is not causing an error

The only way to get around this is to have a user press a button to re-enable events, but I can't tell them to press the button because there are no events to trigger the message!

View 9 Replies


ADVERTISEMENT

Display Application EnableEvents Status

May 26, 2008

I would like a message in a cell (eg, true/false) telling me if events are on or off. I think it means returning the Events status.

View 6 Replies View Related

EnableEvents

Aug 27, 2009

EnableEvents. I'm using the following:

View 3 Replies View Related

Enable Macro & EnableEvents Problem To Protect Sheets

Mar 21, 2007

I am trying to crack away to protect my workbook, by forcing users the enable macros on launching.

What I am trying to do is if Workbook_BeforeSave runs a routine called DoIt. This calls HideAll, saves, then calls ShowAll al whilst not updating. (ie so the user is non the wiser and the computer does not asked to save again if no changes are made).

The reason I am trying this is so that if the user closes the document, it will not ask them again to save. Instead it has preserved the state in the HideAll place (ie all important worksheets are VeryHidden.

It is only by enabling Macros on open can the ShowAll make them visible again.

Below is the code I am using and it works, except it seems the Application.EnableEvents = False in the DoIt macro does not seem to be doing what it should.

very dearly love to get an answer as it is driving me up the wall!

Module 1:

Public bIsClosing As Boolean
Dim wsSheet As Worksheet

Sub HideAll()
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
If wsSheet.Name = "Warning" Or wsSheet.Name = "Authorise" Then
wsSheet.Visible = xlSheetVisible
Else
wsSheet.Visible = xlSheetVeryHidden
End If
Next wsSheet
Application.ScreenUpdating = True...............................

View 9 Replies View Related

Practice Data

Feb 24, 2010

Does anyone know where I can get a large amount of generic data that I could import? I don't want my skills to get rusty while I am out of work. I keep finding little tables here and there, but I am looking for a big file that I can really play with. Maybe something with 20 or 30 columns and several hundred rows?

View 9 Replies View Related

Linking Data - Best Practice

May 13, 2006

I have yet another general design or best practice question regarding linking data between sheets in a workbook.

So the example scenario is we have 12 individual customer pages shown amounts invoiced and credited for the financial year.

I want to create an overall summary page.

Is it best to:
1. Use straight link eg. =Data!A1
2. Use Vlookup
3. Use Indirect with Address function
4. Use Database functions

I believe providing you have enough available memory that there isn't an issue with any of the above methods.

View 3 Replies View Related

Best Practice - Multiple Workbooks?

Jun 28, 2006

Task - Streamline & Automate daily Excel Tasks for 13 Staff & Manager, based in 2 different locations. Current Status. 25 workbooks, "Shared", + 1 Workbook {List.xls}, "Not Shared". All Validation lists are in this List book. 1 main data entry sheet in each workbook, feeding totals on Main Tab. Each data entry sheet is 10 columns, 250-700 rows. 5 Colums are free text entry, 5 are fed from data validation lists. The data validation lists are on another Tab, which is linked to a non shared workbook.

Each workbook has a Main Tab, which totals data input, and represents data in colours, in different cells..Picture a warehouse, with shelves & racks. I am in the middle of designing a simple form...but not so simple..thats another story. I am concerned that there is a lot of potential for disater, especially with shared books. I have thought of the access route, but the staff need to see colours on the sheets. Any opinions on what the Best Practice may be to handle this scenario. My main forte has been formula, pivot tables & external data..this is a new challenge

View 3 Replies View Related

Best Practice Spreadsheet Design

Aug 10, 2007

I have been tasked with performing a work measurement study to improve productivity in our organisation. I managed to adapt a paper based report to an excel file; with the view of creating a model. I also intend adding the cost of employing people in specific positions to determine the cost per hour for each specific activity.

View 2 Replies View Related

Best Practice Worksheet Struture

Aug 11, 2008

I am trying to reorganizing my database. Therefore data i like to restructure my current data (sheet 1) into a similar format of sheet 2. Is there a way to automate this process?

View 3 Replies View Related

Formatting Cells To Date - Best Practice

Aug 7, 2006

I thought I read somewhere that it wasn't good practice to format a whole column(s) b/c then you would have empty cells that were formatted. Is there a better way that you guys format your columns without selecting the whole column? I know you can do this in VBA, but I was just wondering if there was a way that I'm unaware of.

Second, is there a formula that can inserted into conditional formatting, that will check a named range against two other cell values. If that ranges date is > AB2 and <AC2 then it will bold that range? I'm trying to figure out how to do things without always relying on VBA if possible.

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

Application Run

Mar 14, 2007

I want to make a macro where it will use the current filename of the workbook I have open (where is says New Quote Sheet 2.xls below) Is there something I can put where it will use the current filename when the filename is changed?

New Quote Sheet is a read-only template, and when a new quote is started, it is renamed.

Eg.
Application.Run "'New Quote Sheet2.xls'!Part8"
Sheets("8-Part").Select
Sheets("8-Part").Copy After:=Sheets(25)
Sheets("8-Pack").Select
Sheets("8-Pack").Copy After:=Sheets(26)

View 11 Replies View Related

Application.Run Error

Aug 5, 2009

I have the following

View 2 Replies View Related

Application.IgnoreRemoteRequests

Dec 11, 2006

With the help of Professional Excel Developement by Bullen Bovey and Green, I am building a Dictator Application. All-in-all, it is coming very well, EXCEPT that I can't seem to get the IgnoreRemoteRequests setting to get written to the registry properly on ShutDown.

On Open, I change this setting to True. In the BeforeClose event, I reset it to False, along with all of the Settings that I hosed on Open. All of the other settings get properly saved on exit, however, the IgnoreRemoteRequests is still set to True the next time Excel gets re-opened.

View 9 Replies View Related

VBA To Open Another Application

May 22, 2007

understand the procedure for opening another application from Excel using VBA?

First check if open, if so, activate, if not, open and set to active?

Any help would be great! -even links to other posts or otherwise

View 9 Replies View Related

Application.VLookup In VBA

Jun 4, 2008

I've probably spent the last 4 hours reading posts from a bunch of different boards as well as this one, and I can't seem to pinpoint my problem. I'm trying to access data from a closed workbook via the following:

'Dim x As Variant

'x = Application.VLookup(Cells(1, 1).Value, Workbooks("C:Documents and SettingsmeMy DocumentsmyOptionsProgramsLiveUpdate.xls").Sheets("LIVE UPDATE").Range("$A$1:$C$5"), 3, False)

Cells(1, 2).Value = x

It doesn't seem to be working as I get the following error:

Run-time error -9

Subscript out of range

View 9 Replies View Related

Open New Application

May 29, 2009

I need a code to open a new Excel Application (Not just a workbook).

eg: say I already have book1.xls opened. Now I want a new excel application (say book2.xls) to be opened. Now, when I close any of the books (by using the cross on top right corner), the other shouldn't be closed.

View 27 Replies View Related

Usage In Application

Jun 26, 2009

done some VBScript spreadsheet reporting involving basically inserting data and some formatting.

I need some advice on what approach to take with Excel on my current project. The basic goal is to scrape project requirements off a word doc and load them into Quality Center (a test management tool from HP, henceforth referred to as QC). I have an hta/vbs process that offers the user choices gleaned from the QC API, scrapes the word doc, and creates a spreadsheet with a row for each requirement and a column for each property a "requirement" has in QC. These values are a combination of data from the word doc and values selected by the user in the preceeding hta interface.

The reason for this Excel step in the process is that all the values chosen up to this point are generic for the entire project. But there are a few properties that will be requirement specific. So all the info is collected in Excel and the user is to then go through each row and choose the "target cycle" (which I pulled off QC earlier in the process and display in Excel as a dropdown list). The "Platform Folder" and the "Module Folder".

Ok, so with that backround, here is my quandry... In the hta interface the user selected all the "Platform Folders" the project will have requirements in. These are then presented in a dropdown in the Excel, so the user can choose which of these Platform Folders this specific requirement should be loaded into. What I need to do is then have the "Module Folders" to be a dropdown that is dependant on the "Platform folder" chosen.

This is difficult enough for me, creating a dynamic dependant dropdown that will be replicated on each row. I don't know exactly how to do this but feel I can probably figure it out with all the help offered on these MVP sites.

What I can't figure out is where to do this. Should I set up the lists, formulas, and control code dynamically? Can these dependant list derive their values from comma delimited lists (like I did the non-dependent lists) or must they be in a worksheet? The only way I see to make the dependent dropdown work correctly is to have it run off a worksheet event. Can worksheet event code be applied dynamically or do I need to have a "template" type workbook containing the code in the application's folder and use that instead of creating a new workbook at exe time? I still have to write the upload into QC code, and that will probably be a macro that the user will have to install into their personal.xls. Can the dependent dropdown code be installed in the same way even though it needs to be event driven? Another aspect is getting the data for the dropdown. I can either get the lists earlier in the process when I already have a connection to QC and pass it along, or I can connect to QC from Excel and get the values dynamically. I am assuming passing them in will be more efficient.

View 9 Replies View Related

Database Application

Feb 5, 2010

I am very new with Excel and VBA, what i need to do is:
develop an initial log in to open an excel workbook...
and then open the workbook if username and password

1. ask for user name (field should be 50 characters)

2. Ask for a password (password alphanumeric with special characters field 25 characters)

3.Validate user to a salesman data base
if log in attempt fails after 3 attempts, purge buffer, clear log in screen and lock keyboard for 60 seconds)

I have done the whole excel application all i need is this thing to pop up when double click in my direct access to it..

Also save document with the invoice number that in in a cell

and How can I make a cell auto increase the invoice number every time open workbook?

View 9 Replies View Related

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

Hide A Application

Oct 20, 2006

If you run a macro like this: Application.Visible = False. How do you get back to the application

View 3 Replies View Related

Minimize Application

Feb 13, 2007

i want to minimize the excel application and show a userform on startup.

View 2 Replies View Related

Start A New Application

Mar 22, 2007

I have an excel file which automatically loads an user form once the file is opened and when the user form is closed, the file also closes. When the file is open i am not able to switch to anyother excel file because this userform is active. Is there a way, when i open the file with userform, a New excel application starts. So that i can use the other excel files opened by the previous excel application.

View 4 Replies View Related

Vlookup From Another Application

May 4, 2007

The problem is I need to do a vlookup from another Office application. I need VBAcode that:

opens up an excel spreadsheet,
performs the vlookup
and then retrieves the associated value.

View 2 Replies View Related

Application.filesearch

Jun 27, 2007

I have created a macro that must extract data from other workbook. It opens the workbook, with an exist, and isopened control, and then open it. Itīs running well, but i found while making another macro, that if the file direction is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", then i cant run the exist control. Next you have the code im using. In the first version, i had .LookIn = ThisWorkbook.Path. Now Iīve tried:

1. Dont put .LookIn, and fName is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls"
2. .LookIn = "http://www.xxx.com:8080/"
.FileName = "Challange%20Cost%20Proposal%20Sheet.xls"

Both are failing, it says, the file doesnt exist. If i try directly WorkBooks.Open "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", it opens correctly, but i would have a control, because the original file name could change, and the macro would crash if does.

Function OpenWorkBook(fName) As Integer
If Exists(fName) Then
If (IsOpen(fName) = False) Then
Workbooks.Open (fName)
OpenWorkBook = True
Else
MsgBox "The WorkBook " & originWorkBookName & " is already open."
OpenWorkBook = -1
End If
Else.........................

View 5 Replies View Related

Mouse Over In Other Non VBA Application

Mar 15, 2008

Is it possible to get mouseover information from outside of Excel using VBA? I'm trying to automate a non-microsoft package but I need my mouseclicks to wait for a message to appear within the other application before my macro continues. I've already asked something similar before but I've put a better title on this thread so apologies for the repeat.

View 2 Replies View Related

Controlling Another Application Where Hotkey Is Not Available?

Jan 29, 2014

I am working on an Excel macro which uses the Shell function to open another application, the AppActivate statement to change focus to that application, and then a series of SendKeys statements to perform tasks for which keystrokes (hotkeys) are available. However, there is one step in the process which does not have a hotkey available, but requires clicking on a drop-down with the mouse. Is there a way to have an Excel macro "click" on something for which a hotkey is not available?

View 1 Replies View Related

Macro To Quit Application

Jun 16, 2014

I am trying to add the following to my macro to close excel when the macro is completed:

[Code] ......

This will save the workbook before exiting. How do I direct it to not save any open workbooks and exit the app?

View 1 Replies View Related

Open An Spreadsheet Except Through The Application

Dec 16, 2008

I cannot open an Excel spreadsheet except through the Excel application. This includes through Windows Explorer, on the internet (this forum), in email. I need to save the file first, then open Excel, then open (through Excel) the file.

In Email, I get "System cannnot find the file specified" On this site, I get "Access to the specified device/path is denied" From Explorer, "Cannot find the file.... based on the criteria xls(or one of its components). Make sure the path and file name are correct and that all required libraries are available."

Last time, our IT group couldn't fix it and had to reinstall the entire Office package to get it working again. I'd rather not go through that.

View 2 Replies View Related

Close And Save Application

Dec 5, 2008

When a user X's (clicks of the X in the titlebar) of a user form, I want to save whatever input has been made to that point and close the application. this is what I tried but it neither saved input nor closed the application

View 2 Replies View Related







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