Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 - cannot get worksheet change macro to work
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 - cannot get worksheet change macro to work
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.. Run "CopyRtoZ" 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 wrote in message oups.com... | 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. | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 - cannot get worksheet change macro to work
To eliminate the code being the issue i tried adding the code as given
at http://www.automateexcel.com/index.p..._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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 - cannot get worksheet change macro to work
solved the issue - it was my antivirus software, as explained he
http://www.developersdex.com/vb/mess...2677&r=5942622 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 - cannot get worksheet change macro to work
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 - cannot get worksheet change macro to work
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 wrote in message ups.com... | solved the issue - it was my antivirus software, as explained he | | http://www.developersdex.com/vb/mess...2677&r=5942622 | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change direction worksheet excel 2007 right to left | Excel Discussion (Misc queries) | |||
why doesn't this simple macro work in excel 2007? | Excel Discussion (Misc queries) | |||
Worksheet.Change macro doesn't run in 2007 | Excel Programming | |||
Macro work in Excel 2003 but not Excel 2007 | Excel Programming | |||
Excel 2007 macro does not work | Excel Programming |