Userform.load Excel invisible one for the vba experts !

[Fixador]

Currently - on opening - I open a userform & make the Excel.Application invisible.

This i find is handy - but there is a downside - quite frequently at some point in the day - my ( now ) minimised userform - vanishes from the screen - and the program stops running ( i can tell it has stopped as the Q2 triggers are not getting fired in GrussBA ).

Task manager shows several Excel are running - but it is not until the end the day before i get the program to miracleously re-appear ( - why does it re-appear ? not a clue - something to do with shutting everything down )

Strikes me as there could be several issues here ....... so, question - instead of having Excel invisible on open - now, minimize it on open - and keep the Userform open & visible

However, whenever i have done this - the Userform vanishes as well

All ideas gratefully accepted ...

getting to be at wits end on this ! aggghhh !

[osknows]

Hi Fixador,

Not sure why the code would stop running? Have you referenced all worksheet read/writes to specific workbooks, Eg With Thisworkbook.Sheets...?

In terms of the userform disappearing... It might be something to do with the 'ShowModal' property of the userform. If the showmodal property is true Excel cannot be accessed until the form is closed.

and/or it could be your method of hiding/unhiding the userform. A userform can be hidden/unhidden or loaded/unloaded. I think unloading a userform removes the userform from memory so any variables/code within the userform could be lost.

This example shows different ways of managing userforms and the effect it as on the parent application http://www.mediafire.com/?emqnncmztkyga3m

It might be something different but hard to tell without examples

Os

[Fixador]

Hi Osknows,

Im fairly confident that my code is referenced ok - it has being checked - so that would be 2 of us missing something !

what I have is in ThisWorkbook is:-

Code: Select all

Private Sub Workbook_Open()
UserForm1.Show (False)

which tackles the modal issue

Then in Userform1:-

Code: Select all

Private Sub CommandButton2_Click() 'view Excel
    'UserForm1.Hide
    Application.Visible = True
End Sub

I have disabled Userform1.hide - as i found that - if I recovered it from being "hidden" from the workbook - it would re-intialise - there's a puzzle !

Now into Sheet1

Code: Select all

Private Sub CommandButton4_Click()
    UserForm1.Show (False)
    Application.Visible = False
End Sub

I'll take a look at your mediafire file

cheers

[osknows]

Your code works fine for me?

[osknows]

Are you running mutiple instances of excel?

[Fixador]

Hi Osknows - you work later than me !

- yes - running multiple instances - never had any problems like this until started using forms a few weeks ago.... strewth have i said something like a tautology there !

Just started looking through yr xls.... you got it - form visible, with Excel minimised !

[Fixador]

Hide Excel2 > UnHide Excel2 achieves minimisation - but keeps the form visible.

Looking at the code :-

Code: Select all

Private Sub CommandButton7_Click()
Unload UserForm1
Application.WindowState = xlMinimized
UserForm1.Show 0
End Sub

Unload Userform1 - damn - if i did this - I would expect UserForm1 to re-intialise - ie, I lose all of the inputted values on the form

musing..... could I substuite Userform1.Hide ? one to try

Code: Select all

Private Sub CommandButton7_Click()
UserForm1.Hide
Application.WindowState = xlMinimized
UserForm1.Show 0
End Sub

Can a Textbox retain a changed value ???........

Now to add an intialisation - that inserts a value into the textbox

Start workbook : Intial value shows

Click on Hide Excel

Click on Unhide Excel

textbox value remains the same

...now the crunch test

change the value in the textbox

now click Hide Excel

Unhide Excel - and bingo - textbox has retained the new value

Well thats a new trick - which i desperately needed ! 2 problems solved

the test xls is at

http://www.mediafire.com/?hyur39qii8knh3r

[Fixador]

nearly there ......
Intially form open, but Excel does not show in taskbar - no thats not true - it appears in the taskbar - then vanishes ....

Userform cmd button: to "show Excel" - and Excel appears visisble and in taskbar

From Workbook cmd :to "hide Excel" - form appears - and Excel stays in taskbar as minimized

phew !

needs a bit of tweaking to get Excel to stay visible in the taskbar at start up - probably got a .visible = false lurking in the userform1 code !

thanks Osknows