Comments, VBA & Min/Max

[RSprengers]

I have the following (test)code in a workbook (no module)
What I'm trying to do is this:

I have 5 cells that are automatically updating (C3:G3) through DDE connection. These five cells have a comment (each one is different)

Cell A3 contains the Min. value from C3:G3 (=Min(C3:G3))

Now I want the comment in A3 to be the same as the comment in the cell that it refers to (The cell with the minimum value)

It works the first time I run the macro, but if I start changing the data (either manually or by DDE) A3 updates the minimum value, but not the corresponding comment.

What am I doing wrong?


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

Sub test()
Dim newcomment As String
For Each cell In Range("c3:g3")
If cell.Value = Range("a3").Value Then
newcomment = cell.Comment.Text
Range("A3").ClearComments
Range("A3").AddComment (newcomment)
End If
Next cell
End Sub

 

[sykes]

Hi

What does the line:

 If Not (Intersect(Target, Range("A3")) Is Nothing) Then

do?

This, I think, is testing for a change in A3, but is testing false, and stopping your "test" routing from running, by the looks of it.

 

[RSprengers]

Yes, it is testing for a change in A3

The only time I need the comment in A3 to change is if the value in A3 changes. (In other words, if the minimum value in the range of cells changes, than I want to check which cell now contains the minimum value and than copy the comment of that cell to cell A3)

If I didn't do this, it would execute the macro every time the sheet changes (which is a lot if you use a DDE connection)

 

[RSprengers]

No-one ???

 

[sykes]

As I said, I think the intersect line is always proving false, and halting the code in it's tracks.

As you're using the worksheet_change event (which I assume is triggered by DDE changes to the data as well) that bit of code will always be running anyway in response to sheet changes from DDE.

What I'd suggest is to have a "holder" which reflects the value of A3. Then when the sheet changes, you check the value of A3 against it's holder, and if there's a discrepency (i.e. a change in A3) ,update the holder to the new A3 value then run your "test" code:

Put the holder at the very top of your code sheet:

Dim holder As Long

Change your worksheet_change code thus:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not holder = [a3].Value Then
  holder = [a3].Value
Call test
End If
Application.EnableEvents = True
End Sub

...... and I've put a "test running" message into your "test" procedure, just to see whilst you're playing with it when "test" actually gets fired:

Sub test()
MsgBox "test running"
Dim newcomment As String
For Each cell In Range("c3:g3")
If cell.Value = Range("a3").Value Then
newcomment = cell.Comment.Text
Range("A3").ClearComments
Range("A3").AddComment (newcomment)
End If
Next cell
End Sub

Obviously, delete the messagebox line once your happy with the result.

 

[RSprengers]

Thanks !!!!
This works.

I'm afraid that since my workbook updates a lot of data through DDE, it will become very slow because it will run the change event for every change in the workbook....

I will test it, but in case it becomes slow, is there something I can change in the code so it will only run the event code if the specified cell (A3) changes in value?

Thanks

 

[RSprengers]

Next problem....

As you already mentioned....

The workbook does not trigger the event because of changes due to DDE.....
Any solutions?

 

[sykes]

mmmmmmmm

I've been having a look at one of Just_Jon's threads, and he ended up using the worksheet_calculate event instead:

So, keep everything else the same, delete the code in the worksheet_change event, and put this into the worksheet_calculate event:

Private Sub Worksheet_Calculate()
Application.Calculation = xlCalculationManual
If Not holder = [a3].Value Then
  holder = [a3].Value
 test
End If
Application.Calculation = xlCalculationAutomatic
End Sub

I've got no DDE set up, so can't test it, but it should fire in response to your DDE changes when they force the sheet to re-calculate.

Here's J_J's thread :
http://www.mrexcel.com/board2/viewt...days=0&postorder=asc&highlight=rslinx&start=0