Worksheet On_Change question

[clifftech]

I have a worksheet range that when the user changes any cell in the range the Worksheet_change(ByVal Target As Range) event triggers property changes to a command button..which works fine. The problem is I also have a userform that can make changes to cells within the range but userform changes do not trigger the Worksheet_Change event. Is it possible to trigger a Worksheet_change event from a userform change or do I have to handle changes made by userforms differently.

Thanks

 

[SkipVought]

Hi,

What is the code that causes changes to the sheet range from the userform?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

Thanks for the quick reply.

Here is a sample of the userform code I am using:

Private Sub cmdAdd_Click()
Set ws = Worksheets("OperatingCosts")

ws.Range("AA3").Value = Me.txtSalCap.Value
ws.Range("AA4").Value = Me.txtSalCo.Value
ws.Range("AA5").Value = Me.txtSalFltEng.Value
ws.Range("AA6").Value = Me.txtSalBenefits.Value
...
...
...
End sub

txtSalCap, txtSalCo, etc are userform textboxes.

 

[SkipVought]

Each one of those statements triggers a Worksheet_Change event on sheet OperatingCosts.

Do you have any code that assigns Application.EnableEvents?

What is your Worsheet_Change code in OperatingCosts?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

Skip,

I do not have any code for Application.EnableEvents.

The code I'm using for Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AA3:AA6")) Is Nothing Then
With Worksheets(2).cmdDefault609
.BackColor = &HC0C0FF
End With
End If
...
...
End Sub

 

[SkipVought]

Have you put a break in your code in order to STEP and see what happening in the cmdAdd_Click procedure?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

I put a break in and stepped throught the code. The values are being changed by the values in the textboxes. I'm not sure what else to look for.

 

[SkipVought]

If you open the code window where this code is...

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AA3:AA6")) Is Nothing Then
    With Worksheets(2).cmdDefault609
    .BackColor = &HC0C0FF
    End With
End If
...
...
End Sub

and observe the SHEET OBJECT that is highlighted in the VBA Project Explorer, and RIGHT-CLICK on that Sheet Object, selecting VIEW OBJECT, is this the sheet that has your range in column AA3:AA6?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

Yes. It is the Operating Cost spreadsheet that has the range AA3:AA6

 

[SkipVought]

Then there is no reason that THAT Worksheet_Change event would not fire each time a value is changed on THAT sheet.

How do you know that, "userform changes do not trigger the Worksheet_Change event."?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

I know it works when I manually change a cell since it changes the background color of the command button.

When I open the userform and change a textbox value, which is linked to the one of the cells, it does not change the background color of the command button.

 

[SkipVought]

I've run a test, with a userform, textbox and button, and the change from the userform DOES trigger the Worksheet_Change event, changing the backcolor, in a button on the SECOND sheet in the workbook.

You ought to be able to put a break on the FIRST statement in cmdAdd_Click, and step thru to the end, seeing the event fire and the code in the event execute.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

If you wish, send me your workbook and I'll take a look...

ii36250 at bellhelicopter dot textron dot com

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[clifftech]

Thanks for the offer.

I'm going to try with a new workbook first and try to get it working. I'll let you know.

 

[clifftech]

Skip,

I found the problem. The range of cells that I was monitoring for change were actually referencing other cells. A1 was referencing cell AA1. So even though the value in A1 was changing, the formula + AA1 was not changing so it was not triggering a change. Dumb mistake.

When you told me you had it working I figured I was missing something obvious. Thanks for your time.