Allow User To Disable Event Code
Dec 29, 2007
I have these codes in place in my excel file, there is nothing wrong with them at all. Here is the the problem : My superior runs the macros in place in the file almost daily, and when he is done he then closes it and all is well.
The macros does things to other files so they are changed in a way that the supervisors like them. I avoided putting macros in these individual files, because of the attempt to make it as simplistic as possible without them being able to affect overall file.
The problem happens when he (my superior) accidently makes a change in the primary sheet in the workbook with all the macros, it affects alot of my formulas and macros that are already in place. Within this workbook, it keeps a log of things from the other files results, so I have to have a record of it in the workbook, hence having a save event in place.
The Question: Is it possible to create a macro button, that will allow him to disable the codes below, so that he can close it and open again, so that it doesn't show any of the errors that happens by accident and try to close it again so there is no errors? Basically, I am asking what is the code for this, and where would I place it in.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mylogoff
Set mylogoff = Sheet1. Range("A65536").End(xlUp).Offset(0, 1)
mylogoff.FormulaR1C1 = "=NOW()"
mylogoff.Copy
mylogoff.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = Fals
mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]"
Application.DisplayAlerts = False
Sheets("Start Here").Select
ThisWorkbook.Save
ThisWorkbook.Close
End Sub......................
View 9 Replies
ADVERTISEMENT
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
Jan 23, 2010
I have a user form with four pages. I would like to disable pages 2 and 3 if the checkbox on page one is cheked. I know the pages have a property called "Enabled" but i can't figure out how to code this.
The user form is called frmAnvilProgNew
The pages are called Page1, Page2, Page3, and Page4
The checkbox is called Check1
see the attached workbook. Is this possible? Should I be using tabs instead of pages?
View 2 Replies
View Related
Jan 25, 2007
I have a workbook that has a macro that needs to run before it is closed. I have a button on my sheet that runs that macro and then closes the sheet. Is there a way that I can disable the "X" at the top right hand of the workbook and force the user to close the workbook via my button?
View 3 Replies
View Related
Jun 1, 2008
if you can prevent an event macro from running at will.
The problem is occuring when I have a userfrom with various controls, and the change of one control triggers an event macro that changes other controls, this then in turn triggers further control change macros.
I have tried altering the macro name to things like optionbutton1_change, select, enter etc.
The only other way around it I can think of is to introduce a binary variable, but that will mean ensuring it os always the right value and then coding an IF statement on each event macro.
Therefore, I'm thinking there must be an easier way, some kind of switch ??? Something that says ignore any event macro when the control is changed as a result of different event macro.
- I'll include a small example below just to clarify what I am talking about.
private sub optionbutton1_click()
userform1.textbox1.value = "Test Change"
end sub
private sub textbox1_change()
msgbox "Change taken place"
end sub
What I am trying to achieve is getting excel to ignore the second macro when the control is changed by another macro rather than a user entry.
View 9 Replies
View Related
Jun 6, 2008
I am trying to ask to the user to check if they logged out when they close the workbook but my code is not working...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("OD&D Log-in").Select
If Range("H5") = "reconcile" Then
a = MsgBox("Do you want to Log-Out?", _
vbYesNo)
If a = vbNo Then Cancel = True
If a = vbYes Then
Sheets("OD&D Log-in").Select
Else
Workbooks("Daily OSD Log (ver5).xls").Close SaveChanges = True
End Sub
View 9 Replies
View Related
Jul 10, 2006
it is possible to add a mouse over event to this Macro so that when a user runs the mouse over the bitmap picture that this macro is assigned to it would show the value in TextBox7 for the record with the specified TextBox1 Value.
For example for the below Macro it would show the Value of TextBox7 for the record that matches "103" in TextBox1
I have searched the Forum as well as a few different VBA books and can not find much on Mouse Over events
Sub crkt_id_103()
Load frmMain
frmMain.TextBox1 = "103"
frmMain.Show
End Sub
View 9 Replies
View Related
Feb 23, 2010
I have a master spreadsheet that houses most of the information that remains in my control, and several other user spreadsheets that update cells from that master through links.
I am having some problems lately with users doubleclicking the locked linked cells in their workbooks, and excel then wanting to open and give the user access to my master spreadsheet. Is there a way to disable this feature? I would like it to not even acknowledge the action if that's even possible because it confuses them as to why they are getting a prompt to open another document.
View 5 Replies
View Related
Nov 15, 2012
If possible, I'd like to be able to catch & handle the event of a User deleting a row (or potentially a column) from a specific Worksheet so I can then implement some automated "housekeeping processing" on other existing rows / columns.
I'm using XL 2010 so from what I've found so far it looks like I should possibly be using the Worksheet_Change() function, but beyond that I'm currently stuck.
View 1 Replies
View Related
Dec 30, 2011
What is the vba code to disable scrolling Up or down?
View 1 Replies
View Related
May 11, 2007
I have two radio buttons. Let's call them Button_1 and Button_2. They both have code behind the click event.
One of the lines in the Button_1 code makes the value of Button_2 false. If Button_2's value at the time is true, the Button_2 click event fires off and runs through it's code.
How do I disable Button_2's click event in this scenario so the value changes, but the code in it's click event won't run?
I tried application.enableevents = false, but that didn't do it.
View 9 Replies
View Related
Oct 13, 2008
I started to write mini codes but still much to learn.
Private Sub ComboBox3_Change()
If Range("A1") = "TRUE" Then
ComboBox3.Enabled = 1
ElseIf Range("A1") = "FALSE" Then
ComboBox3.Disabled = 1
End If
End Sub
View 9 Replies
View Related
Jan 4, 2008
Is it possible to disable the autosave feature in vba?
View 3 Replies
View Related
Sep 2, 2006
Is is possible to use VBA to remove/delete a macro and also remove code like this on worksheets:
Private Sub Worksheet_Change
End Sub
Private Sub Worksheet_Activate()
End Sub
View 4 Replies
View Related
Dec 5, 2013
Which code is "better" to disable D&D, C&P....
Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub
Private Sub Workbook_Deactivate()
[Code] .......
OR This one:
Option Explicit
Private Sub Workbook_Activate()
With Application
.CutCopyMode = False
.CellDragAndDrop = False
[Code] ........
I noltice that one uses "Option Explicit" and one doesn't, one seems to be longer than the other, but when I tested both, they both seem to do the same thing. I did notice that neither one of them prevents a "paste" into the worksheet when something is copied from an outside source, but that is not a concern right now!!!
View 3 Replies
View Related
Feb 27, 2008
I need to to use VBA to copy a worksheet (which i've managed to do!), but I need the new worksheet to have a Worksheet_Change event. Now when I copy the worksheet, the event doesn't copy over (obviously as its a cut and paste jobby). Any ideas on what code I need to add in the Worksheet_Change event just after the new worksheet is automatically created?
View 14 Replies
View Related
Jan 17, 2007
I have seen many examples posted here that are close to what I need, but I am not experienced at writing code, so I am not sure how to make the changes to this code that apply to what I need. So I will try to explain what I am looking for & hopefully not be too long winded:
I have an excel workbook that has several worksheets within it - 10 of the worksheets are identical as far as the formulas that are in each of the cells, however, they are all VLOOKUP cells that refer to another excel workbook (used as a "database")which lists all of our projects - there is a cell in each of the 10 sheets that can be changed that will allow that particular worksheet to access the information in the "database worksheet" for the particular project name that is entered in this cell.
There is currently an event worksheet code for each of these worksheets which allows for a picture to be displayed "floating" above cell (L13) based on the project name that is showing in this cell- however it is based on all of the project pictures "living" in each worksheet (the picture that is called up by the project name is displayed while the rest are hidden - as per the formula)- the code is shown below:
Option Explicit
Private Sub Worksheet_Calculate()
Dim oPic As Picture
Me.Pictures.Visible = False
With Range("L13")
For Each oPic In Me.Pictures
If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
End Sub
However - this option works fine when there are 5 or 10 pictures/projects - but we are looking to grow our project database. So, I was hoping to be able to store the pictures in another location (such as another worksheet or in a file on the server - I would also appreciate input if anyone has an opinion on which would work better?) and have some type of worksheet event code that can be written in to each worksheet that would access the picture in this "central" location and have them appear in cell (L13) of each worksheet based on the project name displayed.
View 9 Replies
View Related
Jan 25, 2008
I have 2 worksheets ('pathways', 'pathway events').
'pathways' has unique rows with a unique ID i.e. [Pathway ID] whilst 'pathway events' has the same initial column [Pathway ID] but with multiple values of the same [Pathway ID] value.
If an [Pathway ID] value is selected in 'pathways', I want it to trigger some code which will open a new worksheet and copy the multiple rows in 'pathway events' with the same [Pathway ID] value and paste them into the new worksheet.
Is this possible to do in Excel. I normally use Access and you can have triggered events.
View 9 Replies
View Related
Jan 26, 2010
i need to add a DoubleClick event to about 40 workbooks.
each has 6 sheets and the code will be added to two of them.
i can cycle thru the folders/subfolders and open the correct files.
but how do i put the code into the specific sheets using a program?
View 9 Replies
View Related
Oct 19, 2006
In a Userform, is there a way to run code that's listed under another UserForm event subroutine?
Example:
I have a Listbox and several Labels. When I click on a selection in the listbox, it populates the labels with various data from a spreadsheet. This code is in a "list_AfterUpdate()" subroutine.
If I change a piece of data and click a CommandButton1, I would like the labels to automatically update.
The only way I can think to do that (at present) is to run the code listed in the "list_AfterUpdate()" subroutine.
Is there a way to run that code without duplicating it in the "CommandButton1_Click" subroutine?
View 6 Replies
View Related
Nov 2, 2006
i try with what limited knowledge i have, if you dont mind take a look at the code below, i read your article and added the appropriate line, the code works fine except the msgbox has to be ok'd twice before it exits sub any ideas why?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim MyCell
Set rng = Range("A2:A100")
If Not Intersect(Target, rng) Is Nothing Then
With rng
For Each MyCell In rng
If MyCell = "" Then
MyCell.Select
MsgBox "Please use this next blank cell"
Exit Sub
End If
Next
End With
End If
End Sub
View 2 Replies
View Related
Aug 20, 2007
Is it possible to copy a "Worksheet_change event" macro to a new worksheet by macro? Like when I insert a new worksheet, a certain macro, for example "run macro on data entry", to be already written in its worksheet_change event.
View 7 Replies
View Related
Oct 3, 2007
I'm having trouble copying a macro to a newly created sheet. I do like this:
ThisWorkbook.VBProject.VBComponents("Kopier1").Export filNavn
For Each kopSheet In ThisWorkbook.Worksheets
If Left(kopSheet. Name, 8) = "Inddata-" Then
kopSheet.Copy
ActiveWorkbook. SaveAs ThisWorkbook.Path & "" & kopSheet.Name
ThisWorkbook.VBProject.VBComponents("Kopier1").Export filNavn
Workbooks(kopSheet.Name & ".xls").VBProject.VBComponents. Import filNavn
ActiveWorkbook.Close
End If
Next kopSheet
I copy 6 sheets, named "Inddata-*", and i wan't to copy a module named "Kopier1" with it. I know that i can use an add-in, but that is unfortunately not a good idea in this project. It does export the module "Kopier1", but, it doesn't import it to the newly created workbook!
View 2 Replies
View Related
May 28, 2008
time-to-time an unexpected eventcode appears in my worksheet modules
View 10 Replies
View Related
Dec 17, 2009
How do I make a Command Button and put event handling code (in the VBE) to handle the click event?
View 2 Replies
View Related
Jul 21, 2014
Is there a way of capturing the print event to run some code?
I.e. The 'Print' button is clicked and then code executed in the sheet.
View 2 Replies
View Related
Mar 20, 2008
I have a manually calc'd workbook with the following code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("VAL1CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("VAL2CELL")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("CHOICE")) Is Nothing Then Me.Calculate
If Not Intersect(Target, Me.Range("$L$36")) Is Nothing Then Me.Calculate
If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")
End Sub
Everything works as it should other then the part that is
If ActiveCell.Address = "VAL1CELL" Then Range("VAL2CELL") = Range("Y$41")
When the user selects VAL1CELL This is cell B2 and is a drop down, I want VAL2CELL which is C2 and also a drop down to show what is in Y41 (i.e the first name that appears in the drop down...not a thing happens ? is there a flaw to my code ?
View 9 Replies
View Related
Dec 23, 2009
I wonder if there is a way to clear event handler code in a userform programmatically?
Haven't quite managed it yet.
Something like:
dim x as integer
With ThisForm.CodeModule
x = .CountOfLines
For 1 to x
.line = ""
Next x
End With
View 9 Replies
View Related
Jul 20, 2006
change the below code from a worksheet_change to a worksheet_calculate method. and still do the same action. the reason I am changing methods is due to the fact that the validation will not trigger the worksheet_change event to fire. this is my attempt to find an alternative way to fire off the macro.....
View 9 Replies
View Related
Jul 28, 2006
I found this great macro here but I need it target more than column 1. does anyone knoe how to target column 1 and 3 at the same time?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If IsEmpty(Target) Then
Target.Offset(0, 1).Value = Empty
Else
Target.Offset(0, 1).Value = Now()
End If
End If
End Sub
View 4 Replies
View Related