Activateイベントはアクティブ状態になったときに発生するイベントです
初期状態でいうと、フォームの表示される前に「Initialize」イベントが発生し、表示されて最初にこのイベントが発生します
このイベントは別のフォームからアクティブ状態になったときにも発生しますが、Excelや別のアプリからアクティブ状態になっても発生しません
複数のフォームを並行して使用する際に、このフォームイベントで特定のフォームがアクティブ状態になったら関連するシートやブックに切り替える、といった動きに使えます
複数のフォームを自由に切替するには「ShowModal」プロパティをFalseに設定する必要があります
他によくあるのがフォームを別のアプリ（Excel以外）よりも全面に表示する処理でも使用します
Excel以外のアプリをアクティブ状態にすれば、当然そのアプリが最前面表示されますが、その際にすべてのアプリより全面に表示させることでなんらかの情報をフォームに表示させて他のアプリを操作できます
これにはWinAPIを使用して、アクティブウィンドウとしてユーザーフォームのハンドル値を取得してそれに対して表示処理を設定します
これと同じようにWinAPIを使用して、×ボタンを使用不可にしたりする際にも使用します
このように、このイベントは大きく2通りの目的があるので、状況に合わせて使用してください