Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"With ActiveSheet" validation question
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 Mo 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. Is there a simple way to "active" the only Sheet available in the session in a generic way ? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"With ActiveSheet" validation question
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 "Virgil" wrote: 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 Mo 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. Is there a simple way to "active" the only Sheet available in the session in a generic way ? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"With ActiveSheet" validation question
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. "Barb Reinhardt" wrote in message ... 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 "Virgil" wrote: 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 Mo 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. Is there a simple way to "active" the only Sheet available in the session in a generic way ? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"With ActiveSheet" validation question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveSheet.Shapes("Rectangle 23").Select will not work in 2007 | Excel Programming | |||
Change "With Worksheets" to "Activesheet" | Excel Programming | |||
Validation ?:Accepting both Numbers AND specific letters("N","n"," | Excel Discussion (Misc queries) | |||
What are the reasons for ""ActiveSheet.Paste Link:=True"" failure | Excel Programming | |||
Validation question - allow -500%, 0%, "=50%*50%" but forbid "A", "", " ", "-" ? | Excel Programming |