Temporary Disabling Of Workbook Event

Jul 13, 2006

I have a workbook change event that when fired checks criteria to see that the sheet is complete. If not complete, the user must complete information on the sheet before continuing. The problem is that some of the "stuff" the user must do requires macros that fire and gain access to other sheets. Thus the "trap" I have set to restrict the user to just the one sheet has backfired on me restricting the user to just the one sheet. Is there a way that the workbook change event (or any other workbook event) be temporarily disabled until activated again?

View 2 Replies


ADVERTISEMENT

Disabling End Statement During OnChange Event

Mar 25, 2009

I am writing an XLA in XL2002 which will act on a number of legacy and new spreadsheets. During the execution of the XLA I set cell values.

i.e. m_rRange.Cells(j, i).Value = m_sInVal(j, matchIndex)

This then triggers the onChange event of the worksheet. Unfortunately, for one of the spreadsheets that this needs to work with, this causes an error. The error is trapped within the spreadsheet itself but the sub End is called which halts all VBA code execution.

My problem is that the XLA has not finished working and gets terminated early. I cannot capture this using an onError statement as it is never triggered (the spreadsheet code stops the execution and control never returns to the XLA).

I can disable the events using Application.EnableEvents but this means that none of the onChange events are called which is also not desirable.

So my question is: is there any way of disabling the termination of code using the End() statement, or of detecting when it happens to stop it?

View 9 Replies View Related

Create New Temporary Workbook

Jun 17, 2008

I'm trying to create a new worksheet just in memory.

Private Sub test()
Dim wsheet As New Worksheet
Dim a As Variant
a = ActiveSheet. Range("A1").Value
wsheet.Range("A1").Value = a
End Sub

When I run this, the like "wsheet.Range("A1").Value = a" gives the error <Class does not support automation or does not support extended interfaces. I could use that vitual worksheet (I call) to do functions like sorting filtering and other things without affecting the actual data, and get only the results. Is there a way to create such virtual worksheets.

View 6 Replies View Related

Disabling Of Workbook Maximization

Oct 12, 2006

How to disable by code Maximization of Workbook?

View 2 Replies View Related

Disabling Macros Will Close Workbook

Apr 3, 2007

First of I have a workbook, that runs several macros. All works fine. However I need to better secure the workbook. I realize that there is no guarantee on the security with excel but am hoping for something to solve the problem stated below.

When a user (with high security settings) opens the workbook in question, I would like the workbook to CLOSE out if the user chooses to disable macros when the asked by excel.

View 9 Replies View Related

Temporary Highlight Row Of Active Cell

Nov 18, 2010

When looking at a spreadsheet with many columns, it would be nice of the row of the cell I have selected would remain highlighted in yellow, so that as I scroll across rows it's easy to view the data associated with the selected cell. Of course, if I select another cell, I would want the highlighting to change to THAT row. I'm guessing this would have to be in a this.workbook change event.

View 9 Replies View Related

Deleting Temporary Internet Files

Feb 9, 2010

This is a follow up to an earlier post about web queries

I need to be able to clean out my directory :
"C:Documents and Settings" & Environ("UserName") & "Local SettingsTemporary Internet Files"

But nothing I've tried seems to work.

The kill command doesn't appear to work with wildcards... is there a way I can loop through the directory and delete the files one by one?

View 6 Replies View Related

Delete Temporary Internet Files

Jun 6, 2008

When I run a webquery to download information from online I have found the common 1004 error to occur very often however I have found that if I delete my temporary internet files prior to running the webquery, this problem does not occur. I therefore require an automated function to delete the temporary internet files however the simple 'Kill' statement does not seem to work.

View 5 Replies View Related

Deleting IE Temporary Internet Files / Cookies / History?

May 4, 2014

I have a VBA application that uses the IE Application object many many times through a loop, and then repeats. So basically it is running continuously, and opening invisible IE window every few seconds.

I have code in there that 'kills' all IE instances between each iteration so that the number of windows doesn't pile up, but after awhile the temporary internet files, cookies, and history build up too.

Is there any VBA code that will clear those three things out?

View 8 Replies View Related

Event Fire For Any Workbook

Jun 16, 2007

Is it possible have code in an addin that fires when, X happens in any workbook?

For example,

Any time a user tries to print a worksheet a message box pops up asking, "are you sure you want to print that"

View 9 Replies View Related

Workbook Before Close Event

Jul 22, 2008

I have a sheet that on open looks at the username and determines which tabs can be seen by that user. If macro's are not enabled, I want the sheet to just display sheet 4.

here is the code I have on open that works fine: -

Private Sub Workbook_Open()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = True
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = False
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub

I have a Workbook_BeforeClose() function that does not, and I am stuck as to why!

Here it is: -

Private Sub Workbook_BeforeClose()
If Environ("username") = "Bob" Then
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
ThisWorkbook.Save
Else
Sheets("Sheet1").Visible = False
Sheets("Sheet2").Visible = False
Sheets("Sheet3").Visible = False
Sheets("Sheet4").Visible = True
End If
End Sub

