XL dsn't respnd 2 evnts when HTML elmnts have fcus

masterofdisaster · August 14th, 2003, 08:09 AM

Hi,

I am building a workbook in Excel 2000 where I need to recreate drop boxes from web pages. I can do this by manually creating new controls in XL and pasting in the selection list data then programmatically assigning the corresponding HTML option value to a string by doing a select case. Problem is I have a few hundred to do, so this option is extremely time consuming. I can paste the control right off the web page into the workbook and use the function below to grab the HTML option value that is already contained in the control. Much easier. This code stops me from having to search out and type all of the options for the user to select and then adjusting those values from code to work with the web pages.

Code:

Function dropboxvalue(objname As Object, tcell As Variant) 
    Dim true1 
    true1 = objname.Selected                            'set true1 to string of true/falses 
    Dim MyPos 
    MyPos = InStr(1, true1, "TRUE", 1)                  'find true in string 
    pos1% = ((Val(MyPos) - 1) / 6) + 1                  'determine increment of selected option 
    Dim opt1 
    opt1 = objname.Values 'opt1 = list of options 
        opt1 = ";" & opt1 & ";" 
    optpos1% = 0 
    For q = 0 To Val(pos1%) - 1 
        startpos1% = optpos1% + 1 
        optpos1% = InStr(startpos1%, opt1, ";", 1)       'set starting point of correct option in string 
    Next q 
    optpos2% = 0 
    For q = 0 To Val(pos1%) 
        startpos2% = optpos2% + 1 
        optpos2% = InStr(startpos2%, opt1, ";", 1)      'find end point 
    Next q 
    len1% = (optpos2% - optpos1%) - 1                   'length of option string 
    start1% = optpos1% + 1 'stasrting position of option 
    dropboxvalue = Mid(opt1, start1%, len1%)            'set dropboxvalue = option string 

End Function

This code works like charm, problem is I can't get Excel to respond to any events while one of these controls has the focus and I can't find a way to switch focus with the enter or tab key. The worksheet is not responding to and events while one of these HTML elements is active. This is a problem because it prevents being able to tab or enter through the fields and activate other things on the worksheet. Perhaps there is a way to get the controls to respond that I havenât tried yet. Any suggestions you have would be great, thanx.

JC