Cannot use range objects

[jason]

Hi, I'm trying to use VB to alter one worksheet based on a
roster of names in another worksheet (in the same
workbook). I'm using Excel 2000 version 9.0.2720 (Windows
2000) and Visual Basic 6.0.8435.

Each time the code gets to a place (any place) where it is
using a range, the code stops executing and VB reports the
following: "Visual Basic 400". Nothing else! Just -
"400."

I suspect that, since I'm new to VB, I'm not setting
things up correctly. Here is a typical function (see ***
for the line where things are breaking down):

Private Sub addNewList(ByVal numEmpl As Integer)

Dim mySheetName As String
Dim mySheet As Worksheet
Dim myRange As Range

Dim numOfWeeks As Integer
Dim numOfShifts As Integer
Dim baseCell As Range
Dim cellFormula As String
Dim initials As String

'Initialize variables
mySheetName = Worksheets("ROSTER").Range("E7").Value
Set mySheet = Worksheets(mySheetName)
numOfWeeks = Worksheets("Roster").Range("E10").Value
numOfShifts = numOfWeeks * 17

'Get to our starting point
mySheet.Select
Set myRange = Range("B5")
*** myRange.Select

If anyone can help, I would greatly appreciate it.

 

[Don Guillett]

You might be trying to make something harder than it need be cuz this will
work
sub gothere()
x = [roster!e7]
Application.Goto Sheets(x).Range("b5")
end sub

but this works too
Sub addNewList()
Dim mySheetName As String
'Dim mySheet As Worksheet
Dim myRange As Range
Dim numOfWeeks As Integer
Dim numOfShifts As Integer
Dim baseCell As Range
Dim cellFormula As String
Dim initials As String

'Initialize variables
mySheetName = Worksheets("ROSTER").Range("E7").Value
'Set mySheet = Worksheets(mySheetName)
numOfWeeks = Worksheets("Roster").Range("E10").Value
numOfShifts = numOfWeeks * 17

'Get to our starting point
Sheets(mySheetName).Select
Set myRange = Range("B5")
myRange.Select
End Sub

 

[Bernie Deitrick]

Jason,

Your code did not generate any errors when I tried it out.

But, there is a Knowledge Base Article - 213835 about error code 400:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213835&Product
=xlw2K

It doesn't seem to apply to your code, but perhaps you have an event
that is fired when your activate your sheet?


SYMPTOMS

In Microsoft Excel 2000, when you use the Microsoft Visual Basic
ShowDataForm method, you may receive one of the following error
messages:

Run-time error '1004':
ShowDataForm method of Worksheet class failed

-or-


400

CAUSE
This behavior can occur because Excel is unable to find a data list on
the worksheet that you specified in the Visual Basic statement.

HTH,
Bernie
MS Excel MVP

 

[jason]

Bernie (et al.),

Thanks for the replies. To Bernie in particular, I looked
up that error code, also, and decided that it couldn't
apply to me; maybe I'm wrong about that, but I don't see
how.

Another thought has occurred to me. I was not able to do
the installation of Windows 2000 on my machine when it was
provided to me. Could it be that the libraries that
support use of range objects have not been installed? I
know, for example, that the help system for Visual Basic
has not been installed. I've got the object browser,
project browser, basic editing, etc. But maybe I don't
have the libraries. Is it possible to install MS Office
and not install the components necessary to run VB (or, at
any rate, run it at a high level)?

Thanks for your help. If I cannot get the range objects
to work, I may try something else, perhaps what Don was
suggesting.

Jason

 

[George Nicholson]

Jason:

I don't think so. Installing the VBA *Help* files is definitely an option
that needs to be turned on during a custom install of Office, but the files
that VBA requires to function are always installed, as is the VB development
environment (VBE): the Object Browser, etc.

If they weren't, there would be a lot of workplaces that would gladly chose
to cripple their user's installations of Office "for Security purposes"
rather than relying on "Disable/Enable macros" options.

Brrrrrr. That's a scary thought.

 

[Frederick]

Hi Jason, just a thought , I had some code that wouldnt work once and I was
told to set the references in VB etc. you may need to enable the references
in the VB environment to include "Visual Basic for Applications" and
"Microsoft Object Library"

Ie. Open the VB workspace then select Tools -> References and click the
check box for the above references.

Fred

 

[Jason]

Alas, I've tried adding both of those, and it didn't fix
the problem. Good thought, though. I even added a few
libraries that I thought *might* be relevant, but no luck.

Ah well. Back to the drawing board.

Jason

 

[Dave Peterson]

Another couple of guesses:

1. Did you ever declare a variable called "range"?
2. You may want to run Rob Bovey's code cleaner:
You can find it at:
http://www.appspro.com/
3. Is your code behind a worksheet? I got a 1004 (not 400) error when I tried
to select a range that wasn't fully qualified. (Unqualified ranges belong
to the worksheet owning the code--even if you've selected another sheet
first.)

with mySheet
.Select
Set myRange = .Range("B5")
end with
myRange.Select

So now that .range("b5") belongs to mySheet--not the sheet owning the code.

4. When you were adding references, did you notice if any reference was
missing? If yes, try removing it (or finding it) and see if that helps.