Prevent Event Code Running When Other Workbooks Open

Aug 23, 2008

I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.

Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .

View 7 Replies


ADVERTISEMENT

Prevent Event Code Running

Oct 19, 2007

I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off? My problem is this...

At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through. I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!

View 5 Replies View Related

Prevent Combobox Change Running Other Control Event Procedures

May 17, 2008

I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.

View 8 Replies View Related

Prevent VBA Macro Firing Event Code

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

2002 Code V 97 Code: Add A Small Workbook Open Event Code Which Works For Me But Debugs For The Others

Jan 27, 2009

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

Textbox Click Stops Event Code Running

Aug 26, 2007

I have a textbox from the drawing toolbar. When someone changes a cell then clicks in the textbox, Worksheet_Change does not run. If they double click in the cell, that's OK I can capture that event and protect the sheet, stopping them clicking in it. But if they just start typing in the cell, I can't capture that. I have seen some API code which captures keypresses, but it is not practical to use as it loops repeatedly. I could lock the textbox and have the user do something to unlock it, but this is a last resort.

View 7 Replies View Related

Copy Event Code To Other Workbooks

Nov 1, 2006

Am 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.

View 7 Replies View Related

Code Running When Already Open But Not When Newly Opened

Feb 26, 2014

Two workbook, A & B. Code in B copies some data over to a sheet in workbook A.

It does this not problems if A has remained open, but if it has just been opened, I get a "subscript out of range" error at the "With Workbooks" line.

The bit in red is what I have recently added to allow workbook A to be closed unless needed. Since adding this, the debug error has emerged, even though there was no problem when workbook A remained permanently open.

View 3 Replies View Related

Object Defined Error When Code In Workbook Open Event

Sep 7, 2007

I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.

The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.

The code is below;

Private Sub Workbook_Open()

DoEvents

Dim Res1 As VbMsgBoxResult
Dim GovRng As Range

For Each GovRng In Sheets("Map").Range("GovernanceMembers")
If GovRng.Value = Application.UserName Then Goto 111
Next GovRng

Exit Sub

View 6 Replies View Related

VBA Code To Run Macro In All Open Workbooks

Mar 12, 2014

I have a macro to get copy of sheet named "Doc Info" from workbook File 1 to active workbook.

I could do it for one file on any active workbook.

But what I would require is, upon executing this macro , i want this macro to get executed in all open workbooks( could be any numbers ).

I want to move copy of sheet from File 1 to all open workbooks ( which i am doing it manualy for every file )

All these open workbooks could be from any folder , wont be in same folder.

So logic is to execute my macro apply in all open workbooks in my computer.

Below is the code and i have attached file for test

HTML Code: 

Sub Copysheet()
Dim wSht As Worksheet
Dim wBk As Workbook
Dim wBk1 As Workbook

Set wBk = ActiveWorkbook 'Workbooks("File 2.xls")
Set wBk1 = Workbooks("File 1.xlsm")
Set wSht = wBk1.Sheets("Doc Info")

wSht.Copy before:=wBk.Sheets(1)

End Sub

View 2 Replies View Related

Code To Work Between Two Open Workbooks

Mar 23, 2014

I have this code...

Code:
Sub Macro1()
'
Sheets("Log").Select
Range("F1").Copy
For Each wb In Workbooks
If wb.Name Like "Test Book*" Then
wb.Activate
Sheets("Data").Select
Range("O2").Select
ActiveSheet.Paste

[code].....

can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'

Using this pasted date in cell O2 carry out the required filter function.

The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.

View 4 Replies View Related

Macro Code To Work Between 2 Open Workbooks

Jun 4, 2008

I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here

Set current_wb = ThisWorkbook
Dat_Fil = Application. GetOpenFilename
Workbooks.Open Dat_Fil
Set wbkFrom = Application.ActiveWorkbook

NOTE-Workbook object variables are declared outside any macro, and as "public." Code here

Public current_wb As Workbook
Public wbkFrom As Workbook

In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro

current_wb.Activate
current_wb.Worksheets(1).Select
wbkFrom.Activate

I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.

View 4 Replies View Related

Prevent Worksheet_Change Event

