Very strange problem

[Enkay62]

Hi everybody.
Here's what I've discovered just by chance.
I've an excel 2000 workbook with a form that's always shown as it's loaded via workbook_open event and unloaded via workbook_beforeclose event.

I realized that if I put , in workbook_open event, the code
application.windowstate=xlminimized here's what happens:

1)the workbook icon on task bar flashes.
2)Clicking on that icon, the form is shown directly on desktop(Excel remains an icon).

The result is that I can use that form as if it was an independent application, without visualizing excel sheets. it is not necessary to show any sheet. Sheets contains data and formulas but any input and output is done from form.

So, here's my question.

What's the code to get that click on workbook icon programmatically?

I tried both with "appactivate" and with "Windows("mywb.xls").activate" but it didn't work (maybe because such code cannot be referred to the same workbook in which it is written).

Any help will be much appreciated.
Thank you all.
Regards.
Nick.

 

[combo]

Hi,
it is possible to hide whole excel instance instead (however, you need API to show UserForm icon on the taskbar):

in ThisWorkbook module:
Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show
End Sub

in UserForm1 module:
Private Sub UserForm_Terminate()
Application.Visible = True
End Sub

In that case you need precise error handling, not to stay with invisible excel instance in memory.

combo

 

[Enkay62]

Hi Combo.
Thank you very much. it works perfectly.
Now, when I open my workbook, the application is shown for 1 second before being hidden.
I understand that it's necessary to run excel in order to generate workbook_open event where I put the code to hide the application.
What I wish to know (maybe I'm asking too much -)), is if there is a way to run excel, even for that second, without showing it at all or, at least, opening it's window just as icon?
Thank you very much for any suggestion.
Regards.
Nick.

 

[combo]

Hi Nick,
If you could use automation to create excel and open a file, the new instance would be invisible. Macros are by default enabled. So, from another location, VB/VBA code, with reference to excel library:

Dim xlApp as Excel.Application
Set xlApp=New Excel.Application
xlApp.Workbooks.Open FileName:="Path\FileName.xls"
setxlApp=Nothing

And "FileName.xls" code selection after modifications:
Private Sub Workbook_Open()
UserForm1.Show
End Sub

Private Sub UserForm_Terminate()
Application.Quit
End Sub

If it is not possible, you can hide the startup screen, create a shortcut to the file and add /e switch. However, the excel window will flash.

combo