Running macro at set time disables range selection

batyushki

New Member
Joined
Mar 11, 2004
Messages
4
I have a macro that needs to run at a set time every day. But when it runs, range.select is disabled -- all of the macro's selections occur in the active cell.

Here is how it is set up:

1. My PERSONAL file has an on_time statement that opens the spreadsheet containing the macro at a fixed time.
2. The spreadsheet with the macro has a workbook_open statement that calls the macro.

Why is selection disabled? Here is the code.

PERSONAL.xls, This Workbook code:


Private Sub Workbook_Open()
Application.OnTime TimeValue("8:37 AM"), "autorun_rp_export"
End Sub

Sub autorun_rp_export()
Application.DisplayAlerts = False
Workbooks.Open FileName:= _
"\\psafilc\medcadat\merch\International\Distribution\NewAllocation\RP Files\ExportRP.xls" _
, ReadOnly:=True
Application.DisplayAlerts = True
End Sub


Then in the spreadsheet with the macro:


Private Sub Workbook_Open()
Call export_rp
End Sub

Sub export_rp()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.Calculate

currbook = ActiveWorkbook.Name

Worksheets("Export").Activate
ActiveSheet.Unprotect
Range("B11:D10000").ClearContents

Range("G:G").ClearContents
Range("G11").Select

... etc.

This last select statement, and all other select statements in the macro, don't work. Excel doesn't return an error, but the macro keeps working in the same active cell.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Code:
Private Sub Workbook_Open()

.... does not work in a worksheet module. You probably mean ...Private
Code:
Sub Worksheet_Activate()
.. or whatever.
 

batyushki

New Member
Joined
Mar 11, 2004
Messages
4
Actually that piece of code is in the Workbook part of the code, so it should work. When the workbook opens it is automatically calling the macro that is in the module.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
That is not what you said. Quote "Then in the spreadsheet with the macro: ..."

Either way you seem to have put it in *twice* somehow. Excel will only run a single instance .. the first one it finds in the ThisWorkbook code module.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,516
Messages
5,596,617
Members
414,081
Latest member
Subaru_Steve

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
Top