Controls and ActiveWorkbook Problem 1

[GreyBox]

Apologies in advance for the length of this posting.

I'm trying to 'import' a data sheet into my main workbook, which has some sheets with controls on. When I open the sheet to import it opens in a new workbook (of course), which immediately becomes active. So far, so good.

However the main workbook has some Combo controls which detect change events, and these activate, causing an error because the Combo control reference doesn't exist in the new active workbook.

So, I could preface these with the main workbook name, but the workbook name may change.

So, I use the ThisWorkBook. prefix, but having included it for all the affected controls the workbook becomes unstable and gives Windows errors when I try to save it.

So I try the alternative of setting EnableEvents to False, but it seems that events are still tracked before being ignored, because the errors continue. :-(

Am I unlucky or misguided? Has anyone else ever tried to do anything similar and survived?

 

[Zathras]

...So, I could preface these with the main workbook name, but the workbook name may change...

You can store the current workbook name in a variable when opening or saving the workbook. That way you can reactivate it or use "with" whenever you need to reference the main workbook name.

 

[GreyBox]

I've now tried the suggested solution and it worked - thanks Zathras!

Now, the next problem that's come up is that if I use the Save As option off the file menu I get a similar error to the original during the save operation - i.e. the VBA code can't find the control as it's now looking for it under the original workbook name.

How can I sort this sequence of events so I don't get an error? There must be a precise instant when Excel stops using the original workbook name and starts using the new "Save As" workbook name. How can I synchronise to this? Or is there a way to suppress errors during the Save As operation? Or maybe I need to write my own Save As routine? (I'm slowly sinking out of my depth here....)

All input welcomed...

 

[Zathras]

...VBA code can't find the control...

I don't understand. What "control" are you talking about. If you know the name you want to use when saving a worksheet, all you have to do is something like this:
[blue]

Option Explicit

Sub test()
  Call QuietSaveAs("D:\wisnet\scripts\xxx.xls")
End Sub

Sub QuietSaveAs(AFileName As String)
  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs AFileName
  Application.DisplayAlerts = True
End Sub

[/color]
Is that what you are asking for?