Runtime Error 9 When Opening Up Another Excel File

paulman1976

New Member
Joined
Jun 26, 2013
Messages
43
I have written a simple VBA Code in one workbook, that works well. However, if I am still in that workbook and open up a new workbook (which hasn't got any macros or VBA), I get the Runtime Error 9 message.

It highlights in the VBA Code the following line: If Sheets("IFAs").Range("F3").Value < 21 Then

The whole VBA Code is as follows:

Private Sub Worksheet_Calculate()

If Sheets("IFAs").Range("F3").Value < 21 Then
MsgBox "Need to Re-stock Brochures - Order now"

End If

If Sheets("IFAs").Range("G3").Value < 21 Then
MsgBox "Need to Re-stock A5 Enquiry Leaflets - Order now"

End If

End Sub


Can someone help as it is really annoying when I am still working within this file and need to open another one and the error message keeps popping up.

Many thanks.

Phil.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
Place the following statements and the beginning and end of your macro, respectively, in order to skip any errors:


Code:
On Error Resume Next


Code:
On Error GoTo 0
 

Forum statistics

Threads
1,136,969
Messages
5,678,890
Members
419,787
Latest member
juanam

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