"With ActiveSheet" validation question

[Virgil]

Hi

Issue: ActiveSheet not always valid

Context :
I have a VBA sub called via a macro . It is in an XLS file with no SHEETS in
it.
The macro is used for DBF files opened by Excel.

With ActiveSheet

....

End With

More:
Sometimes when i run the macro, my trace shows "Form" for the ActiveSheet
name which on paper does not exist. Usually it is the name of the DBF file.
The DBF can be opened by a double lick or after excel has been started.
Then the XLS macro file is opened. As the DBF file name may change i don't
want to force some name on it.
Usually all i do is click on a DBF field then re-run the macro.

Thanks

 

[Barb Reinhardt]

I'm not sure how you have an XLS file with no sheets in it. Whenever I've
tried to delete the last sheet in a workbook, I get an error.

I'm not exactly sure what you're doing, but I often refer to a sheet this way

Dim myWS as Excel.Worksheet

Set myWS = ActiveSheet 'or some other sheet you want to work on.

or you could do something like this
Dim myWB as Excel.Workbook

Set myWB = WOrkbooks.open(filename) 'Your DBF File
Set myWS = nothing
on error resume next
Set myWS = mywb.worksheets("SheetName")
On error goto 0
if not myws is nothing then
'Action on worksheet.

end if

These are some ideas.

HTH,
Barb Reinhardt

 

[JLGWhiz]

Hi Barb, I suspect that the OP's DBF is actually Sheets(1) of the workbook.
Probably hidden since it needs a double click to activate it. But this is
all guesses.

 

[Virgil]

Thanks for the feedback

I will try to be clearer

When you open a DBF file in Excel, the tab at the botton is referenced with
the DBF name. No other sheets are available.

We have our DBF maint. macros in an XLS file. This is opened after the DBF.
If you where to open the XLS by it self the screen would be works sheet free
however the macros exist (VBA). This is because the default sheet in the XLS
has attributes to make it invisible / disabled as part of the properties.

Interesting the invisible sheets name is "Data" yet the ActiveSheet.Name
reports "Form" when it gets it wrong.

Regards