OLEObjects

[Roy]

Hi,

I have an Excel template, that when used to create a new spreadsheet
displays a form based wizard. The idea is that the wizard will create a
number of controls for you on a sheet when you click certain buttons on the
wizard.

The code below is called by the button on the wizard:

Public Sub addNewFNRow(pType As kcFN)
Dim xPosition As Integer
Dim yPosition As Integer
Dim lHeight As Integer
Dim lCoords As String

Dim lRange As Range
Dim oOLE As OLEObject
Dim cboObject As ComboBox

Select Case pType
Case kcIFN
lCoords = _
INITIAL_IFN_COL & INITIAL_IFN_ROW & ":" & INITIAL_IFN_COL &
INITIAL_IFN_ROW
Set lRange = shtFNOrders.Range(lCoords)
xPosition = lRange.Left
yPosition = lRange.Top
lHeight = lRange.Height + 2

Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
Left:=xPosition, Top:=yPosition * (gCount + 1), Width:=100,
Height:=lHeight)
oOLE.Name = "cboIFNCountry" & gCount
'oOLE.SendToBack
'oOLE.ZOrder msoSendToBack
Set cboObject = oOLE.Object
With cboObject
.Font.Name = "Tahoma"
.Font.Size = 8
.Clear
.AddItem ("Item1")
.AddItem ("Item2")
.ListIndex = 0
End With

gCount = gCount + 1
End Select
Set lRange = Nothing
Set oOLE = Nothing
Set cboObject = Nothing
End Sub

The code works and inserts a populated ComboBox in the correct position on
the sheet - only trouble is the wizard completely disappears! None of the
terminating event handlers get called e.g. QueryClose, Terminate etc! I
thought that it might be something to do with loss of focus, so that when
the control is inserted, the new control steals focus causing the form to
go. YOu can see by the commented out lines, that I tried to mess around
with ZOrder but that did not work. I also tried re-showing the form, but
that does not work. Note that the wizard is non-modal.

Any ideas anyone - this is driving me nuts!

Cheers

Roy

 

[Todd Larson]

I believe an above post mentions in this situation to have the form Modal
state. It will stay visible then after adding the OLEObject to the worksheet.

Todd Larson