Workbook open event - userforms

daveatthewell

New Member
Joined
Jul 28, 2006
Messages
43
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 
L

Legacy 98055

Guest
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="filter:progid: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

New Member
Joined
Jul 28, 2006
Messages
43
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
 

Forum statistics

Threads
1,136,369
Messages
5,675,360
Members
419,565
Latest member
Phil57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top