Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Worksheet change event to trigger Results

Puzzle:
The code example below causes an "Object doesn't support this property or method (Error 438)" message. The Compiler sees the word "ComboBox1" in the code before it gives itself a chance to create the Object and make references to it as a Member of the Sheet it was created in.

Question:
How can Code be used to add a ComboBox to a worksheet and set properties such as Fonts and FillColor for that ComboBox without using or adding a Form? (See: Experiment Goals and the Code example below for a better explanation.)

Experiment goals:
Without using the Forms Toolbar and without adding a Form to the Project.
In VBA code,
1. Add a ComboBox to a Worksheet.
2. Format the ComboBox (e.g. Change its Font, FillColor, Placement etc. See: code example below)
3. Do the above in "One" Run. (In other words, without asking the user to run the program twice.)

Note: The desired results can be viewed by adding Conditional Compiler options to create the object on the first Run, and Format it on the Next. For an explanation of why this workaround can't be used See: Goal Notes (below code example).


	VB:
	
 ComboBoxTest() 
    Dim strtHrBox As Shape 
     
     'Create an Hour ComboBox.
    Set strtHrBox = Application.ActiveSheet.Shapes. _ 
    AddOLEObject(ClassType:="Forms.ComboBox.1", _ 
    Left:=249.75, Top:=38, Width:=30, Height:=15.5) 
     
     'Error: Object doesn't support this property or method (Error 438)
     'Note: ComboBox1 doesn't exist until after Run-time
     'The Compiler is complaining about the non-existance of a
     'Sheet Member it hasen't given itself a chance to create.
    With Application.ActiveSheet.ComboBox1 
        .AutoSize = False 
        .BackColor = RGB(197, 197, 197) 
        .ColumnCount = 1 
        .ColumnWidths = 1 
        .Font.Name = "Small Fonts" 
        .Font.Size = 7 
        .Font.Bold = True 
        .LinkedCell = "Sheet1!E2" 'Change this to match your Sheets Name.
        .ListFillRange = "Sheet1!B2:B13" 'Change this to match your Sheets Name.
        .ListRows = 12 
        .ListWidth = 28 
        .Placement = xlFreeFloating 
        .SelectionMargin = False 
        .TextAlign = fmTextAlignLeft 
        .Name = "StartHrBox" '
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Goal Notes:
Getting this to work in one pass or one run will allow the code to hook to an event trigger. Or at the very least I won't have to ask the user to run the program twice in order to display an object correctly on screen. Figuring out how to do this without using Forms allows me to embed the Controls in Shapes.

Class Note:
The ComboBox Class has all the properties and members that need to be used for this experiment but the ComboBox Class seems to be intended for use within a Form. I mentioned this because the With block in the above example works on the second Run after ComboBox1 is created as a Member of the Sheet in which it was created by the first run.

LIBRARY NOTE:
Class ComboBox
Member of MSForms
Library MSForms
C:WINDOWSSYSTEMMSForms.TWD
Microsoft Forms 2.0 Object Library

Thank you for your time and help. At the very least I hope this sparks some interesting thoughts, ideas, or questions for the Forum.

Hi

I am trying to figure out which event gets triggered when a user selects "Tools">"Protection">"Protect Sheet".

When protection is enabled, I want the sheet to disable a few checkboxes, and when the protection is turned off, I want the worksheet to now have the checkboxes enabled.

Currently I have 2 work arounds:
1) Enable/disable the protection, save the file and reopen to see the checkboxes change. I used the Workbook_Activate event to handle the enabling/disabling of checkboxes.
2) Enable/disable the protection,then select a cell in the worksheet to get the checkboxes to change. I used the Worksheet_SelectionChange event.

It would be nice to be able to change the status of the checkboxes directly from whatever event that is raised when protection is enabled/disabled.

Thanks very much!

Closed as a cross post

Hello and thanks for taking the time to look at my question!

