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.
Load Meline? The form must already be loaded for the code to be running.userform.somethingthe form will be loaded automatically.