vba裏技,技,論理 - インプットボックスの出現位置の設定

 これは非常に特殊な技法である。インプットボックスはユーザーに入力させる大事なツールであるが、画面の中央にボックスが出現して、入力をしたいセルなどが隠れてしまうことがある。

 そこでインプットボックスが現れる位置を設定してそんな障害を回避したい要求が生まれる。ここで問題になるのが、これにはディスプレー画面の左上端からエクセルの画面が現れる距離が分からないことだ。これはvbaで自動的には知ることができない。インプットボックスが現れる位置を設定するにはこの位置を探ることからは始め、幾つかのステップを踏むことが必要になる。

1.エクセルの左上の画面までの距離を策定する
2.そこからインプットボックスを置く場所までの距離をvbaで書く
3.その二つの距離を足す
4.コーディングの前処理
5.全体のコーディングをまとめる
6.実行

1.エクセルの左上までの距離

v横距離=0
v縦距離=0
v=Inputbox(“名前を入力して下さい”, xpos:=v横距離, ypos:=縦距離)

というコーディングを書いておく。「xpos:」は横位置で、「ypos:」は縦位置だ。エクセルまでの距離はエクセルとは全く別の測定の仕方をしているので、「xpos:=」と「ypos:=」の後に数字を入れるのだが、これは試行錯誤でやるしかない。使用するディスプレーのサイズが違えば、距離(数値)が違ってくる。試しに

 v横距離は500
v縦距離は2000

とくらいに仮設定して上記のv水平=0の「0」の代わりに「500」を入れ,v垂直の「0」の代わりに「2000」を入れて

v=inputbox(“名前を入力して下さい”, xpos:=v横距離, ypos:=v縦距離)

を実行するとインプットボックスが出現する。実行は単にEnter Keyを押せば簡単だ。そうするとインプットボックスがxpos:=500, ypos:=2000の位置に現れる。行き過ぎれば数を少し小さくして、到達しなければ数を大きくする。例えば500ではなく520、2000を1980とかにして調整していく。

インプットボックスがエクセル画面のきっちり左上に来るまで調整を続ける。ぴったりと当てはまる位置にインプットボックスがくれば、それがディスプレー画面の左上端からエクセル画面の左上までの距離の数値だ。v横距離とv縦距離の数値が決まる。

もし横位置が515、縦位置が2150だとすると
v横距離=515
v縦距離=2150

が設定された。

2.そこからインプットボックスを置く場所までの距離をvbaで書く

Set cvisible = ActiveWindow.VisibleRange(1)
rwindowfix = ActiveWindow.SplitRow
cwindowfix = ActiveWindow.SplitColumn

vaddcc = Cells(1, 1).Column
vaddvisiblecc = ActiveWindow.VisibleRange(1).Column
vaddrr = Cells(1, 1).Row
vaddvisiblerr = ActiveWindow.VisibleRange(1).Row
If vaddcc = vaddvisiblecc Then
xエクセル位置 = v位置.Left
Else
xエクセル位置 = v位置.Left – cvisible.Left + Cells(cwindowfix + 1)
End If

If vaddrr = vaddvisiblerr Then
yエクセル位置 = v位置.Top
Else
If v位置.Row <= rwindowfix + 1 Then
yエクセル位置 = v位置.Top
Else
yエクセル位置 = v位置.Top – cvisible.Top + Cells(rwindowfix + 1)
End If
End If

これでエクセルの画面の左上からインプットボックスが出現する水平(xエクセル位置)および垂直の位置(yエクセル位置)が設定された。実はこの設定の条件はウインドー枠の固定やその他の理由で画面が当初位置から動いた時にも対応するための記述で、説明は省く。理解するよりそのまま記憶するかどこかに残しておいた方が簡単でしょう。

3.その二つの距離を足す

x位置 = xエクセル位置 * 20 + v横距離
y位置 = yエクセル位置 * 20 + v縦距離

設定された数値に「20」を掛けるのはエクセル制作上の係数だから論理はない。

これでエクセル画面の左上からインプットボックスが置かれる位置の左上までの距離(xエクセル位置* 20 = 横の距離)と(yエクセル位置* 20 = 縦の距離)が設定された。

v位置.topはエクセル画面の左上からv位置の上まで、v位置.leftはv位置の左までの距離を表す 。

従ってディスプレーの上端からインプットボックスが置かれる場所までの距離は下記の通りとなる。

v横距離 は500 +  xエクセル位置 * 20
v縦位置 は2150 +  yエクセル位置 * 20

なお上記のv位置はインプットボックスを置きたい位置である。具体的には後述する。

3.コーディングの前処理

次には2.のコーディングを一つのsub-precedure とする。この処置はインプットボックスが出て来る度にコーディングを書くのが面倒だからだ。プログラム名は任意だが例えば「インプットボックス位置設定」とか分かりやすいものにする。

Sub インプットボックス位置設定()
Set cvisible = ActiveWindow.VisibleRange(1)
rwindowfix = ActiveWindow.SplitRow
cwindowfix = ActiveWindow.SplitColumn

vaddcc = Cells(1, 1).Column
vaddvisiblecc = ActiveWindow.VisibleRange(1).Column
vaddrr = Cells(1, 1).Row
vaddvisiblerr = ActiveWindow.VisibleRange(1).Row
If vaddcc = vaddvisiblecc Then
xエクセル水平=v位置.Left
Else
xエクセル水平=v位置.Left – cvisible.Left + Cells(cwindowfix + 1,
End If

If vaddrr = vaddvisiblerr Then
yエクセル垂直 =v位置.Top
Else
If v位置.Row <= rwindowfix + 1 Then
yエクセル垂直 =v位置.Top
Else
yエクセル垂直=v位置.Top – cvisible.Top + Cells(rwindowfix + 1
End If
End If

v横距離=500
v縦距離=2150

x位置 = xエクセル水平 * 20 + v横距離
y位置 = yエクセル垂直 * 20 + v縦距離

End sub

 
4.前処理

sub-procedureを含めた全てのコーディングで変数を有効にするため、変数の宣言は忘れてはならない。これは全体のコーディングが始まる前に宣言する。

 次にインプットボックスを置きたい場所を決める。例えば
Selection.offset(, 1)とか、cells.find(“氏名”).offset(1)など任意の場所でいい。
決まったら

Set v位置 = Selection.offset(, 1)

とかにする。このv位置は2.の記述の中に出て来る変数だ。

5.全体のコーディングをまとめる

Public v位置 as variant, x位置 as variant, y位置 as variant
.
.
.
.
Set v位置 = selection.offset(, 1)
インプットボックス位置設定

v名前=Inputbox(“氏名を入力して下さい”, xpos:=x位置 , ypos:=y位置)

これでプログラム実行の準備が完了した。-

6.実行

Public v位置 as variant, x位置 as variant, y位置 as variant
.
.
.
Set v位置 = Selection.offset(, 1)
インプットボックス位置設定
v名前 = InputBox(“氏名を入力して下さい”, xpos:=x位置, ypos:=y位置)
v位置.value=v名前

これで選択されたセルの右一つ横の左上から始まってインプットボックスが出現する。名前を入力すると、選択されたセルの右に名前が入力される。

ディスプレーの左上端かあらエクセルの左上までの距離の設定は面倒だが、後は通常通りに実行していけばいい。因みに変数の文字の書き方では可動しないこともあるので、その時は別の変数に換える。換えるときは関係する全ての箇所で換えなければならない。

別稿に続く

vba酒巻 修平

—–

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です