I have a Worksheet_Change method in my sheet module which has suddenly begun working selectively. I have several target ranges in my worksheet that I need to watch for changes. One range of cells is all pull-down menus (a list of electrical loads through excel's validation), another two cells are looking at the name and location of an electrical panel.

When the user selects an electrical load, the worksheet_change method updates a legend of definitions. When the user enters a new name or location, the method updates similar fields elsewhere on the same sheet.

The problem is, excel has stopped executing worksheet_change when a load is chosen or location entered. It only executes when the aforementioned load and location are deleted. However, the name field triggers worksheet_change just fine!

I have attempted to put breakpoints on the worksheet_change method; Excel does not even execute the method as described above. I've tried breaking on the toggling of Application.EnableEvents (which occurs early on in the execution of worksheet_change). I currently have a global watch on Application.EnableEvents and I'm quite sure it's TRUE before I attempt to trigger a worksheet_change event.

My next step is to create a brand new workbook and copy my spreadsheet and code into it. I'll post the results of that experiment.

Thank you for your time and responses!
Len French

Firstly, I'd like to stress that I'm a VBA rookie, so I'd appreciate replies
will with details and explanations.

I'm trying to figure out how to build some VBA conditional formatting to:

1. test multiple cell criteria
2. overlap (when needed) the resulting conditional formatting

For example, in my worksheet, I want to test the content of columns N and O
for 3 different possible conditions and format the entire row accordingly:

if N is blank and O is a number, use a green font for the entire row
if N is "RET" and O is "RET", use a grey font for the entire row
if N is a number and O is "RET", use a red font for the entire row

Plus, I want to test the contents of columns V and W for a single condition
and format the entire row accordingly:

if V is "Internal" and W is "Internal", use a peach background for the
entire row

This latter test should be able to overlap with any of the first set.

And, lastly, I want to test the contents of column T for a single condition
and format the entire row accordingly:

if T <1 and the row above >=1, then draw a heavy blue line above the
entire row

And this third also should be able to overlap with any of the prior sets.

Finally, when any of these conditions change (so the criteria are no longer
met), I need the formatting to revert back to the default.

I know this will need to be triggered by a Worksheet_Change event, but have
been fiddling for a couple of days without much luck.

--
Susan
Technical Writer

I have tried searching this on the web but i cannot find the right event for this.

I need an Event which should trigger when the sheetname is changed. (i want to turn it back if people change them)

Worksheet_Change but it doesnt work like that, i believe cause its only ment for the excel cells.

Greetings and TIA for your time
I need your advice on the feasibility of an idea:
I'm using worksheet drawing objects as 'buttons" to run macros. I would like
to temporarily change the fill colours of the "buttons" when the mouse moves
over them before the mouse click to activate the macro. I thought that maybe
I could position an active X control behind each "button" and trigger a maro
to change button colours with the mouse move event. The control would idealy
be without any fill or lines. Also, I would need to identify the worksheet
shape object ("button") to re-color within the event handling procedure.
Maybe I colud group the control and shape object and then identify the shape
object from this association (maybe a .parent type thing)
Any advice will be greatly appreciated

--
David

I am attempting to use the below code, but when using the EnableEvents, the three lines of pivot tables do not update. If I remove the EnableEvents, then it enters a never ending loop. I need for when any one of the pivot tables is updated/changed, for the others to automatically update as well, then the rest of the code runs. Thanks for any help.

Private
Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Dim DAO As String
    DAO = Range("F12").Value

    'reset the formatting for the pivot tables on the Options tab after a refresh
    
    If (Target.Name = "DataAsOf") Or (Target.Name = "ThisYearPeriod") Or (Target.Name =
"LastYearPeriod") Then

    Application.EnableEvents = False
    Worksheets("Options").PivotTables("DataAsOf").RefreshTable
    Worksheets("Options").PivotTables("ThisYearPeriod").RefreshTable
    Worksheets("Options").PivotTables("LastYearPeriod").RefreshTable
    Application.EnableEvents = True
    
        Range("E10:F10").Copy
        Range("E12:F12,E32:F32,E52:F52").PasteSpecial Paste:=xlPasteFormats
        Worksheets("Options").Select
        Range("E76").Select
        Application.CutCopyMode = False

    End If


Hello again,

I have a worksheet containing Reuters live data being fetched with RtGet function:
=AdConvert(RtGet("IDN_RDF","MAL/SELECT1","IRGROW 5"),29,14)

Im trying to keep track of the changes in the information (Commodities Futures),
I need an event (in Visual Basic) that triggers when new content arrives,
Worksheet_Change does not work.

This thread relates to a question I asked here

Thanks to any help,
Lee

***** EDIT *****
Never mind... I'm an idiot

The problem was I deleted a column from the worksheet the same time the upgrade happened and I never changed the test column to reflect the now missing column... column 9 is now column 7

Please disrgard.

**********************Original post is below**************************

I have some VBA that simply move a row to the last row of another worksheet.... worked fine.

Recently, my company upgraded (without telling any one or allowing any one to test anything out) to version 2010. Now, none of my maros work. If I click on the View Macros, nothing is listed there. If I right click on the sheet tab and select "View Code".... the VBA is all still there.... but dosen't execute.

Here is the code of one of my macros

Private Sub
Worksheet_Change(ByVal Target As Range)
'stop anything re-triggering this event macro
Application.EnableEvents = False

On Error GoTo ErrHnd

'Check column 7 for test value of "NO"
If Target.Column = 9 And UCase(Target.Text) = "NO" Then
    Dim rngCell As Range
    Dim rngDest As Range
    Dim strRowAddr As String
    
    'save target row address
    strRowAddr = Target.Address
    
    'find next row in destination worksheet
    Set rngDest = Worksheets("Pending Schedule"). _
               Range("A" & CStr(Application.Rows.Count)).End(xlUp).Offset(1, 0)

    'cut the source row & paste to destination
    Target.EntireRow.Cut Destination:=rngDest
    'remove the cut/copy range marquee
    Application.CutCopyMode = False
     'delete the source row
    Worksheets("Completed Schedule").Range(strRowAddr).EntireRow.Delete _
        Shift:=xlUp
End If
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
Application.EnableEvents = True
End Sub
I have gone into File -> Options -> Trust Center -> Trust Center Settings-> Marco Settings and set "Enable all macros" as well as checking "Trust access to the VBA project object model".... with no luck.

Any help would be appreciated.

I have a document that calculates the cost of recipes based on data entered by the user from a list. Once a recipe total is calcuated, it sends that value to another worksheet, where they are all listed. This sheet is hidden and protected. Each month, the chefs will update the cost of the ingredients, which will in turn update the cost of all the recipes, which will, in turn, update the cost of the recipe on the hidden page. What I'm looking to do is make a notation (change the color of the cell, something similar) if a recipe cost changes on the hidden page. Then, after I update the POS system with the new recipe costs, clear the triggered event from the screen and be ready to start anew the next month. This is because some recipes will change and some won't.

Does anyone have an easy way to do this? I found some VBA that worked only on the worksheet, it didn't do anything when the value changed as a result of a formula (as in =Recipe!A1).

Thanks for your help.

I'm not a programmer, although I've written macros for several different programs starting with Lotus 123 2.01a. Excel VBA is still challenging to get my head around. Anyway,

My current project is a Excel 2003 / 2007 (in 2003 compatiblity mode) worksheet for in-house use. I've dilebratedly tried to keep code to a minimum and accomplished desired effects with conditional formatting, etc. I have, though used some code.

The "main sheet" has a Combo Box control (cmbTeam) with the following basic code "on change"

Private Sub cmbTeam_Change()
    Worksheets("ConstantData").Range("celSelectedTeam").Value = cmbTeam.Value
    If cmbTeam.Value = "" Then
        cmbTeam.BackColor = vbYellow
    Else
        cmbTeam.BackColor = vbWhite
    End If
End Sub
This works fine, most of the time. It always works when actually using the workbook. The only problem is when EXITING Excel. If I Exit (Alt-F4, etc) with the workbook open and choose "Yes" to "do you want to save changes", I get a Runtime Error 1004, "Method 'Worksheets" of object '_global' failed. If I save and close the workbook, then exit, I do NOT get this error.

I have found a couple of ways of preventing the error, or preventing it from interferring, the easiest being

at the top of the Sub. I can also force the Sub to Activate the worksheet being modified (ConstantData) and then Activate the
main worksheet so the code looks like this

Private Sub cmbTeam_Change()
Application.ScreenUpdating = False
Worksheets("ConstantData").Activate
Worksheets("ConstantData").Range("celSelectedTeam").Value = cmbTeam.Value
Worksheets("Assumptions").Activate
Application.ScreenUpdating = True
    If cmbTeam.Value = "" Then
       cmbTeam.BackColor = vbYellow
    Else
       cmbTeam.BackColor = vbWhite
    End If
End Sub
My question, though, is why the bleep is the Sub even running and causing an error when I Exit and Save? It has already run and NOT caused any error when the actual event that is supposed to trigger the code occured. And it only happens when I Exit and answer "Yes" to save. Not when I save and close the workbook, nor when I close the workbook w/o saving? I think it might have to do with Excel 2007 forced-recacluating of 2003-format worksheets?

Before you ask, here is the code that runs OnClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Assumptions").Select
    Application.Goto Reference:="celHome"
    RstHome
    ' Call sub to Restore CTRL+HOME to default
    Application.EnableEvents = True
End Sub
 
Sub RstHome()
'Restore CTRL+HOME to default action
    Application.OnKey "^{HOME}"
End Sub
Since I've been able to make Excel ignore the error with OnErrorResume, this isn't critical. But it's bugging the bleep outta me. I'd like to know why it is happening so I can avoid it in the future, if possible.

Thanks!
- Sequoia

also posted at:http://www.ozgrid.com/forum/showthread.php?t=145662 (and resolved there)... it's a big web out there ...

My first visit to this forum, so please be nice! Just hope someone can help. I've got a pretty complex VBA project that I've put together containing around 150 subroutines, 15 different user forms and a whole heap of untidiness thanks to the organic way in which I've developed it over the past 12 months or so.

I've just stumbled across two issues in my final checks on the project:

1. I've got a combobox on tab 2 of an 8 tab multipage. Each tab has a button at the bottom which advances the user to the next tab, the previous page then being locked down and inaccessible. I set up a subroutine counter, so that every routine logged when it was accessed. For some reason the combobox_change() routine for this object, once activated, continues to be accessed long after the value is selected and the relevant page disabled. By the time the form completes its running, this routine has potentially been accessed 500-1000 times!

I've managed to nip this in the bud by adding combobox.value="" once the combobox input has been used and recorded on the data storage worksheet in the underlying XLS, but it's rather a clumsy solution to a problem that - as far as I can see - shouldn't exist.

The combobox is definitely not changed by any other events or code lines, as I've searched the entire project, and it doesn't have a cell-link, so there's no question of an underlying cell being changed either. The Change event is supposed to trigger when there's a change of value, but Msgbox reports confirm that the Event is being triggered when there has in fact been no underlying change. Most puzzling of all, combobox.enabled=false and combobox.locked=true don't stop the macro activating either!

Is this a known bug, or is there some underlying thing going on here!?

2. I've just sent my project out for wider examination, and it's immediately been flagged to me that it doesn't work on Mac Excel. Are there simple steps required to convert the sheet, or is the possibility of a multi-platform project pretty much dead in the water?

For info, I'm very much an amateur excel enthusiast with no formal training who honed his programming skills on the ZX Spectrum (10 Print "Bum", 20 Goto 10), which probably goes a long way to explaining why my sheet is somewhere over 20,000 lines of code!

I use event code within the worksheet, workbook and application classes and I understand that there are events that will trigger responses within all 3 classes - an example would be a range selection change ("SelectionChange" within a worksheet object, "SheetSelectionChange" within a workbook or application object). I further understand the order of events is from least significant object to the most significant object (so the worksheet event code will go first, followed by the workbook event code and finally the application event code). Finally, I believe that I am on the right path in trying to leverage the "Application.EnableEvents" property (set it to false to stop all responses to event triggering and back to true when you want the code to pay attention to event triggering). It's my belief that I should be able to trap and respond to an event at the worksheet level, and stop the event from being trapped and responded to at the workbook and/or application level.

So far, I have been unsuccessful. I have attached 2 example workbooks that contain the bones of the code with which I'm tinkering - Test-Event-Enable-1 puts event code in the worksheet code in the sheet module, workbook code in ThisWorkbook and application code in a class module; Test-Event-Enable-2 puts event code for all objects into class modues. SetupEvents / SetupAppEvents must be executed to turn the turn event handling on and I simply incorporate msgbox code to reflect what event code module is being executed when I select different ranges on a worksheet.

Would anyone have any advice / suggestions (if this is not possible, I'd even like to know that). Many thx, BFRG

A few problems I'm having. I'm very new to VB and this code so far was put together with help from you guys! This code calculates a value for column K when you punch in a number in column J (in any rows 17 to 116, separately). The first problem is that when you delete a value out of J, the value stays in K for some rows (where it should only contain a value if J does). I'm guessing it has something to do with the triggering event that I put in bold. Secondly, I need to be able to protect row K at all times from deletion or entering another value. It ONLY should calculate a value based on J. So I need to use the module (below the code) and I want to integrate it into the code or call it in a module. It's not working properly because it prevents ANY and ALL changes, even the ones caused by new values entered in J (which should be allowed). It should only prevent deletion or manually entering a value on the worksheet. So the bottom line is that when you enter a value into J, unless column E says "Annuity" it will calculate using the formula given. If you delete J, K should go away as well. And at no time can you delete or manually change K. And when you switch values in E, it must reloop to check if it says "Annuity" or not, and if not, it must clear J (which clears K).

And lastly, this thing is brutally slow, so maybe there's an easier way to write it. Sorry for all the detail, but I think it's an easy fix for someone who knows programming, but i don't know how to put it together! NML Inventory is the Activesheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Const pw As String = "password"
Dim Rng As Range, c As Range, Rng2 As Range, Rng3 As Range, i As Long, lastrow As Long
Set Rng = Range("E17:E116")
Set Rng2 = Range("J17:J116")
Set Rng3 = Range("K17:K116")
If Not Intersect(Target, Rng2) Is Nothing Then
    Sheets("NML Inventory").Select
    lastrow = Range("J65536").End(xlUp).Row
        For i = 17 To lastrow
        If Cells(i, 10) = 0 Or Cells(i, 10) = "" Or Cells(i, 5) <> "Annuity" Then
        Cells(i, 11).ClearContents
        Else: Cells(i, 11) = Sheets("INPUT").Cells(28, 13) * Sheets("NML Inventory").Cells(i, 10)
        End If
    Next
Else
    If Not Intersect(Target, Rng) Is Nothing Then
        ActiveSheet.Unprotect pw
        For Each c In Rng
            Select Case c.Value
                Case "Annuity"
                    c.Offset(0, 3).ClearContents
                    c.Offset(0, 4).ClearContents
                Case "DI", "LTC"
                    c.Offset(0, 3).ClearContents
                    c.Offset(0, 5).ClearContents
                Case Else
                    c.Offset(0, 5).ClearContents
            End Select
        Next c
        ActiveSheet.Protect pw, AllowSorting:=True, AllowFiltering:=True
    End If
End If
End Sub
Module:

Public Sub Undoit()
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub


when a customer is chosen on my order worksheet, the change event triggers
the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

' Column for Notes is AC (29)
' Starting row is 8

If Target.Column = 3 Then
If Target.Row = 8 Then
Dim n, x As Range, off As Integer, c As String
With Sheets("Work Order")
Set x = Range("CustomerList")
End With
off = 7 + Application.WorksheetFunction.Match(Range("C8"), x, 0)
c = "$AC$" & off
With Sheets("Customers")
Set n = .Range(.Range(c))
End With
' popup notes about customer
MsgBox n.Value
End If
End If

End Sub



what it should do is look in C8, and then find at which location that
customer is in the list (Thanks to Tom for the Match help!). Then add 7 to it
- as the first entry in my customer list is in row 8.

i am getting an error on
Set x = Range("CustomerList")

CustomerList is a named list in my workbook - in fact it is the source for
cell C8

what am i missing?

thanks!

J

Hi guys,

I have an event that triggers when the user changes a cell in a worksheet.

The Subroutine checks if cell changed is in column A and greater than row 13. Is there a smarter way to do this rather than using Split?

How do you get the next Target.Address? For example, if I change cell A14 Target.Address = $A$14 then the next Target.Address would be $A$15.

Private Sub Worksheet_Change(ByVal Target As Range)

    x = Split(Target.Address, "$")

    If x(1) = "A" And CInt(x(2)) > 13 Then
       ' Get next target address
       ' Execute other code
    EndIf

End Sub
Thanks for the help guys.

George

Firtsly , apologies for the Title but it's hard to define the problem:-

I need to create a shelf summary worksheet that calulates the number of shelves of type, width and length from many worksheets representing shop fittings. The end result is a matrix:-

Shelf Depth
Shelf Type / Length 450 600 750
============================
Type A Short
Type A Medium
Type A Long
Type B Short
Type B Medium
Type B Long
Type C Short
Type C Medium
Type C Long

Ther are 3 types shelf types, 3 lengths and 10 depths.

This is calculated from many worksheets. Each of these worksheets contain a cell that defines the type (A, B or C), a cell for each side of the fitting that contains the width (450, 750 etc). Below each side is information about each shelf including it's length (S, M or L).

I add the worksheets to the spreadshet programatically, I don't know how many there will be but I know they will all be added in sequence from a template worksheet and they will begin with IG or WG.

I have already managed to do a sum on the cells "=SUM(IG 1!C20:WG Blank!C20)" (Not sure of syntax as doing from memory at home) which works. What I can't do is to add the conditional statement that filters the type and depth from the other cells. I tried referring to them using "=IF(IG 1!C20,"=450":WG Blank!C20,"=450") but it causes an error.

Is it possible?

I can quite easily do this using VBA, but the user may need to manually change the shelf depths and I don't want them to have to initiate anything to recalculate the totals. I could trigger it with an event but it would be overkill and besides which if Excel can do it, why write a program.

I am using the following code, based on help received in this forum. The purpose is to progressively unhide sheets as they are completed by the user selecting from the nominated cell. This process must be foolproof and either be automatic or involve no more than selecting a cell value (Yes)

I am now trying to get the progression from 'property details 1' to 'property details 2' which is more difficult. The user can select the number of property sheets that will be reuired in cell 'service details'!D15

Within the sheet the user can fill in up to 49 rows (65-113) of property details, each line is revealed through conditional formatting on completion of the previous, subject to a maximum determined by the user entering the number of properties in cell E7. This can be any number 1 -49.

Once the maximum is reached the next line indicates no more information is required.

At this stage I would like the next property details sheet to be unhidden - the problem is I'm not capable of working out how to get the information from within this variable range to trigger the event.

Any ideas?

Ed

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_Range As String = "C13:C13" 'change on each sheet as required
Application.EnableEvents = False
Application.ScreenUpdating = False
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_Range)) Is Nothing Then
Dim ws As Worksheet
Dim sWord As String
sWord = Sheets("Notes - read me").Range("c13").Value 'Change as required
For Each ws In Worksheets
Next
Select Case sWord
Case "yes" 'Adjust True or False according to sheet
Sheets("service details").Visible = True
Sheets("property details 1").Visible = False
Sheets("property details 2").Visible = False
Sheets("property details 3").Visible = False
Sheets("property details 4").Visible = False
Sheets("property details 5").Visible = False
Sheets("property details 6").Visible = False
Sheets("property details 7").Visible = False
Sheets("Data 1").Visible = False
Case "no", "" 'Adjust True or False according to sheet
Sheets("service details").Visible = False
Sheets("property details 1").Visible = False
Sheets("property details 2").Visible = False
Sheets("property details 3").Visible = False
Sheets("property details 4").Visible = False
Sheets("property details 5").Visible = False
Sheets("property details 6").Visible = False
Sheets("property details 7").Visible = False
Sheets("Data 1").Visible = False
End Select
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Sub ReSet()
Application.EnableEvents = True
End Sub


So here is my problem, i have my code almost completely done. The fourth column, the status column, is set to auto copy to other tabs in the workbook based on what the status is.

However, the loop i have to delete it from the current sheet once it auto-copies is not working. it copies onto the other tab fine, i just need help to make it delete once column d changes!

Here is my code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngSrc As Range
Dim rngDest As Range
Dim wsDest As Worksheet
Dim n As Double

On Error GoTo ErrHnd

'disable events - so that changes made by this code
'do not re-trigger it
Application.EnableEvents = False

'test if the changed cell is in columns A to J
'must action changes in associated data, not just status col.
If Target.Column >= 1 And Target.Column < 11 Then
    Set rngSrc = ActiveSheet.Range("D" & CStr(Target.Row))
    'actions based on word entered in column D
    Select Case rngSrc.Text
        Case "HOLD"
            Set rngDest = Worksheets("HOLD") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
            'Set rngDest = rngDest.Offset(1, 0).Offset(1, 0)
        Case "DEAD"
            Set rngDest = Worksheets("DEAD") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "LOST"
            Set rngDest = Worksheets("LOST") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "FUNDING"
            Set rngDest = Worksheets("FUNDING") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "PREFUNDING"
            Set rngDest = Worksheets("PREFUNDING") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "CONTRACT OUT"
            Set rngDest = Worksheets("CONTRACT OUT") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "SUBSELL"
            Set rngDest = Worksheets("SUBSELL") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "BCA SUBMISSION"
            Set rngDest = Worksheets("BCA SUBMISSION") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "STARTER"
            Set rngDest = Worksheets("STARTER") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)

        Case Else
        'no match - so error out
        GoTo ErrHnd
    End Select
    


    
    'move the data to the required worksheet
    'copy columns A to J - adjust as appropriate
    rngSrc.Offset(0, -3).Resize(1, 10).Copy _
            Destination:=rngDest
    
    'now delete it from any other destination worksheet
    'loop through all worksheets
    'test if it is one of the eight named destination sheets
    'but not the one we just copied it to
    For Each wsDest In ActiveWorkbook.Worksheets()
        If (wsDest.Name = "HOLD" _
                    Or wsDest.Name = "DEAD" _
                    Or wsDest.Name = "LOST" _
                    Or wsDest.Name = "SUBSELL" _
                    Or wsDest.Name = "CONTRACT OUT" _
                    Or wsDest.Name = "PREFUNDING" _
                    Or wsDest.Name = "FUNDING" _
                    Or wsDest.Name = "STARTER") _
                    And wsDest.Name <> rngDest.Worksheet.Name Then
                    
            'find matching ID - work from end of used range
            'just in case there is a duplicate entry
            For n = wsDest.UsedRange.Rows.Count To 10 Step -1
                'find matching Task
                If wsDest.Range("A" & CStr(n)).Text = _
                                    rngSrc.Offset(0, -3).Text Then
                    'match found, so delete row
                    wsDest.Range("A" & CStr(n)).EntireRow.Delete
                End If
            Next n
        End If
    Next wsDest
    
    'now test the worksheet we just copied to - for duplicates
    'start at row above the one we copied to
    With rngDest.Worksheet
        For n = rngDest.Row - 1 To 2 Step -1
            If .Range("A" & CStr(n)).Text = _
                        rngSrc.Offset(0, -3).Text Then
                'delete row
               .Range("A" & CStr(n)).EntireRow.Delete
            End If
        Next n
    End With
End If


're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True


I have a spreadsheet control inside of a userform. I can generally access this spreadsheet and do what I need to do with it. My problem is that I need to monitor it for the cell change event. I normally accomplish this with:

Private
Sub Worksheet_Change(ByVal Target As Range)
    MsgBox (Target.Row & ", " & Target.Cells)
End Sub
in the 'code' portion of the particular worksheet that I want to monitor. I have the same thing in the spreadsheet object that I have inserted onto the userform, but the event never gets triggered.

Any ideas?


No luck finding an answer? You could always try Google.