Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2003 Form Control Challenge

Status
Not open for further replies.

BJZeak

Programmer
May 3, 2008
230
CA
Attempting to either add a text box control OR set the TakeFocusOnClick button property on a worksheet in EXCEL 2k3 ... poking around online leaves me to believe that MS broke the ActiveX controls used in Excel 2k3 a few years back with an activeX security update and didn't bother providing a patch for Excel 2k3 ... is anyone aware of or found a way to either set this button property and or use 3rd party text box in Excel 2k3?

in Excel 2k3 not sure what the differences are between:
View Toolbars Control toolbox ... all of the controls listed return a cannot insert object error
View Toolbars Form ... appear to show the same control objects as control toolbox but some controls like textbox are disabled

Wanting to enter some text in a cell then click a button to perform a task
1) the button cannot be clicked until the CELL entry is completed (tab, enter or arrow keys) user is confused by having to enter an extra key so the solution was expected to require setting the button's TakeFocusOnClick property to True ... haven't been able to find the proper link to this controls properties in the object browser

2) another option might be to use a text box control and use the on Enter event to replace the button Click except that the FORM textbox control is disabled and the Control Toolbox textbox is broken

Other Suggestions?





 
I think you probably need to read uop about the difference between Excel Form controls (not to be confused with userform controls, which are <ahem> ActiveX controls) and ActiveX controls in Excel

There's a reasonable overview, if you are interested, here

The properties and methods differ significantly between Form controls and ActiveX controls. The ActiveX Control command button control has the TakeFocusOnClick property, the Form Control command button control does not.

So your real problem is this odd "cannot insert" behaviour, because you cannot insert the necessary control. There was a time, with older versions of Office (so, yes, Office 2003) where a fair number of people reported this issue, and for many the issues was caused by the cached version of the controls not being compatible to an updated Excel. The trick was to delete all the cached versions. Can't for the life of me remember how to do that. Hang on ...

... ok, from an old StackOverflow post (itself referencing a Technet post):

Close Excel.
Start Windows Explorer.
Select your system drive (usually C:)
Use the Search box to search for *.exd
Delete all the files it finds.
Start Excel again.
 
Thx Strongm

The distinction doesn't explain why there are disabled controls in the Form (controls) ... from my perspective, why bother putting controls in a toolbar if they are not available?

Access 2003 has numerous "FORM" Controls which work properly ... I believe VBA is included in most if not all of the OFFICE suite modules with various flavours of FORM controls ... there is an expectation, with a PROFESSIONAL CONSOLIDATED OFFICE SUITE, that ALL modules function consistently. (work the same way) in this case apparently not.

As for EXD files ... closed Excel, removed all instances of *.exd files, opened a new blank excel workbook (2003) and attempted to insert a FORM control with exactly the same result: "Cannot Insert Object" error

here is a link to what I believe best describes the Active X issue resulting from a security update in Dec 2014


This individual also got nowhere with deleting *.exd files

The Link suggests there is a solution ... the only solution I follow from this link seems to require moving to a newer version of Excel?
 
> I believe VBA is included in most if not all of the OFFICE suite modules with various flavours of FORM controls

The FORM controls you are referring to here (MS Forms) are User Form controls, which are the ActiveX controls … (yes, confusing I know)

>doesn't explain why there are disabled controls in the Form (controls)

No, the link I provided does not explain that, that wasn't its purpose!

The reason they are greyed out is that they whilst they are not available for a standard worksheet, but they are for an Excel Dialog sheet. Insert a dialog sheet, and you'll find the previously greyed out Form Controls are now available (and none of the ActiveX controls …). Fairly standard Windows behaviour - to grey out currently unavailable options

Excel1_mbckyy.png


>here is a link to what I believe best describes the Active X issue
Yes, I am aware of that issue. But there are other causes of the symptoms you described and as I said in my post, the solution I linked to worked for many people. I.e not all. Unfortunately, it look like you fall into the latter category. And let's be clear, Microsoft were not under any obligation to ensure Dec 2014 patches were compatible with Office 2003, as even Extended Product Support had been ended prior to that date. So, whilst Microsoft brought out application fixes for Office 2007 and later for the problem caused by the security patch, they did not do so for Office 2003. So, if your issue is definitely caused by the security patch, then unfortunately you may be out of luck.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top