Excel 2007 - cannot get worksheet change macro to work

[ptcane]

Can anyone tell me why this VBA doesn't work? - i'm sure i have
managed to trigger macros before with the top code but it doesn't seem
to work now.

I have a data feed which updates 16 columns in excel (alternatively if
i turn off the data feed, am i right in thinking the top code should
run if i highlighted 16 columns and pressed delete?)

In the code window for the sheet to which the data is going, i have
the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

Run "CopyRtoZ"

Application.EnableEvents = True

End Sub

In Module1 i have:

Sub CopyRtoZ()
'
' CopyRtoZ Macro
'
Range("R5:R6").Select
Selection.Copy
Range("Z5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

I don't understand why the above does not trigger a copy R5 and R6 to
Z5 and Z6.

I know very little about VBA, i am using Excel 2007 and saving as
a .xlsm file as this is what it tells me to do. The security options i
can find are all on low.

thanks for any help.

 

[Jim Rech]

I didn't run your code but are macros enabled? In Excel 2007 they won't be
if the workbook has a File Open password or if it has Workbook protection.

Unrelated comments fwiw..

If the sub is in the same workbook you can just call it by name:

CopyRtoZ

Also sub CopyRtoZ doesn't need to do any selecting:

Sub CopyRtoZ()
Range("R5:R6").Copy
Range("Z5").PasteSpecial Paste:=xlPasteValues
End Sub


--
Jim
| Can anyone tell me why this VBA doesn't work? - i'm sure i have
| managed to trigger macros before with the top code but it doesn't seem
| to work now.
|
| I have a data feed which updates 16 columns in excel (alternatively if
| i turn off the data feed, am i right in thinking the top code should
| run if i highlighted 16 columns and pressed delete?)
|
| In the code window for the sheet to which the data is going, i have
| the following:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
|
| If Target.Columns.Count <> 16 Then Exit Sub
| Application.EnableEvents = False
|
| Run "CopyRtoZ"
|
| Application.EnableEvents = True
|
| End Sub
|
| In Module1 i have:
|
| Sub CopyRtoZ()
| '
| ' CopyRtoZ Macro
| '
| Range("R5:R6").Select
| Selection.Copy
| Range("Z5").Select
| Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
| SkipBlanks _
| :=False, Transpose:=False
| End Sub
|
| I don't understand why the above does not trigger a copy R5 and R6 to
| Z5 and Z6.
|
| I know very little about VBA, i am using Excel 2007 and saving as
| a .xlsm file as this is what it tells me to do. The security options i
| can find are all on low.
|
| thanks for any help.
|

 

[ptcane]

To eliminate the code being the issue i tried adding the code as given
at

http://www.automateexcel.com/index.php/2004/09/13/excel_vba_run_a_macro_when_a_cell_change

however when i change cell 1 nothing happens. i have tried saving and
re-opening the worksheet. therefore it seems an excel issue, however i
am given no warnings that macros have been disabled.

recorded macros (eg copy cell 1 to cell 2) seem to run and work fine,
but worksheet change macros dont seem to want to work.

 

[ptcane]

solved the issue - it was my antivirus software, as explained here:

http://www.developersdex.com/vb/message.asp?p=2677&r=5942622

 

[ptcane]

well it worked once now has stopped again. how can i get rid of any
security measures in excel so i don't have to worry about these being
the issue?

 

[Jim Rech]

it was my antivirus software

The gist of which is that, if the workbook is protected, you have to have
virus software installed and active that can scan it.

--
Jim
| solved the issue - it was my antivirus software, as explained here:
|
| http://www.developersdex.com/vb/message.asp?p=2677&r=5942622
|
|