Worksheet Controls Forms vs Active-X

[Andy Keen]

I am creating an application, and wish to keep the code
totally separate (to allow upgrades) from the data
workbooks, whose sheets will contain controls. Ideally I
would use ActiveX controls for their greater flexibility.

These would require code embedded in the data workbooks,
although I can minimise this by calling functions held in
the code workbook, which must then be referenced by the
data workbook. This seems a lot of hassle. Does anyone
know if there is a better way?

Also, I understand ActiveX controls are not available in
Excel 2001 for the Mac. Are they available in more recent
versions?

And finally, is there any reason to believe that the older
forms controls will no longer be supported in future
versions of Excel?

Many thanks in advance for answers to any of these related
questions.

 

[Bob Phillips]

Andy,

I had a similar problem where I had an application workbook and multiple
data workbooks, although it was purely for workbook and worksheet events not
ActiveX controls, but same principle. I could have used application events,
but I did not want to impose that on other workbooks, so I used the method
that you describe whereby I simply added stub events that called into the
application workbook, like so

Private Sub Workbook_Open()
Application.Run "'Leave.xls'!wbOpen"
End Sub

Private Sub Workbook_Activate()
Application.Run "'Leave.xls'!wbActivate", ThisWorkbook.Name
End Sub

Private Sub Workbook_Deactivate()
Application.Run "'Leave.xls'!wbDeactivate", ThisWorkbook.Name
End Sub

etc.

It seems a bit kludgy, but it works well.

Don't know anything about Mac.

I would have thought that forms controls are so embedded that they will
remain for the foreseeable, but who knows what will happen with .Net.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)