Worksheet.Calculate code no longer executes on worksheet launch?

  • Thread starter Thread starter Arris
  • Start date Start date
A

Arris

Hi all,

I wrote some code before christmas, some of my first simple stuff in
Excel. I'll try and explain the problem I am having as best as I can.

Previously I had an embedded Excel object in a Lotus Notes Form. I then
had a button in Notes which opened up the Embedded object, I believe
the code is similiar to vB.
'Get embObjData handle from Notes Document

Set handle = embObjData.Activate(True)
Set ws1=handle.Worksheets(1)

I then had code within the embedded Excel which populated Combobox
objects which I had created on the Excel Worksheet with a list of
values from Lotus Notes.
Now before Christmas I experienced the code I had in
Worksheet_Calculate correctly assigning values when the Excel object
was opened/activated. But just recently I had Excel crash a few times
and did an online Office update. I'm just wondering if Microsoft
stopped the Calculate sub being executed on launch of the object.
Sounds like the sort of thing they would fix as a "security flaw".

If anyone know of somewhere within the Excel object where I can put
code that will execute when the object is launched please let me know
as this is adding to my already large list of things to fix,

Here is the code I am using.

Code:
--------------------

Private Sub Worksheet_Calculate()
Dim session As Object
Dim db As Object
Dim doc As Object
Dim view As Object
Dim documentString As String
Set session = CreateObject("Notes.NotesSession")
Set db = session.CurrentDatabase
Set view = db.GetView("Lookup")
Set doc = view.GetFirstDocument
Set doc = view.Getdocumentbykey("Combo box values")

raSize = 0
For Each entry In doc.GetItemValue("StrDispVal")
ReDim Preserve ra(raSize) As String
ra(raSize) = entry
raSize = raSize + 1
Next

'Set all ComboBoxs to have this as list
ComboBox1.List = ra()
ComboBox2.List = ra()
ComboBox3.List = ra()
ComboBox4.List = ra()
ComboBox5.List = ra()
ComboBox6.List = ra()
End Sub

--------------------


Nothing too complicated, but I just can't find the event/sub to put it
in so that the values are assigned when the object is launched (well I
was using calculate but it doesn't seem to work any more)

Thanks for any help.

Arris.
 
Back
Top