Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HTML Text boxes (2003)

    I attach a spreadsheet into which has been copied some HTML text boxes. The s/s also includes a macro to extract data from the HTML boxes and put this into sheet 2.

    As I am not sure how many boxes may be finally present in the s/s, I need a method which will allow me to create a collection of the names of all the boxes that are present in the workbook, so that I can address them correctly and then perform other operations on them.
    Can somebody also please explain how to address the boxes correctly so as to perform operations. For example I need to be able to delete the boxes once I have extracted the data. HTML1.delete does not work.

    Can somebody please assist.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HTML Text boxes (2003)

    Listing them:

    Dim wsh As Worksheet
    Dim obj As OLEObject
    Set wsh = Worksheets("Sheet1")
    For Each obj In wsh.OLEObjects
    If obj.progID = "Forms.HTML:Text.1" Then
    Debug.Print obj.Name
    End If
    Next obj

    Deleting one:
    wsh.OLEObjects("Control 11").Delete
    or
    wsh.OLEObjects(5).Delete

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Text boxes (2003)

    Thanks. However how can I access the value held in the boxes while addressing them as controls?

    The folowing worked
    s12 = HTMLText12.Value

    However the following fails at the ????
    Dim i As Integer
    i = 1
    Dim wsh As Worksheet
    Dim obj As OLEObject
    Set wsh = Worksheets("Sheet1")
    For Each obj In wsh.OLEObjects
    If obj.progID = "Forms.HTML:Text.1" Then
    'Debug.Print obj.Name
    Worksheets("Sheet3").Cells(i, 1).Value = obj.Name
    Worksheets("Sheet3").Cells(i, 2).Value = obj.Value '?????
    i = i + 1
    End If
    Next obj

    OLEObject does not appear to have a value function.

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HTML Text boxes (2003)

    I think I have cracked it using

    Worksheets("Sheet3").Cells(i, 2).Value = obj.Object.Value

    However I am confused as to why I need to use the extra Object identifier. Can you please explain?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HTML Text boxes (2003)

    obj is an OLEObject; this is a very general category that can refer to several kinds of objects, so VBA cannot resolve obj.Value directly. By using .Object, you refer to the object - whatever it may be - contained in the OLEObject.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •