VBA Tips - Enable Events

Good morning

I am having an issue with a userform event handler

The following snippet of code is from a userform click event handler (to delete a customer).

Code:

' find the customer name on the Tables sheet and delete it
    Sheets("Tables").Activate
    Application.EnableEvents = False
    Sheets("Tables").Unprotect Password:="brittaly"
    Set rngTemp = Sheets("Tables").Range("CustNames_Tables").Find(what:=strCustName)
        'rngTemp.Select
        rngTemp.Delete xlShiftUp

I use the .EnableEvents = False to turn off the combobox_change event handler so when the customers name is deleted from the table it wont trigger the _change event (which it has been doing).

Why is it still triggering when I've set the event enabler to off? When I step thru the code, right after the bolded line above, the code jumps right to the beginning of the combobox_change event handler even though .EnableEvents is set to false

Hello Everyone,
I have used something liek the following to create 2 shortcuts that open a file:
Code:

  Public Sub Shortcut2()  

  Dim WSHShell As Object  

  Set WSHShell = CreateObject("WScript.Shell")  



  Dim DesktopPath As String  

  DesktopPath = WSHShell.SpecialFolders("Desktop")  

  CreateShortcut DesktopPath & "\Program.lnk", WSHShell.ExpandEnvironmentStrings(Range("B2").Value & "\Location\Program.xls"), WSHShell.ExpandEnvironmentStrings("%windir%"), (Range("B2").Value & "\Location\Shortcut.bmp")  

  CreateShortcut Range("B2").Value & "\CLIPr\Canadian Life Income Planr.lnk", WSHShell.ExpandEnvironmentStrings(Range("B2").Value & "\Location\Program.xls"), WSHShell.ExpandEnvironmentStrings("%windir%"), (Range("B2").Value & "\Location\Shortcut.bmp")  

  Set WSHShell = Nothing  



  End Sub  

My question is this: I would like Application.EnableEvents = True to run if the shortcut is clicked...can I do this?
Thank you

Hello

When i copy a cell and want to past it in another sheet, once i move to the distenation sheet the copy commeand dissapear as if there is no copy instruction was given.

I tried Application.EnableEvent = True, but the problem still exist ?

Please help ?

Yours

Hi All,

Can some please tell me the VBA code lines to:

Disable Application Events

&

Enable Application Events

Regards

ColinKJ

Quote:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("c19")) Is Nothing Then
Range("c19") = UCase(Range("c19"))
Call
End If
Application.EnableEvents = True
End Sub


This is my code but i also want to add another cell C16 that is monitored.
I tried range("c19","c16") but it doesn't seem to like it

Anyone can tell me the way please?

Let me try to lay out what I am trying to do he

-I have a password protected file which only certain ppl know the password to.
-once those ppl are in the file, a macro runs which unhides all the sheets. If an unauthorized person is viewing the wkbk (i.e. they open the wkbk with macros disabled), they can only view one sheet (not the sheets with the sensitive information)

-I am trying to make code which hides all sheets upon saving and also upon closing the wkbk. I have set up code which allows me to be able to SaveAs in other locations while still keeping all sheets hidden upon saving. It also allows the authorized user to click save (w/o closing) and it will save the wkbk but keep all sheets unhidden.

-I am also trying to get it to hide all sheets upon closing, when I click to close the file, I have the following code:

Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("CloseVariable").Range("CloseVar").Value = "True"
HideSheets

End Sub

The "CloseVariable" sheet is just a hidden sheet I have set up to track whether or not the user is trying to save with closing the file or save w/o closing. After this code is run, the excel event asking if I would like to save changes pops up. If I click NO, there is no problem, the file closes, the sheets are hidden and everything is fine.

However, if I click yes, the following code is run: (this is the code I mentioned earlier, used to hide sheets upon saving the file). It has if statements to handle the situation of whether or not it is a saveAs and whether or not it is a save w/closing or w/o closing the file. However, excel crashes when I get to the indicated line shown below. I am not sure I fully understand what is going on with the application.enable events code.

Any ideas of why excel is crashing on me??

Sub Workbook_BeforeSave(ByVal SaveasUI As Boolean, Cancel As Boolean)
HideSheets

If SaveasUI = False Then
Cancel = True
Application.EnableEvents = False
Else
Application.EnableEvents = True
End If

If Sheets("CloseVariable").Range("CloseVar").Value = "False" Then 'If the save is not part of a close, then unhide sheets

If SaveasUI = False Then 'If this is not a saveAs, then unhide sheets
ThisWorkbook.Save
UnhideSheets
End If
Else

Workbooks.Close

I have a routine which changes the contents of a combobox. I'd like to prevent the combobox's change event firing when this happens.

Code looks like this:

Code:

Sub Thing()

[do stuff..]

Application.EnableEvents = False

cb_Name.Value = strVariable

Application.EnableEvents = True

[do more stuff]

End Sub

Problem is that the change events fires regardless of how I've set the EnableEvents property. I can't work out what I'm doing wrong. Any thoughts?

Hello All:

I'm trying to speed up some of my macros and have been advised in the past to set Enable Events to false and Calculation to x1Calculation Manual. However, before I proceed to do this can any one tell me exactly what I'm doing when I set Enable Events to false? As well when I set Calculation to x1 Manual?

Somewhere in my rambling through web sites I found a user defined function that turned all three of these off, then back on with only a single line of code each. I'm hoping someone else has seen this and kept better notes of where they have been.
Thanks!

Hello, all.

I apologize for a silly question in advance. I haven't done VBA coding since before our company switched to 2007.

I have put the following code in a workbook:

VB:

 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
     
    GenerateDailyAverages 
     
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



and it does not run on saving the Excel file.

The public sub "GenerateDailyAverages" is in the same workbook module, and works fine if I run it manually.
I have tried putting it in the Workbook_Open sub as well, and same thing - no run.



Am I missing something obvious?

Thanks in advance!

Ben.