Worksheet_SelectionChange or Worksheet_Change not firing

dazhoop

New Member
Joined
Oct 10, 2008
Messages
22
Dear All,

I have a software package that dynamically updates a linked spreadsheet, sometimes several times a second.

My purpose is to capture the current data values of a given range once or twice a second, although the precise frequency doesn't have to be spot on, and carry out some calculations and record the output, along with the current date/time.

In an ideal world I would like to have used a worksheet_change event on the interactive spreadsheet, but the vendor has confirmed that this function will not work with the spreadsheet's interaction with its software.

So, I thought I would have a crack at having the interactive worksheet open and have a looping copy/paste to a second worksheet, and then have the worksheet_change event trigger within the second, non-interactive worksheet.

Whilst I have built VBA and made progress using a variety of examples nabbed from the vast resource of posts on this site, I am having trouble making the worksheet_change / worksheet_selectionchange trigger without having to actually click on a cell in the second, non-interactive worksheet.

It does the copy / paste and updates the values in the cells, but nothing happens until I click on a cell, and sometimes not all of the data is captured until the next change happens and I click again on a cell.

Enough babbling - here is my code:

WORKSHEET 1 of File 1

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub SleepAndCopy()
Do
Call CopyPasteRange
Sleep 500 'this value is in milliseconds ie 1000 for 1 second
Loop
End Sub

Sub CopyPasteRange()

'Dim LstRow As String

Workbooks("200902021-1").Sheets("Interactive").Activate
Range("D6:I9").Copy

Workbooks("200902021-1").Sheets("DataCapture").Activate
LstRow = Application.Range("C6:H9")
ActiveCell.PasteSpecial (xlPasteValues)

End Sub

WORKSHEET2 of File 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "E6" Then
Application.EnableEvents = False
Range("j" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("k" & Rows.Count).End(xlUp)(2).Value = Now
Range("l" & Rows.Count).End(xlUp)(2).Value = "=SUM(C7:E7)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "F6" Then
Application.EnableEvents = False
Range("m" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("n" & Rows.Count).End(xlUp)(2).Value = Now
Range("o" & Rows.Count).End(xlUp)(2).Value = "=SUM(F7:H7)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "E8" Then
Application.EnableEvents = False
Range("p" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("q" & Rows.Count).End(xlUp)(2).Value = Now
Range("r" & Rows.Count).End(xlUp)(2).Value = "=SUM(C9:E9)"
Application.EnableEvents = True
End If
If Target.Address(0, 0) = "F8" Then
Application.EnableEvents = False
Range("s" & Rows.Count).End(xlUp)(2).Value = Target.Value
Range("t" & Rows.Count).End(xlUp)(2).Value = Now
Range("u" & Rows.Count).End(xlUp)(2).Value = "=SUM(F9:H9)"
Application.EnableEvents = True
End If
End Sub

What do I actually want to achieve - I want to copy the data values in range D6:I9, Sheet1 and paste them to range C6:H9 in Sheet2.

I then want Sheet2 to realise that the data values have changed, then copy the value from between 1 and 4 cells, perform a 3 cell sum on 1-4 ranges, and enter the NOW() value 1-4 times, dependant on which cells actually changed.

I think that is a good summary of what I am hoping to do, and if anyone can spot the errors of my ways, then advice will be gratefully received. If an example of input and current generated output is required, then that could be supplied to.

Thanks for your time.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Might you have better results with a WorkSheet_Calculate event instead of worksheet_change?
 
Upvote 0
Try using

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

which should fire when a cell is changed, as opposed to clicking in a cell.

In fact, you could also try this on the 'interactive' sheet, without having to copy data to another sheet.
 
Upvote 0
Thanks for taking the time to assist.

The use of worksheet_* simply does not work with my interactive worksheet - I have already tried some simpler code, and the software vendor themselves confim that that function will not recognise the changes that the software makes to the interactive spreadsheet cells.

Manually editing Worksheet 2
If I leave it as worksheet_selectionchange, I see that each time I click on any of the four Target cells detailed in my "If" statements, then it executes the actions within the "If " statement, regardless of whether the value in those key cells has changed or not.

If I alter the code to worksheet_change and then manually alter my four Target cells, then the If statement executes as expected .... so this is good and proves that worksheet_change is what I nee to be using.

But, if I manually copy the range from Worksheet1 and paste it into Worksheet2, nothing happens and the worksheet_change doesn't trigger.

I have noticed that when I am manually changing values in worksheet2 I am getting formula warnings in the cells I am writing my =sum calculations to, and so I don't know if this might have anything to do with my problem?

In case it does, D6:I6 and D8:I8 are positive numeric values to two decimal places, and D7:I7 and D9:I9 are numeric values to two decimal places, and a currency symbol. I have tried applying the correct "Currency" cell format to the source, paste target and =sum target cells, but still get the same formula warning.

Thanks,

Darren
 
Upvote 0
A couple of things spring to mind:

Rich (BB code):
Workbooks("200902021-1").Sheets("DataCapture").Activate
LstRow = Application.Range("C6:H9")
ActiveCell.PasteSpecial (xlPasteValues)

What is the active cell here? You haven't selected a cell as far as I can see. Where do you actually want to paste to?

In the event code it should be

Rich (BB code):
Range("l" & Rows.Count).End(xlUp)(2).Formula = "=SUM(C7:E7)"
 
Upvote 0
I would like to paste from / to:

Copy Range D6:I9 of Worksheet:Interactive

to

Range C6:H9 of Worksheet:DataCapture

I guess by active cell you mean what cell would I like the automated process to be in when it does the paste to Worksheet:DataCapture? I would like it to be in cell C6 .... I thought that was what my entry:

LstRow = Application.Range("C6:H9")

was doing - is it not, then? How should I do so?

I have also updated each of the formulas in the worksheet_change code as suggested in my DataCapture worksheet.

I am perplexed as to why me manually pasting the value from any single cell into one of the four key cells triggers the If functions, but pasting a range does not trigger the If statement?
 
Upvote 0
Try

Code:
Workbooks("200902021-1").Sheets("DataCapture").Range("C6").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
VoG - Precisely what I'm looking for in active cell selction, thanks ... but it does not trigger a worksheet_change event.

I did do another search of the forum for "worksheet_change" + "paste", and this came back:

http://www.mrexcel.com/forum/showthread.php?t=58826&page=2

I am of the opinion that I am struggling with the same problem, although as I am using MS Excel 2007 one would have hoped they might have nipped it in the bud.

Do you think I am best served trying to incorporate "Andrew Poulsom's" suggestions in this thread?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top