Autofit and ActiveX controls

[Mike]

I have a Sheet1 and a Sheet2. Sheet1 contains an "Update" button,
which Activates Sheet2 (with a module level flag, m_bUpdate = True).
In Sheet2_Activate, I read a database, and dynamically fill all the
cells in Sheet2. The cells in Column A represent an Item, Column B is
Description, etc. Each Item will have more than 5 rows of data, with a
blank row between each Item.

Item1/Description/Stock/.../Preferred Vendor/...
more info about Item1
more info about Item1
more info about Item1

Item2/Description/Stock/.../Preferred Vendor/...
more info about Item2
more info about Item2
more info about Item2

Each Item potentially has more than 1 Preferred Vendor, and I show the
Vendors by a priority ranking.

So, I show all that, and it works/looks fine. During the creating of
Sheet2, I set Application.ScreenUpdating = False, populate all the
cells, protect the Worksheet, then set ScreenUpdating = True. I want
to put an ActiveX forms.optionbutton.1 in the cell(s) with the
Preferred Vendors. The idea being the user will select one of the
Vendors before placing the final order. Anyway, during a test .xls
that I've been messing with, I can't get the .AutoFit to work when I
place an ActiveX OptionButton within the boundaries of a certain cell.
It doesn't seem to understand the boundaries of the ActiveX control.
Does .AutoFit only work with text placed within a cell ? Any
thoughts/ideas ?
I do the following
Worksheets("Sheet2").Columns("A:T").EntireColumn.AutoFit

This may be more verrbose than neccessary, sorry about that...

Mike

 

[Dick Kusleika]

Mike

Does .AutoFit only work with text placed within a cell ? Any
thoughts/ideas ?
I do the following
Worksheets("Sheet2").Columns("A:T").EntireColumn.AutoFit

Correct. OLEObjects sit on an invisible layer above the spreadsheet. The
option button and the cell don't even know each other is there. I usually
do it the other way, that is, fit the OLEObject to the cell instead of the
cell to the OLEObject

Set MyObj = Sheet2.OLEObjects.Add(...

MyObj.LinkedCell = "A1"
MyObj.Top = Sheet2.Range("A1").Top
MyObj.Left = Sheet2.Range("A1").Left
and so on.