MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Excel VBA Errors on Saving Workbook

  Asked By: Carole    Date: Mar 26    Category: MS Office    Views: 2524

I have developed an Excel workbook with underlying VBA modules and
it all appears to function correctly.

However, when I try to save my application with a different filename
using the 'Save As' option, I get presented with several errors of
the following form:

Run-time error '1004': Select method of Range class failed

and if I Debug it, the problem is in the "Range(rangeText).Select"
line in the following sub-module:

Private Sub FillRange(rangeText, colour)

ActiveSheet.Unprotect Password:="noedit" 'Unprotect inputs
With Selection.Interior
.ColorIndex = colour
.Pattern = xlSolid
End With
ActiveSheet.Protect Password:="noedit", DrawingObjects:=True,
Contents:=True, Scenarios:=True

End Sub

This sub-module is called by the callback routines for several Drop
Down List combo boxes in the workbook.

Couple of questions:

1. Why is the error occurring ? I think it may have something to do
with VBA not knowing what the current workbook/worksheet is.

2. Why is this VBA code executing when I try to save my application
i.e. Why do the callbacks for the Drop Down Lists execute on
the 'Save As' operation ?



9 Answers Found

Answer #1    Answered By: Phyllis Foster     Answered On: Mar 26

I believe that when you save, the application  "compiles".... Not really but
that's another story... the code to P-Code.

On the Debug menu in the IDE there is an option  to Compile. Try doing that
and see what happens.

Are you using Option Explicit at the top of the module?

Try looking at the vlue of the text *and the variable type* in the watch
You could also check what the active workbook/worksheet is at the same time.

Callbacks can be a bit complex sometimes.

Answer #2    Answered By: Levi Wilson     Answered On: Mar 26

If I compile my code from the VB IDE, it doesn't appear to do much
at all. At least, there are no error  messages but also no "build
complete" message. The only thing that happens is that the 'compile
<project>' option  is greyed out.

You mentioned 'Option Explicit' at the top of the module. I'm not
familiar with this, what does it do ?

If I debug my error, my rangeText variable is as I expect, but if I
then click to my workbook  - which has already been renamed to
the 'Save As' filename - the named cell range that rangeText refers
to does not exist if I inspect the range names using 'Insert - Name -
Define', in fact none of the ranges from my original workbook
exist. It is as if Excel has not yet copied them over yet.

I'm pretty new to this VBA stuff, so please forgive some of my

Answer #3    Answered By: Brock Smith     Answered On: Mar 26

Bit short on time at the mo... But for now....

Option Explicit
Tells the IDE that you have to declare variables explicitly.
If you leave this out it's possible to just use any variable anywhere. This
means that if you type in a variable name wrongly VBA will instantiate a
variable with that name.

The compile option  doesn't "compile" in the sense that other languages
compile so there is no build complete message because there is no build.
What it does is attempt to create p-code and put it into place in the file.
This process checks variables and references for example.

Try putting Option Explicit at the top of the module and then "compiling".

I'll look at your code a little closer later ... Sorry for the rush!!

You'll find a discussion of p-code at

You're probably getting the 1004 because the code doesn't know where
rangeText is.

What variable types are you are passing?

> I'm pretty new to this VBA stuff, so please forgive some of
> my queries.
Don't be daft... You are more than welcome in this group with any questions!

Answer #4    Answered By: Sheri Porter     Answered On: Mar 26

Do you have any "Events" programmed?
such as: the BeforeSave Event?
WorksheetChange or WorksheetDeactivate event?
You may be able to create a BeforeSave event, then set a bookmark
so that you can go into Debug and try to go step-by-step to find when/where
the macro is executing.

Answer #5    Answered By: Ibadah Younis     Answered On: Mar 26

Are you running any on save  macros?
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

As for the Range(rangeText).Select
Try adding the sheet name to the line.

If you are on sheet1 when the macro is executed and rangeText is on sheet2 it
might give the error.

Answer #6    Answered By: Raymond Fischer     Answered On: Mar 26

I was wondering what the variable "rangeText" was set to, as in,
does it include a workbook  reference in it's identifyer? . . . if
that is hard coded, it wouldn't tranfer to the newly created
RE: "Range(rangeText).Select"

If that's the case, you might set the first one using "ThisWorkbook"
(because it refers to the workbook the code is running on 'now'), as
opposed to ActiveWorkbook, which refers to the one which is active.

Answer #7    Answered By: Sebastian Anderson     Answered On: Mar 26

It occurred to me later that you wouldn't have to have the workbook
identified in the rangeText variable . . . but if you had an
identified sheet, you'd get the same error. That is, if the
formula's include a reference to a specific sheet (as in, say you
had a sheet that was named "Information") on the original workbook,
and that sheet with that name did not exist on the new workbook,
you'd get a REF# error  . . . another thought to toss into the mix.

Answer #8    Answered By: Inez Wood     Answered On: Mar 26

My rangeText variable is a simple string used to reference a pre-
assigned range within my spreadsheet i.e. A group of cells.

For example,

rangeText = "DynamicBlkIn"

and 'DynamicBlkIn' has been pre-defined using Excel (Insert - Name -
Define) as the following:


where "Inputs" is the name of a worksheet.

What seems to be happening is that, upon issuing a 'Save As', Excel
creates a new copy of the 'original' spreadsheet and then, for
whatever reason, attempts to execute the VBA callbacks for all the
Listbox objects.

At this point, no named ranges exist in the new copy of the
spreadsheet, so the VBA code falls over, as the "DynamicBlkIn" range
does not exist.

That's what I think the problem is, just not too sure how to get
around it !!!!!

Answer #9    Answered By: Natasha Rivera     Answered On: Mar 26

sounds like to me that when you do the Save-As, it's trying to
compile, so the debug picks up reference errors, so the "Debug,
Compile" needs to be run first and those errors  debugged and
adjusted as needed before you can use the macro. That would
explain, as Lisa pointed out, why it is firing when you run Save-
As. It's not really processing the callbacks from the combo boxes--
it's trying to compile the entire macro code and it hits those
problem lines and indicates them for your correction.
That would raise the question: is the macro running fine
(without errors) on the original before you try the Save-as?

Or, as other mentioned, it involves an event firing
which needs to be handled.

As far as I know a File Save-As copies the names, too, so those
names should be on the new version, as well. At least, it did that
when I checked using one of mine a few minutes ago.

Or, are you saying you're getting the Run-Time error  after the
Save-As is completed and you're running the program? That doesn't
sound like what you described, but I was just checking to make sure.

Another way to look is: when the Run-Time error appears, and you
hit the "Debug" button, when you hold your mouse curser over
the "rangeText" variable it should tell you what the value is set
at. That might help you figure out what's hitting the error.

Didn't find what you were looking for? Find more on Excel VBA Errors on Saving Workbook Or get search suggestion and latest updates.