Excel 2003. ActiveX controls embedded in worksheet not working, then can’t even insert them. *SOLVED*

[DocAElstein]

I took another look at this problem recently. I managed to solve it this time around. I will share the solution in the next few posts…

The brief shortened version of the solution:
Various updates needed to be de installed. No other action was necessary. Finding those updates proved to be more difficult than thought in the original attempt at a solution …
A summary at the end is given of the updates that needed to be removed.

Detailed report on the work done during finding the solution

Problems in listing/ checking updates on a computer:
A major handicap in finding the solution was, I must confess, a stupid oversight, or rather lack of thoroughness on my behalf: Because of the sometimes large number of dates involved, a semi automated method was sought to get a list of, and search through, the updates on a computer… At the time only one method was found , the “wmic” command type way ( http://www.eileenslounge.com/viewtop...=28682#p222044 ) to get a list of the updates into a text file. Unfortunately this only seems to produce a list of updates mainly associated with the operating system. It misses, in particular, all Microsoft Office related updates.
Another more recently found method, ( https://www.eileenslounge.com/viewto...=31572#p244479 ) appears to have limitations: Microsoft Office related updates are not shown for computers with the operating system XP; this new method appears to list all updates by their instillation date, and it is not updated after any update removal.
The list found manually through the computers control system also appears to have some inconsistencies from computer to computer. The only characteristic consistently useful of the list viewable though the computers control system useful was found to be that most updates are in the list, ( even if occasionally duplicated ). A removed update did not appear in that list after the removal through de instillation. The number of updates indicated and the date of their instillations seemed to be inconsistent and unreliable.

Building Update lists and identifying “good” and “bad” updates
The main goal of my work was for personal use of getting functioning Excel 2003 on all my computers. From about a dozen computers , most had the problem that ActiveX embedded in a worksheet controls had never worked in Excel 2003. I had three Operating systems, Windows / , Vista and XP
There was no initial specific plan or strategy, but as time went on, one evolved:

I built up three main files to list all updates on all my computers.
WMI Query XP.xls https://app.box.com/s/5brcqrc5uk4tuhmwdgrr1dhygnmh7nr5
WMI Query Win 7.xls https://app.box.com/s/wm3qcgy5gn4csq0csuk5fp2iqu0hrqxz
WMI Query Vista.xls https://app.box.com/s/9i4tbuy3qx8dpd2uy2993s9z2pv4y0mb
In addition, a file was used to build up a list of updates that did not appear to cause any problems
UpdatesOK.xls https://app.box.com/s/djh7zvbxuqymzo3nm6clftq3ygk3iwyf
The initial lists of all updates were partly built up by the two methods mentioned, but due to the problems in those methods mentioned, they were mostly checked, and supplemented to, laboriously manually via inspection the computers control system list. The way to see the computers update list is slightly different from computer to computer, but is approximately as in these steps for a Vista operating system computer example:
Microsoft Symbol, Control/ System Control
SystemControl 1 2 .JPG https://imgur.com/Iis3ayf
Software or Program De installer
Software 3 .JPG https://imgur.com/p9kyIzD
Show Updates
Show Updates 4 .JPG https://imgur.com/6dSCEkA
List Updates 5 .JPG https://imgur.com/6dSCEkA

Here an XP operating system computer example:
Microsoft Symbol, Control
SystemControl 1 2 .JPG https://imgur.com/MSkjhfL
Software or Program De installer
Software 3 .JPG https://imgur.com/nDS9NHH
Show Updates
Show Updates 4 .JPG https://imgur.com/D4D9u8h

Various coding was developed and used along the way to assist sorting and searching the various lists..
The next posts give an approximate report on the work done. It is not complete and is not in full detail, and is just intended to give an approximate indication for both future reference and general interest.

So the start point of the next discussions are with the 3 Excel files containing full lists of all updates on some of my computers.
Initially the file of “good/OK” updates, UpdatesOK.xls , is empty.

A simple routine, Sub FindBads() is used to search the lists. Initially it is filled with 6 updates that have been reported previously as possible causes of problems with Activex controls. At the end of my investigations this rose to 11. In addition some of the previously noted “bad” updates were still on some of my computers and did not finally appear to cause problems with ActiveX controls.

At this stage the XP update list File, WMI Query XP.xls , is probably the most up to date as this was looked at last. But I may update the other files from time to time.
At the time of writing this post, this is the current form of Sub FindBads()

' ActiveSheet code below!!!
Sub FindBads() ' to find all bads in update lists, initially based on internet reserch:  2553154 2726958 2965291 2920813 3054873 974554   ( "890830" is just for test, it is not a known bad ). The list is added to as "bad" updates are identified                                     ' https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49097560#49097560
Dim Bads() As Variant: Let Bads() = Array("890830", "2553154", "2726958", "2965291", "2920813", "3054873", "974554", "4011203", "2965286", "2920794", "4461614", "4461522")
Dim rngSrch As Range: Set rngSrch = ActiveSheet.Range("A1:H" & ActiveSheet.UsedRange.Rows.Count & "")
Dim rngFnd As Range
Dim Stear As Variant
    For Each Stear In Bads()
     Set rngFnd = rngSrch.Find(What:=Stear, after:=ActiveSheet.Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) '
       If Not rngFnd Is Nothing Then
        rngFnd.Select
        MsgBox prompt:="Hama  " & Stear: Debug.Print Stear
       Else
       End If
    Next
End Sub

Another routine was used to mark “OK green” updates in a current update list based on the updates on computers which either
_ had never has the problem issues,
Or
_ the updates on computers once they were “cured” by removal of “bad” updates:

Sub UpOKs() ' ActiveSheet
Dim WsOK As Worksheet
 Set WsOK = Worksheets("UpOK")
Dim arrUpOKs() As Variant: Let arrUpOKs() = WsOK.Range("A2:A" & WsOK.Range("A" & Rows.Count & "").End(xlUp).Row & "").Value
Dim rngSrch As Range: Set rngSrch = ActiveSheet.Range("A1:A" & ActiveSheet.Range("A" & Rows.Count & "").End(xlUp).Row & "") '.SpecialCells(Type:=xlCellTypeConstants)
rngSrch.Copy
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDataObject.GetFromClipboard
Dim strIn As String: strIn = objDataObject.GetText()
Dim arrRngCpy() As String
 Let arrRngCpy() = Split(strIn, vbCr & vbLf, -1, vbBinaryCompare) ' . Becuase the last thing in the string was vbCr & vbLf, we have a last split with an extra element resulting with "" in it
'
Dim UpDts As Long
    For UpDts = 0 To UBound(arrRngCpy()) - 1 ' This ignores the last value of "" which corrsponds to one cell above the last used cell
    Dim GoodCnt As Long
        For GoodCnt = 1 To UBound(arrUpOKs(), 1) ' all good updates looked at
            If Trim(UCase(arrRngCpy(UpDts))) = Trim(UCase(arrUpOKs(GoodCnt, 1))) Then
             Let rngSrch.Item(UpDts + 1).Interior.Color = vbGreen 'UpDts + 1 has +1 because like element 1 is the second corresponding to row 2
            Else '
            End If
        Next GoodCnt
    Dim strMaybeBads As String
        If Not rngSrch.Item(UpDts + 1).Interior.Color = vbGreen And InStr(1, Trim(rngSrch.Item(UpDts + 1).Value), "KB", vbTextCompare) > 0 Then
         Let strMaybeBads = strMaybeBads & Trim(UCase(arrRngCpy(UpDts))) & vbCr & vbLf ' add to list incl last vbcr & vblf
        Else
        End If
    Next UpDts
 
 'Let ActiveSheet.Range("Q2").Value = strMaybeBads
 Debug.Print strMaybeBads
End Sub