0

I am working in Excel 2010 on a large project. Part of it checks for balanced amounts in several different files and if any of them are out of balance, a form pops up with a list of clickable links to the files in question and the expected values. Up until recently, this has been working fine, but this morning I see that the Activate procedure isn't being called when I Show the form. If I click elsewhere and then click on the form again, the Activate procedure runs as it should. It just doesn't run the first time. I even tried stepping through the code and it's not called at all.

As you can see, I didn't change the name of the default "UserForm_Activate". I will say that I have added a lot of code to the project since the last time this functionality worked, but none of that code relates to this form or the procedure that calls it. (That being said, I still plan on looking for a connection there.)

The form is set up to be non-modal. I also have another form that I use as a user interface that is also non-modal and therefore stays visible when this form pops up. The OOBItems object in the code is a Dictionary object with the expected value as the data and the file paths as the keys. LabelLink is a class that makes the links clickable.

Option Explicit

Private LinkLabel() As LabelLink


'***********************************************************************
'Name:          UserForm_Activate
'Description:   Adds links as clickable labels to form when it is shown.
'
'Revision History:
'Date       Author  Changes made
'***********************************************************************
Private Sub UserForm_Activate()
    Const LABELHEIGHT As Integer = 10
    Const LABELSTARTTOP As Integer = 40
    Const LABELWIDTH As Integer = 428
    Const LABELLEFT As Integer = 6
    Const MCSLABEL As String = "Forms.Label.1"
    Const BLUE As Long = 16711680 'RGB(0, 0, 255)
    Const PURPLE As Long = 16711935 'RGB(255, 0, 255)

    Dim Links() As String
    Dim LinkStart As Integer, LinkEnd As Integer
    Dim LinkCount As Integer, LinkNum As Integer

    'split the tag back into links
    Links() = Split(Me.Tag, "||")

    LinkStart = LBound(Links)
    LinkEnd = UBound(Links)
    LinkCount = LinkEnd - LinkStart + 1

    ReDim LinkLabel(LinkStart To LinkEnd)

    'loop through links to add them as labels
    For LinkNum = LinkStart To LinkEnd

        'create new instance of the LabelLink class
        Set LinkLabel(LinkNum) = New LabelLink

        'add the label to the form
        Set LinkLabel(LinkNum).mObjLbl = Me.Controls.Add(MCSLABEL)

        'format the label
        With LinkLabel(LinkNum).mObjLbl
            .Caption = Links(LinkNum)
            .Height = LABELHEIGHT
            .Left = LABELLEFT
            .Width = LABELWIDTH
            .Top = .Height * LinkNum + LABELSTARTTOP
            .Font.Underline = True
            .Font.Size = 8

            'trying to fix the issue of the clicked link text going back to
            'blue if you click on the main form then click back to this form
            If .ForeColor = PURPLE Then
                .ForeColor = PURPLE
            Else
                .ForeColor = BLUE
            End If
        End With
    Next LinkNum

    'refresh form
    Me.Repaint
End Sub


'***********************************************************************
'Name:          DisplayOOBLinks
'Description:   Takes an array of links and opens the form to show them.
'
'Revision History:
'Date       Author  Changes made
'***********************************************************************
Public Sub DisplayOOBLinks(OOBItems As Object)
    Const FORMBASEHEIGHT As Integer = 72
    Const LABELHEIGHT As Integer = 12

    Dim LinkCount As Integer
    Dim TotalsText As String

    'get count of OOB items
    LinkCount = OOBItems.Count

    'resize userform for the number of links to show
    Me.Height = LinkCount * LABELHEIGHT + FORMBASEHEIGHT

    'resize the expected totals textbox for the number of OOB items
    Me.Controls("ExpectedTotals").Height = (LinkCount + 1) * LABELHEIGHT

    'add OOB values to expected totals textbox text
    Me.Controls("ExpectedTotals").text = "Expected totals:" & vbLf & Join(OOBItems.Items, vbLf)

    'preload the form so that the tag can be set
    Load Me

    'join the links into one string to assign to the tag since forms don't take
    'any parameters
    Me.Tag = Join(OOBItems.Keys, "||")

    'show the form
    Me.Show
End Sub

This is the entirety of the code module for the form in question.

Can anyone see why the Me.Show line would show my form, but not run the Activate procedure? I guess I could call it specifically after I show the form but I'd rather not use a workaround when I shouldn't have to.

6
  • What is the purpose of your Load Me line? The form must already be loaded for the code to be running. Commented Feb 18, 2015 at 16:22
  • @Rory Actually, no it doesn't have to be loaded for this code to run. The DisplayOOBLinks procedure is called from another module. It reformats the form and a couple of its controls at runtime then loads the form so that I can set the Tag property before it is shown. Commented Feb 18, 2015 at 16:31
  • Nope - if that code is in the form, the form must be loaded for it to run. Since userforms are auto-instancing, as soon as you use a line like userform.something the form will be loaded automatically. Commented Feb 18, 2015 at 16:33
  • @Rory Oh, I see what you are saying. I had it in there (in a different form) when I first wrote that procedure in a different module. I can remove it; however, that has nothing to do with my issue. Commented Feb 18, 2015 at 16:50
  • UserForm_Initialize() is where the code should be if you want something to happen when the UserForm shows. Commented Feb 18, 2015 at 17:32

2 Answers 2

1

Well, since no one (including me) could come up with anything better, I'll share my workaround and give it another few days before I close the question.

After I show the form, I call the activate procedure manually: UserForm_Activate. It works, but I'd still like to know why it isn't triggering automatically.

Sign up to request clarification or add additional context in comments.

Comments

1

I know this thread is ancient, but I stumbled upon it while looking for a solution to the same problem. What I discovered was that I had a combobox with the enter event running code that was triggering first instead of the userform activate event and the activate event never triggered. This was occurring because the offending combobox was first in the userform tab order and when the form showed, it ran immediately. Moving the combobox down in the tab order solved the problem and allowed the activate event to run as it should. It seems like the activate event should take precedence but apparently it doesn't.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.