Event Code Errors When More Than One Cell Changed/Deleted
Apr 15, 2008
I used the code below for Conditional Formatting. This works fine but the VBA-code crashes when I delete more than one selected cell. Is there a simple modification possible to prevent this from happening?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:C250")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
Case 2
icolor = 12
Case 3
icolor = 7
Case 4
icolor = 53
Case 5
icolor = 15
Case 6
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
View 3 Replies
ADVERTISEMENT
Mar 25, 2008
I have added this bit of code to change the apperance of entered time from 0835 to 08:35
UserInput = Target.Value
If UserInput > 1 Then
NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2)
Application.EnableEvents = False
Target = NewInput
Application.EnableEvents = True
End If
And it works like a charm. Except that if the content in one of the cells later is deleted a "Run time error 13" is the result. Debug leeds to the line "If Userinput >1 Then"
Can this error be avoided..?
View 3 Replies
View Related
Oct 14, 2007
I copied the sheet and redid the format. I renamed the original dashboard sheet from count to "keep" and named the copy after the original "count" Everything still works great - until i either hide or delete the original count which is now named "keep". I get an error at the red colored line below ".publish false".
I have attached an image of the error....
View 7 Replies
View Related
Dec 5, 2012
I have a cell in my spreadsheet (J3) that I need to never be changed or else other macros in my workbook will not work properly. I decided to try and use data validation since it is easy to change the rule every month and I can stay away from messing with passwords.
My cell value is a date, so I set data validation to have that cell equal only that specific date. If a user tries to overwrite the cell value, the error messages is prompted correctly. The one BIG problem is that I can just press the delete key and delete the contents of the cell. Is there any way of preventing this from happening without having to lock the cell and password protect the sheet?
View 3 Replies
View Related
Sep 27, 2006
I run a web query from [url] to import the latest currency rates into Excel. I refresh the data every minute. Every time Cell B19 changes I want to run the following
If Cells(19, 2).Value > 1.27 Then
MsgBox "buy"
End If
I tried putting that code in the Worksheet_Change event but it only works if the user physically changes the cell, not if it's changed by a web query. So how can I run code that triggers when a cell is changed by a web query?
View 3 Replies
View Related
Dec 4, 2006
Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1, 1)
If Not Intersect(.Cells, Range("b3:b6")) Is Nothing Then
Range("b7") = "Not Found"
For i = 3 To 6: txt = txt & Cells(i, "b").Value & "_": Next
For Each r In Range("m3", Cells(3, Columns.Count).End(xlToLeft))
For i = 0 To 3: txt2 = txt2 & r.Offset(i).Value & "_": Next
If txt = txt2 Then
Range("b7").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b16:b19")) Is Nothing Then
Range("b20") = "Not Found".....................
I'm working with this code right now. The problem is the macro will only work if i type the numbers manually. if the values are retrieved from a combobox, the code above down not work as it cannot read the values.
View 9 Replies
View Related
Sep 6, 2013
I have a lot of formulas that I don't want to be changed or deleted, but I still want the user to be able to enter data into the cells.
If I use "Protect sheet" I can't select a cell or enter something into it. I sure there is some setting I have to change, but I'm not sure what to select.
I'm using excel 2007
View 3 Replies
View Related
Jul 15, 2014
the following two statements return the error "Application-defined or object-defined error"
Code:
Sheets("Purchase").Range("PurchaseTax").FormulaR1C1 = "=IF(RC[-1]0,ROUND(RC[-1]/11,2),"")"
Sheets("Purchase").Range("FreightTax").Formula = "=IF(FreightCharge0,ROUND(FreightCharge/11,2),"")"
View 2 Replies
View Related
Dec 5, 2008
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("R1C1")) Is Nothing Then
'do something
End If
End Sub
Now, that Private Sub works fine if you change the value of R1C1 manually or from another macro.
But if R1C1 is the cell linked to a list box, nothing will happen if you change its value by selecting different items in the list box.
View 4 Replies
View Related
Jul 16, 2014
I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
View 7 Replies
View Related
May 13, 2009
I want to show which rows of data have been changed by incrementing a revision attribute. For example, if a user changes the contents of a cell anywhere between rows 2 and 13 and col 1 and 9 then the revision attribute in col 10 would increment from 1 to 2 (for the affected row). If another change affects the same row then the rev attribute would increment to 3, and so forth. I don't care which cell was changed only that something on that row was touched.
I thought the CHANGE event was a dead ringer for triggering some VBA code to control this but, since part of the change event code writes the revision value, this triggers another CHANGE event causing an endless loop until something (??) kicks in and stops it after 220 iterations. Is there a way to inhibit the change event just prior to updating the cell containing the version attribute?
Rather than post the code here I have submitted the workbook that includes the whole setup and code. I should also mention I looked at all the other worksheet events and I do not see any "triggers" that would fire each time a cell content is changed. As a side note, is there a way to step into the code of a change event? F8 does nothing.
View 3 Replies
View Related
Mar 21, 2007
I have looked at a series of Change Event topics and code but can't see what I need. I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes. I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...
View 4 Replies
View Related
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
Jun 2, 2013
I have this code that removes a group of cells on any row that contains "Y" in Column L, but it does not move the remaining group of cells on a row up after the cells are deleted.
VB code so that when a range of cells are deleted, the row(s) below are moved up?
Sub RemoveReceived()
LastRow = Cells(Rows.Count, "H").End(xlUp).Row 'change "H" to the column that determines which is the last row
For x = LastRow To 4 Step -1 'assuming it starts from row 4
If Cells(x, "L") = "Y" Then
Range("H" & x & ":K" & x).ClearContents ' or use Delete
[Code] ........
View 2 Replies
View Related
Oct 20, 2008
I have a workbook with 20+ sheets in it, I add sheets and delete sheets on a daily basis, except for one sheet that is like my summary sheet.
Is there a code, formula, or magic spell that will list the names of the sheets that I have deleted? For instance, if my workbook has 50 sheets and I delete 49 of them, I want to see cells A1 thru A49 (or where ever I wish to place them) filled with the names of the sheets I just deleted.
View 9 Replies
View Related
Sep 5, 2007
I have an existing code that runs almost flawlessly but I am running in to formatting cells error (or at least I think that is the problem).
For example, here is a line of code where my error lies:
If Worksheets(FirstSheet).Cells(Counter, ColtoMove) = MoveIf Then
'DO SOME ACTION
If I put my cursor on MoveIf it shows the expect number of 4500016239…….BUT it errors!
When I step through the code and go to the worksheet (or go to the left side of the = in the above code the data in the cell is “4.5E+09“.
I’ve attempted to change the formatting of this cell range to “Text” by the following With statement:
With Worksheets("CreateOrderFormsData")
.Range("AC:AC").NumberFormat = "Text"
End With
Note: if I verify the cells formatting after the above code is ran by rt click on the cell, format cells the Number field is set to “CUSTOM” and the Type: field is set to “TEXT”. Is this the problem? Please read on.
But that did not seem to do the trick…..actually if I now go to the left of the = again (which is now set to text) the data is “###########” in the cell, in the formula bar the number is the expected 4500016239 though but the CODE STILL FAILS!
Am I on the right track…..can anyone assist with this error please?
View 9 Replies
View Related
Jan 26, 2008
Sub CombineWBs()
Dim wb As Workbook
Dim newWB As Workbook
Dim i As Long
On Error Resume Next
With Application.FileSearch
.NewSearch
.LookIn = "C:Budget"
.Execute
Set wb = Workbooks.Open(.FoundFiles(1))
RenameWS wb
wb.Worksheets.Copy
Set newWB = ActiveWorkbook
wb.Close SaveChanges:=False
For i = 2 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i))
RenameWS wb
wb.Worksheets.Copy After:=newWB.Worksheets(newWB.Worksheets.Count)
View 9 Replies
View Related
Nov 7, 2006
I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long
For i = 1 To 31
If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then....................
View 3 Replies
View Related
Oct 2, 2008
I have created a userform with multi pages... One of the page is up and running ok.
However I have on one page entries like customer number, name, address, and so on.
I have the same userform from a book which I like to use in one of my pages.
Now what I have problems with is that I need one page to refer to another worksheet of the workbook.
Lets say first page to one worksheet the second page to another. And so on.
Here is the code from the book and it likes to refer to the same worksheet as the userform is opening up.
Private Sub frmDFG_Initialize()
'Startzeile für das Drehfeld festlegen
If .ActiveCell.Row < 3 Then
spn_Change = 3
Else
spn_Change = ActiveCell.Row
End If
End Sub
View 9 Replies
View Related
Oct 15, 2008
My code works well if the worksheet name is Sheet1. It will change constantly. I have alot of other similar workbooks that are not affected and cannot figure this out. Here is the code that I get the error at: ....
View 9 Replies
View Related
Nov 21, 2006
I have a workbook which includes a simple set of options on closing such as selecting the front sheet, restoring scrollbars and saving the workbook. To avoid problems with subscripts out of range I am using the ThisWorkbook statement to close the workbook.
This works fine and causes the workbook to close when close is clicked on any excel window. The problem is that excel falls over when it tries to resume closing the other workbooks. I am given (ironically) an error saying "excel has encountered a problem and needs to close". Does anyone know how to work around this?
My code is below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Toolbars9(True)
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayWorkbookTabs = True
End With
View 8 Replies
View Related
Mar 6, 2008
i would like an if macro to pick up if cell dest (i have used a case to define this cell) contains an error or more imoprtantly #REF! then change the offending cells to 0 and put up a message box to put "Check XTA". i have found some that i think may work but i didnt understand them (they had function in them :smask so i couldnt put them in.is there a way to put them in with out functions or could someone point me in the right direction.
View 4 Replies
View Related
May 9, 2008
how to supress the continue,end,debug, message when there is a vba error. The idea being that if there is a bug in my system that I have no realised, I don't want my end user seeing that message! I would preferrable design my own error message to appear instead.
View 2 Replies
View Related
Nov 30, 2009
I have a rather large workbook (30 sheets, 10MB) that has one worksheet with many INDIRECT functions in it (pulling data from the same file, different tabs). I am working to put simple code into the workbook to protect and/or unprotect all worksheets. I have gotten code to work to both protect and unprotect all the sheets, but when I run the unprotect code (see below), and then I go to edit the workbook, data from the sheet with many "indirect" functions temporarily "overwrites" the data on the active sheet (this is fixed when I scroll my mouse over the effected cells). I am developing this workbook for other users, so I'd like to fix this before sending it off to them.
This problem does not happen until after I run the following .....
View 14 Replies
View Related
Aug 18, 2014
I have this VBA code running in cell A1 which gives you a yes/no message box when you try to change the selected cell. I am trying to have the Target.Address = 'Specific Header Cell in Table' but I am not able to develop the code to do so. Here is the code:
[Code] .....
The main reason I have this message box is to prevent the user from changing the cell in the table because the titles in the tables are linked to macros. If they are changed, the macros will not run. Also, I am aware there are codenames for the excel sheets in a workbook. If there are codenames for the header titles in an excel table, I could link my macros to the codenames. As a result, I would not need this macro for the message box.
View 2 Replies
View Related
Feb 26, 2014
I've managed to piece together a VBA code that works perfectly for my needs. The problem is that the sheet it is on gets adjusted a lot, and I only need the code to run when certain cells are changed (rather than each time any cell is changed).
Here is the code I have now:
[Code] .....
Put simply, I need this code to run only when a cell in Range "TValueDeal" is changed. Is making this adjustment as simple as adding a couple lines of code, or is there a better way to write the entire thing?
View 3 Replies
View Related
Aug 23, 2002
Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?
I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.
1. Is there a way to call VB (say, sheet1.myFunction())?
2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)
The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.
View 9 Replies
View Related
Jan 21, 2014
I wrote the following basic code earlier (which will end up being part of a larger code)
Code:
Dim DateInput As String
Dim LastCell As String
Sub LastRowInputBox()
LastCell = InputBox("What is the last row number in the range?", "Last Row Input")
[Code] ......
As you can see, all the ColumnCopyInsert Subroutine does is copy select/copy four columns (E:H), inserts the copied selection immediately to the right, inserts formulas and autofills down to the last row required. I added the LastRowInputBox routine as the range of rows varies from week to week (inserting new rows, deleting others).
The code works great...up to a point unfortunately. If ran as it is, everything that should be copied/pasted will be and cells autofilled to the row specified via the InputBox. Here comes the problem, if any rows are either inserted or deleted then the code throws out "The object invoked has disconnected from its clients" when it reaches the first instance of the following line:
Code:
Selection.Insert Shift:=xlToRight
At which point, Excel hangs (or maybe in some sort of loop) and I have to open Device Manager and close the Excel exe process.
View 5 Replies
View Related
Jun 10, 2009
I asked for a macro to delete the whole row if a duplicate customer number was found in column B. Sometimes, though, my column numbers change. So, logically thinking, I simply changed the criteria, but the macro ONLY seems to work if duplicate customer numbers are in column B only.
This code below won't work if the Customer Number is in column D instead of B even if the reference of B:B is changed to D:D, it doesn't carry the macro over.?
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