Mar 12, 2008

I'm trying to find a way to bypass a worksheet_change event. Right now, my code validates if a change has been done to a specific cell ("D13"). It works perfectly, too perfectly actually.

Here is the code I got :

First line of Module 1 :

Public bDeviation As Boolean

On Sheet1 :

Public Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim VRange As Range
Dim strName As String

Application. ScreenUpdating = False

View 4 Replies View Related

Prevent Control Event Procedures Firing

Apr 27, 2008

I have CheckBox1 (.Value = FALSE) on the UserForm and when I run my sript ... this change Value to TRUE and run the subrutine Private Sub CheckBox1_Click. I need block this step with using VBA code.

View 4 Replies View Related

Prevent Private Sub Macro From Running

Nov 14, 2008

I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.

View 6 Replies View Related

Prevent Macro From Running If Cell Is Red

Apr 29, 2008

I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.

View 9 Replies View Related

How To Prevent Auto Macros From Running

Sep 11, 2008

I have made a vba program in excel 2003 that opens a worksheet using Workbooks.Open, and copies all the worksheets out into the program etc. The worksheets that I am opening have macros that automatically execute when the worksheet is opened (opening up some forms ). These macros are running when I open the file using VBA. How can I prevent this from happening. The automatically executed code in the workbook being opened is located in "this workbook" and can be seen below.

Private Sub Workbook_Open()
Dim CfileName As String
'Check Config sheet for template state
If Config.Cells(10, 3) = 1 Then
CustomizeDirInfo.Show
End If

If Config.Cells(10, 3) = 2 Then
GetConfData
GetGrpData
StdFileGen.Show
Else
Exit Sub
End If
End Sub

View 9 Replies View Related

Cracked Vb Code: Prevent People From Accesing The Code I Protected The Code Blocking It From Visualization

Feb 8, 2007

I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.

View 8 Replies View Related

Prevent Custom Functions From Running Simultaneously?

Jul 31, 2014

I have this excel file with some functions inside a Pivot Table, which use a year value as filter (the value comes from a report filter field) and data from a different spreadsheet to calculate a percentile value ... I created custom functions to replace the excel functions, passing the values as parameters.. The problem is now that when we refresh the connection (Refresh All button under DATA tab) the cells that contain the values returned by the custom function lose those values (go blank) In order to get the values I need to select the filter value again, while when I was using the normal PERCENTILE function the values would always refresh normally

Now... I think the problem might be related to the fact that the spreadsheet in question calls 5 different custom functions at the same time.... and, those 5 function call another at least two more helper functions... so, maybe this recurrence in calling the functions is causing the thing to break up

Is there a way I can prevent this from happening? Maybe put some flag or something that will cause the functions to be executed one by one, and not all at the same time?

View 2 Replies View Related

Running Macros On Event

Jul 3, 2006

Excel 2003

I have three macros:

Sub Concat()

ActiveCell.FormulaR1C1 = "=IF(RC[-1] > 1,CONCATENATE(""*"",RC[-1],""*""),"""")"

End Sub
Sub InsertLocation()

ActiveCell.FormulaR1C1 = "=IF(RC[-2] > 1, R[2]C[2],"""")"

I need To have them excecute automatically once data Is enter into a cell In column A:

I 've attempted onEvent configurations starting with -

Sub FillScanSheet()
ActiveCell.EntireColumn.Select
If ActiveCell > 1 Then
Application.Run Concat
Application.Run InsertLocation
Application.Run Dupes
End If

However I keep getting a circular reference error --

View 7 Replies View Related

Prevent Worksheet Change Event Causing Chain Reaction

Jan 14, 2008

I'd like the users to be able to change some detail in a couple of places and have it updated throughout the spreadsheet. Basically, the user can change the line name in any of the input sheets and the code changes the sheet name, and searches for the reference to the old name in the overview sheet and changes it accordingly.

The problem I have at the moment is that I would also like the user to be able to change the line name from the overview sheet too... I am having trouble thinking how to have similar code in the "Overview" sheets Worksheet_Change event without getting into a big constant loop... e.g. if the line name is changed via code on the individual input sheets won't that then trigger the first code, which will trigger the second etc. etc. I have the following code in the ThisWorkbook section:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sName As String
Dim sOldName As String
Application. ScreenUpdating = False
sOldName = ActiveSheet.Name
If Target.Address <> "$B$1" Then Exit Sub
sName = ActiveSheet.Range("B1")
On Error Goto ErrorHandler
ActiveSheet.Name = sName
On Error Goto 0
Sheet8.Select 'this is the overview sheet
Cells. Find(What:=sOldName, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate........................

View 2 Replies View Related

Exit Event Running Twice Due To SetFocus

Feb 15, 2013

I am using a Textbox1_Exit event that (if certain values are true) sets the focus to different Textbox. However, when I invoke the Textbox3.SetFocus, it redoes all the code in the Textbox1_Exit event since it's technically leaving now.

How do I stop this from happening and ensure that the Exit event happens once? OR How do I properly SetFocus inside an Exit event?

View 8 Replies View Related

Running Macro On Save Event

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

Running Macro On Any Key Pressed Event

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

Prevent Custom Function Running When Inserting/Adding Rows

Apr 8, 2008

I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?

Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C

Function to count used rows....................

View 2 Replies View Related

Prevent Custom Menu Appearing In All Workbooks

Sep 8, 2007

I added a menu command button to a toolbar in a specific workbook. The macro is housed only in that workbook and the macro needs to run on every worksheet in the workbook. However, the button also appears on EVERY Excel workbook that I open when it should not. How can I create a menu command that only appears in the workbook that contains the macro?

View 2 Replies View Related

Suppress Open Events When Using Workbooks.Open

Dec 3, 2008

I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...

Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.

way to suppress the code in the target workbook I open through workbooks.open

View 9 Replies View Related

Running Macro In Different Workbooks

Mar 25, 2014

I have 7 different workbooks as part of my daily routine.

At present the first workbook is opened via the task scheduler, and the macro is run using the following code

[Code] .....

This then opens the next workbook and closes the current one. The next workbook then runs its macro again from a timed call

[Code] ...........

This repeats for 7 workbooks. The problem is, if a macro overruns - the whole schedule is out of sync when macro's are called depending on the time. What I would like to do, is link each macro by calling for the next one, and closing the current workbook. However, when i have tried to do this, the macro has always stopped when the workbook has been closed - the next macro doesn't run.

View 2 Replies View Related

Running The Same Macro For Different Workbooks

May 7, 2007

I was able to create a macro that formats the worksheet that I am working on. However, I would like this macro to be available to all worksheets that I would be downloading from a system.

I tried using the personal macro workbook, but when I ran the macro, it formats the personal worksheet and not the one that I would want to format.

I used general codes, but it pasted the formulas onto the personal.xls file.

View 4 Replies View Related

Prevent Closing Of One Workbook When Two Are Open

Sep 23, 2009

I'm trying to prevent a user from closing a perticular workbook.

Here is some important factors.

Workbook(A) = "LA-01-04-10" This is only one of 52 other but only one is open at a time.

Workbook(B) = "LA.xls" Always the same name and is always opened when one of the workbooks(A) above it opened.


When workbook (A) is opened it will then open another workbook (B) at that time the window is hidden workbook(B) So the user can only see workbook (A) I have a button on workbook(A) that unhides the window to show workbook(B) After the user completes his work in Workbook (B) they need to click another button that sorts the list and returns them to workbook(A) (Which also hides the window for workbook(B).

The problem is I have users that are trying to close workbook(B) instead of pressing the sort list button. So what I want is to prevent the user from closing workbook(B) by displaying a msgbox saying they need to press the button.

Ive tried several approches to this but I'm still having problems. The below script works if workbook(B) is active or shown. The message is displayed and the workbook will not close. However if the user is in workbook (A) and trys to close the message is displayed and both workbooks close like they are suppose to. But why is the message displayed?

One other note I should say is that not only does workbook(A)Open workbook (B) but it also closes it. Which may be why I'm having problems?

I have also included both workbooks so you can see all the code.

I have this code in Thisworkbook of workbook(B)

View 10 Replies View Related







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