So basically after "Bob" is done it will save the sheet with only tab 4 visable. This means that if someone without Macros enabled opens the sheet they can only view tab 4 (I know it isn't password protected in this example, but it will be)

View 9 Replies View Related

BeforeSave Workbook Event

Apr 20, 2007

I am trying to find out more info regarding the BeforeSave workbook event, need example of a short procedure that would prevent the user from saving the workbook with the current workbook name, and would automatically force the file to save as a web page? I'm sorry that I don't have any examples of code that I'm working with, and it feels pretty juvenile to just ask someone to write code for me. But I haven't been able to get past step one with this.

View 2 Replies View Related

Workbook Event Missing

Apr 17, 2008

I have problem about adding workbook event.

when I go to ThisWorkbook and click the left side dropdown at the top of the code window, I can't see the word "Workbook" appear. What I can see is only (General) and there is no other choices.

Other worksheet and application event work fine.

attached is a screen shot when i try to add a workbook event.

View 7 Replies View Related

VBA - How To Import Selected Text File To Temporary Worksheet Using Macro

Aug 5, 2013

Below is the code I currently use to do the following which creates service reports.

When you click on the command button you can choose any XML file you want to import. This file is copied to a temporary worksheet where i selectively choose cells to populate textbox's which later populate my reports. What I want to do is the same exact thing but with a text file. Not all text files are the same length so it needs to be able to have a range. Here's my working code.

For Each SheetName In Array("Maintenance Data Sheet")
With Sheets(SheetName)
.Range("B127") = ""
.Range("U127") = ""
.Range("AJ127") = ""
.Range("AV127") = ""
.Range("AY124") = ""

[Code]...

Cancel:

MsgBox "No XML Selected"
End Sub

View 1 Replies View Related

Workbook Open Event Not Working

Nov 29, 2011

In R1 I sometimes place a cell reference that I want the workbook to immediately go to when it is next opened. I have the following code which does not work as expected (Ignore the necessary .Select parts):

Code:
Private Sub Workbook_Open()
Sheets("MSCI Asia Data").Select
Range("R1").Select
If Not IsEmpty(ActiveCell) Then Application.Goto reference:=Range(Range("R1").Value), scroll:=True
End Sub

Currently R1 contains the value R91 but when the workbook opens, it is not going to the cell R91 in the required worksheet.

View 9 Replies View Related

Workbook Open Event Error

Feb 21, 2013

Have some code in the workbook open event, which when I run when the workbook is already open works fine, however when triggered when the workbook is actually opened it fails on the first line.

Code:
Private Sub Workbook_Open()
Dim lngLastRow As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim X As Long
Dim Y As Long

'Define Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Sheets("Sheet1").Activate

View 2 Replies View Related

Workbook Activate Event Not Firing

Mar 19, 2008

button on main workbook opens 2 other workbooks and assigns a workbook object to them. the 2 opened workbooks are Activated in turn, range values changed and macros on these sheets invoked and results captured and pasted back onto the starter workbook. the macro is within a sub in a module as are the ones in the second workbook. An example of the code used is:

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Dim wkbTest As Workbook
Set wkbTest = Workbooks("Test.xls")
wkbTest.Activate
Sheets("G").Activate
Range("Today").Value = Format(Now(), "dd-mmm-yy")
Application.Run "'" & wkbTest.Name & "'" & "!TestMacro" ............

View 5 Replies View Related

Open Another Workbook For Multiple Userform Event Use.

Sep 12, 2009

The use of this userform is to find a customer reference number. Im using two combobox's and a textbox.value that are populated from another workbook.

Though the way it is at the moment it opens the workbook and closes the workbook every time a new value is set to one of the combobox's.

I want to open the workbook on the useform initialize and do everthink the useform need from it. And then on the userform terminate close the workbook. Or somehink to this equlivent so this process of finding the customer referance number goes faster.

View 6 Replies View Related

If Workbook Event Is Active Make It Visible

Apr 12, 2012

I have a boolean toggle for disabling and enabling a Workbook_Change event.

Right I invoke macros to turn the Workbook_Change event on and off and I don't know what state it's in.

Two Questions:
1) (required) I would love to be able to visually know if it's enabled no matter which sheet I am on
2) (bonus) Be able to change the state with a persistent interface (e.g. perhaps the Ribbon?) no matter which sheet I am on.

View 1 Replies View Related

Double Click Event In Shared Workbook?

Jul 29, 2014

I have a Shared Workbook that 10 or 15 users are in and out of all day. In Column A on a few sheets I have a Before Double Click Event that launches a userform. The macro works for all users except this one person. I have tried several things:

1. I closed the file and Reopened it, to make sure that the user did not disable macros.

2. I checked the file on other users computers to see if the file was working properly (It was.)

3. I went to Options>Trust Center>Trust Center Settings>Macro Settings and Enabled All Macros on this User's system

None of these actions corrected the issue.

