Workbook open event - userforms

[daveatthewell]

I've got a problem with userforms and the workbook open event.
here's a snippet of code from the open event

Private Sub Workbook_Open()
Dim User As String
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = False
Load QQLog

Now QQLog is the Name (in properties) of the userform which I want to load then show modal, awaiting input into various combos, text boxes etc.
Trouble is sometimes the workbook opens and displays the form (normally when I've not got VB active (Alt F11)) without a problem and sometimes I get a file/path error. Then Excel crashes with a Send report dialog folowed by a recovery if that option is checked. The repaired version more often than not, excludes the form ... and any other code.

It's the intermittent nature that annoys me, sometimers it's ok, mostly it's not.

Any offers?
TIA

DK

 

[Datsmart]

I had a similar intermittant issue with a userform, ended up removing it and building a new one. Drastic, but it cleared up whatever was going on.

 

[Legacy 98055]

It is not generally a good idea to show anything modal within an event procedure. Use this workaround...

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
      <font color="#008000"> 'other code</font>
       Application.OnTime Now, "ThisWorkbook.Show_QQLog"
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Friend</font> <font color="#0000A0">Sub</font> Show_QQLog()
       QQLog.Show
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("9292006193518671").value=document.all("9292006193518671").value.replace(/<br \/>\s\s/g,"");document.all("9292006193518671").value=document.all("9292006193518671").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("9292006193518671").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9292006193518671" wrap="virtual">
Private Sub Workbook_Open()
'other code
Application.OnTime Now, "ThisWorkbook.Show_QQLog"
End Sub

Friend Sub Show_QQLog()
QQLog.Show
End Sub</textarea>

 

[daveatthewell]

I had a similar intermittant issue with a userform, ended up removing it and building a new one. Drastic, but it cleared up whatever was going on.

Yes I have done this before, and psosted here in the forlorn hope that it was something I was doing, rather than something Excel/VBA was doing.
Anyway back at it this morning, reconstructed the whole form, without a single copy or paste; copied all the code to notepad (in case there was anything being added in background) and pasted from notepad back into the form's code sheet. Saved the code from VBA and password protected it. closed vba and saved the spreadsheet witha password. Opened the workbook and got a compile error fromthe "hidden" code module. Corrected the (obvious) compile problem, resaved and then got the path/file error back. God knows how much I cursed Bill G and his programmers. But it got me thinking. The help file for the path/file error refers to "changes being made to the VB project". The problem only seems to arise if one attempts to change the code after you've saved it "locked for viewing". It's as if something is trying to prevent unauthorised changes to the code (even though I was the author). Anyway, I went back in,resolved the compile error and resaved it "locked for viewing" and the problem went away. I still think there's an issue with Excel/VBA and will follow up via the microsoft support route, but for now, if anyone has this problem, try NOT to protect the code and workbook at all - first. If it runs OK, then lock it.

Yours, exhausted, but happy (ish).

DK