Consulting

Results 1 to 7 of 7

Thread: Solved: Problem initializing Class after adding an ActiveX control

  1. #1
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location

    Solved: Problem initializing Class after adding an ActiveX control

    I am having an issue and I am not sure what the problem is.

    I have a class module (ClassTextBoxes) with the following:

    [vba]
    Option Explicit

    Public WithEvents TextBoxGroup As MSForms.TextBox

    Private Sub TextBoxGroup_MouseDown(ByVal Button As Integer, ByVal
    Shift As Integer, ByVal X As Single, ByVal Y As Single)
    MsgBox "Ok"

    End Sub
    [/vba]

    And a Module with:

    [vba]
    Option Explicit

    Dim TextBoxes() As New ClassTextBoxes

    Sub AddTextBox()
    Dim Top As Double
    Dim Left As Double
    Dim Cel As Range
    Dim Obj As OLEObject
    Dim ObjTextBox As OLEObject

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Left = Range("B1").Left
    Top = Cells.Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious, After:=Range("A1")).Offset(1, 0).Top
    For Each Obj In ActiveSheet.OLEObjects
    If Obj.Name = "TextBox1" Then
    Left = Obj.Left
    End If
    If Obj.BottomRightCell.Offset(1, 0).Top > Top Then
    Top = Obj.BottomRightCell.Offset(1, 0).Top
    End If
    Next
    Set ObjTextBox = ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Top:=Top, Left:=Left, Height:=175, Width:=464.25)
    ObjTextBox.Object.BorderStyle = 1
    ObjTextBox.Object.MultiLine = True

    Call InitializeClass

    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True

    Set Cel = Nothing
    Set Obj = Nothing
    Set ObjTextBox = Nothing

    End Sub

    Sub InitializeClass()

    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim ObjTextBox As OLEObject

    For Each ObjTextBox In Sheets("Sheet1").OLEObjects
    If ObjTextBox.progID = "Forms.TextBox.1" Then
    If ObjTextBox.Object.MultiLine = True Then
    j = j + 1
    ReDim Preserve TextBoxes(1 To j)
    Set TextBoxes(j).TextBoxGroup = ObjTextBox.Object
    End If
    End If
    Next

    Set ObjTextBox = Nothing

    End Sub


    [/vba]

    The first sub adds a text box. The second sub creates a class of all the textboxes on the sheet. I want to run code whenever the textbox is clicked so I use the mousedown event.

    Both subs work fine on their own, problem is when I add a textbox I call the initializeclass macro to reinitialize the class and add all the textboxes to a new class and it doesnt work.

    Refer to the attached file.

    You can click the add textbox button to add a new textbox and click the textbox and nothing will happen. Then click the initialize class button and reclick the textbox and you will get the message box.

    Thanks

  2. #2
    I suspect you might do better to use a collection to store the class
    instances rather than an array.
    Top of module:

    Dim mcolClasses As ClassTextBoxes

    And in the routine:

    Dim cClassTextBoxes As ClassTextBoxes
                If ObjTextBox.Object.MultiLine = True Then 
                    Set cClassTextBoxes = New ClassTextBoxes
                    Set cClassTextBoxes.TextBoxGroup = ObjTextBox.Object 
                    If mcolClasses Is Nothing Then
                        Set mcolClasses = New Collection
                    End If
                    mcolClasses.Add cClassTextBoxes
                End If
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    It doesn't compile.

    I did just get a workaround to work by using ontime and calling the initialize sub 1 second later. But am still curious as to the issue.

    Thanks

  4. #4
    Wel, maybe Excel needs the time to initiate things when you add a textbox. Using ontime ensures Excel has finished its business before your initialisation code is run.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,232
    Location
    Jake,

    OnTime would have been my suggested solution (you can do it with zero seconds), because I think that the code is ripping through and your initialize is firing away before the new control is fully registered.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Odd. The textboxes do get added to the array, but if the textbox hasn't completely finished and the class starts, then the textbox is registered perhaps it breaks the class.

    Changing the ontime to run after Now and not adding any time still works so I will do that.

    Thanks

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,232
    Location
    A placeholder for the textbox has to be added to the array, that is in your code, but that doesn't been that the asynchronous textbox creating/registering/et al has fully completed before you try to use it. By using OnTime, you break any connections.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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