The purpose of the BeforeDoubleClick Event is to store Columns A:E data and then let the user add more information through the form. Once the User fills out the UserForm. A:E is transferred to one of a number of sheets depending on criteria in the UserForm. A:E is transferred along with the new information that the user has entered in the UserForm.

Another symptom is that on other computers when the Before Double Click Event is activated the Userform launches and the cursor does not appear in the cell until after the User Clicks a Command Button to Update Data on the Userform. On this individual's computer the cursor does appear in the cell without the Userform launching....

View 9 Replies View Related

Changing A Workbook Event Handler Via Code

Feb 5, 2007

I have a workbook, that when opened the first time needs to prompt the user to save it. I got that working with no issues by using

Private Sub Workbook_Open()
SaveOnOpen
End Sub

where SaveOnOpen is a procedure in another module. What I would like to do now is re-assign the Workbook_Open sub to be set to null, so that it doesn't run any more. Is it possible to somehow assign Workbook_Open to call a null procedure, as opposed to setting up an onTime call to delete the code itself?

View 9 Replies View Related

Sheet & Workbook Event Code Not Firing

Jun 27, 2008

why this code does not work when the worksheet is changed between range "B1:F5"?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B1:F5")) Is Nothing Then
With Range("B1:F5")
Cells(Target.Row, 7) = Cells(Target.Row, 6).Value + Cells(Target.Row, 5).Value
End With
End If
End Sub

View 3 Replies View Related

Workbook Sheet Change Event Prompts Save

Oct 28, 2009

I have many worksheets in a workbook that need to be saved if a user changesanything on them.
These sheet names all end in "....SD" and the code needs to only run on those sheets. I have learned alot from the forum but not enough, just yet . . This is what I have so far:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sht As Object
For Each sht In ThisWorkbook.Sheets
If LCase(Right(ws.Name, 2)) = "sd" Then
MsgBox(Prompt:="You must save changes. Save now?", Buttons:=vbYesNo) = vbYes Then ThisWorkbook.Save
End Sub

It doesn't like the 2 "Then's". (Don't laugh - I'm trying.)

View 8 Replies View Related

Workbook Won't Close Correctly And Keeps Re-opening On The Refresh Event

Jun 6, 2006

I'm trying to combine 2 sets of code that I have searched other threads for.Both use the OnTime code to trigger events after 5 and 10sec.
My problem is that the workbook won't close correctly and keeps re-opening on the refresh event. I understand both events need to be passed to the dTime variable, but I am obviously doing something wrong. I will include the code as I'm sure its something obvious;



Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dTime, "Sort", , False
If Cancel = False Then Application.OnTime dTime, "RefreshIt", , False
On Error Goto 0
End Sub

Private Sub Workbook_Open()
Run "RefreshIt"
Application.OnTime Now + TimeValue("00:10:00"), "Sort"
End Sub






Public dTime As Date
Sub Sort()
dTime = Time + TimeValue("00:00:10")
Application.OnTime dTime, "Sort"
Range("A6:M10").Select
Selection.Sort Key1:=Range("I6"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B2").Select
End Sub

Sub RefreshIt()
Sheets("Text").Range("A7:F38").QueryTable.Refresh
dTime = Time + TimeValue("00:00:05")
Application.OnTime dTime, "RefreshIt"
End Sub




Any help would be appreciated!
Thanks

View 6 Replies View Related

Sumproduct And Sum Array Formula (calculate The Salary Payments To Temporary Employees In A Particular Work Unit)

Jul 16, 2009

I have a spreadsheet representing a month where I am trying to figure out different scenarios for employees. One scenario is that an employee could have to move to a temporary position. In that case, I need to calculate the salary payments to temporary employees in a particular work unit. I've tried several different approaches to this problem, but am still getting the error.

One method has been using this sumproduct formula:

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

Worksheet Open Event: Macro Run Every Time A Sheet In A Workbook Is Accessed

Jan 5, 2010

Is there a way to have a macro run every time a sheet in a workbook is accessed? Something similar to the workbook open event, but for worksheets.

View 2 Replies View Related

Worksheet Change Event :: (ByVal Target As Range) Event In Module After Creating A Sheet

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

Disabling A COM Add-In

Feb 26, 2010

Is there a way using VBA that will allow me to disable a COM Add-in when I open a workbook without me (or the user) going through the steps to remove it first.

The reason for my asking is this: We have installed SAS Add-in 2.1 to Excel 2007. So this brings up SAS under the Add-Ins tab. But then when my program executes and it sees I use command bars, it adds my command bars under the SAS menu. I am trying to make the interface as clean as possible for the user, so they only see my command bars or menus.

Also, if we were to install the SAS Add-in 4.2 to Excel 2007, I have read in a SAS paper that SAS Add-in 4.2 creates its owe tab on the ribbon instead of inside the Add-in tab. So I would be able to use xml to make the SAS tab visible="false".

View 9 Replies View Related

Disabling Alt+f11

Apr 12, 2007

Is there a way to disable alt+f11

I will only disable it if the file is opened as read only
and I will enable it on before_close.

View 5 Replies View Related







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