What does '400' mean?

[kualjo]

My code in one file opens another selected file via '.GetOpenFilename'. The newly opened file is then set as the activeworkbook. The next command is 'Range("A1").Select'. I thought it would just select cell A1 in the new workbook and go from there, but I keep getting an error message that just has the red and white X and the number 400. Not very helpful in determining where my error is. What is this, and how can I get this to work properly? :banghead:

[dkusleika]

It means that you are trying to select a cell that's not on the active worksheet.

When you code
Range("A1").Select, it's called an unqualified reference. That means you haven't said which A1 you want to select (meaning which sheet in which workbook). Excel VBA can handle unqualified references, but which A1 is determined by Excel, not by you. When your code is in a standard module, and unqualified range reference refers to the Activesheet.

I your case, your code is in a Sheet module either running in an sheet event or the click event of a button. When the code is in a sheet's class module, unqualified references refer to that sheet regardless of what the Activesheet is. An herein lies your problem. It's trying to select SheetTheCodeIsIn.Range("A1"), but SheetTheCodeIsIn is not the Activesheet.

Here's the fix, in three steps:
1. Don't Select or Activate things unless you really need to. Just work with the object's properties and methods directly rather than working with the Selection object.
2. Whether or not you decide you need to Select, always qualify your reference. Use With blocks or object variables so you don't have to type so much, but always fully qualify your references with the workbook and worksheet.
3. If you need to select something, you can first Activate the workbook it's in, then Activate the worksheet it's in, then Select the range. You don't have to do all that if you know the sheet you want is active, but if it's not, then you need to do at least some of it.

[Aussiebear]

@kuajo You could use your current code if the workbook only contained one sheet, otherwise follow the advice offered by dkusielka in Post#2

[kualjo]

Whether I select, activate, and/or set the workbook as active, or do none of these, doesn't seem to matter. I still get an error of some kind. Here is the portion of code that I am dealing with. Note that selecting A1 is in a different procedure. This may or may not matter.

The raw data file has already been selected. This code opens that file and saves it as 'test save.xls'. (This save will overwrite the previously saved file.) The user is then asked if they want to validate the data. If yes, the new_data_validation sub is called. That's where it bombs.

...
Workbooks.Open raw_file
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="test save.xls", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True


resp = MsgBox("Validate new data?", vbQuestion + vbYesNo)
If resp = vbYes Then
new_data_validation
Else
Exit Sub
End If


End Sub
______________________________________________________________________


Sub new_data_validation()


Application.ScreenUpdating = False


Range("A1").Select
...

The reason for creating a separate procedure for new_data_validation is that it needs to be able to run on its own if necessary. The user can say No in the previous MsgBox, then come back later to do the validation.

Another strange thing that's happening is that when Application.ScreenUpdating = False runs, I can hover the mouse over it and the floating message still shows it as True. Could this be a hint to what's happening?

There are a lot of things about VBA that are still new to me. I'm learning!

[kualjo]

So I changed...

Range("A1").Select

...to...

Sheets(1).Range("A1").Select

...and it worked. I don't understand why the additional code would be needed, since the file only has one worksheet to begin with. Can someone please enlighten me?