ユーザーフォーム入門
フォームのプロパティ

ユーザー フォーム が作業ウィンドウ固定の場合、
ユーザーはアプリケーションの他の部分を使用する前に情報を提供するか、 ユーザー フォーム を閉じる必要があります。
ユーザー フォーム が非表示になるかアンロードされるまで、以降のコードは実行されません。
つまり、モーダルの場合、
バックにあるシートの操作は出来なくなります。
ユーザー フォーム がモードレスの場合、
ユーザーは、 ユーザー フォーム を閉じることなく、他のフォームまたはウィンドウを表示できます。
モードレスのフォームはタスク バーに表示されず、ウィンドウ タブ オーダーに従っていません。
つまり、モードレスの場合、
バックにあるシートの操作が可能となります。

モーダル表示と言います。
モーダルとは、Windowsに良くあるダイアログ画面です。
モーダルの場合は、
ユーザーフォームが表示されている間

モードレス表示と言います。
モードレスとは、画面表示中に他の処理ができる表示になります。

モードレスの場合は、
ユーザーフォームが表示されている間

Showメソッドには引数として、FormShowConstants列挙を指定できます。
引数を省略した場合は、ShowModalプロパティに従いユーザーフォームが開かれます。
Showメソッドの引数で、FormShowConstants列挙のメンバ値を指定することで、
ShowModalプロパティの値に関わらず、モーダル/モードレスを指定する事が出来ます。
UserForm.Show vbModal ・・・ モーダル表示
UserForm.Show vbModelessl ・・・ モードレス表示

多くの場合はモーダルで良いはずで、これが既定となっています。
シートのボタン押下等でフォーム画面を表示し、フォーム画面のOKボタンでシート操作に戻る。
良くあるこのような操作で使うのがモーダルになります。
対してモードレスは、フォーム画面操作とシート操作を交互にどちらも操作したい場合に使います。
シートのボタン押下等でフォーム画面を表示した後、
シート操作→フォーム画面操作→シート操作→フォーム画面操作・・・
このような操作を実現したい場合にモードレスを使います。
良くある例としては、
・シート操作の補助機能として複数のセル範囲の選択をさせる

モーダル/モードレスにより、VBAの構造は大きく違ってきます。
なにより設計からして全く違ってきます。
モードレスの場合はシート操作ができるので、アクティブシートも随時変化します。
したがって、モーダルに比べるとモードレスの方が設計が大変になる場合が多いでしょう。