ExcelのForm シートと連動させる

メルマガ発行内容

＜ExcelのForm シートと連動させる＞
こんにちは、三流プログラマーＫｅｎ３です。

今回は、
ExcelのFormで少し遊んでみます。
中身は単純に、移動のボタンを作ってデータ読み書きしただけです。

気楽に読んでください。

/*
 * １．Accessの入力フォームが簡単な理由
*/

Accessでフォーム作るのって意外と簡単ですよね。
入力フォーム作る時、
テーブルやクエリーのデータと連動させるので、
簡単にそれなりの入力フォームを作ることが出来ると思います。

あと、ウィザードがあって、テーブルから半自動的にフォームを生成してくれたり。

まぁ、変なことばっかり言ってると、
Ｅｘｃｅｌユーザーに嫌われるので、
今回は軽くＥｘｃｅｌでデータ確認のフォームを作ってみます。

/*
 * ２．前回の自分と対決するか（笑）仕様を考える
*/

前回、
In message "[VBAで楽しく No.041] - AccessのForm .Pictureプロパティで画像参照",
 >テーブルの作成
 >^^^^^^^^^^^^^^
 >データは、テーブル名を安易に画像リスト
 >フィールドは、
 >ID オートナンバー型
 >F_TITLE     テキスト型  画像のタイトルを入れます
 >F_GFILENAME テキスト型  画像ファイル名（001.jpgとフォルダー抜きのファイル名）
 >F_MEMO 　   メモ型      コメントなど
 >にします。
---
とAccessのテーブルで管理してたっけ、
こいつを、
シート名をDATA
１行目は見出しの行にして、
２行目から、
A列 画像タイトル
B列 画像ファイル名
C列 コメント
でデータを簡単にシートで管理しますか。

あっ、忘れてた、画像の保存してあるベースのディレクトリも管理してたっけ、
これは、ＤＡＴＡ！Ｅ１に入れとけばイイヤ。

シートのイメージは
_____A_____________B_________C____________D_______________________E_______
1 画像タイトル ファイル名 コメント   画像ベースディレクトリ→  D:\temp\
2 サンタ       001.gif    ケーキ写真
3 お正月       012.jpg    XXXXXXXXXX
4 

といった感じにします。


/*
 * ３．Excelのユーザーフォームを作成する
*/

軽く下記のような部品を使いました。
（フォームに貼ったコントロールの一覧です）

タイトル
___________ txtTITLE                
ファイル名
___________ txtFILENAME
コメント
___________ txtMEMO    

┌────┐
│前へ戻る│btnPREV
└────┘
┌───┐
│ 次へ │  btnNEXT
└───┘
┌────┐
│        │imgBOX(イメージコントロール)
└────┘
を作成しました。
Excelフォームの作成サンプルは
http://www.ken3.org/vba/excel-form.html
に軽くまとめてあるので、見てください。


が作成した画像です。
美的センスの無さを痛感しつつ、中身で勝負にもってくか
（フォーム作りや色使い、うまい人いますよね、正直うらやましいです）

/*
 * ４．中身で勝負？コードを書き込む
*/

部品を貼れたので、コードを書きます。
外見がイマイチなので、中身はまともにいきたいですよね（冗談です）

ＶＢＡのプログラムは、
イベント（タイミング）を考えながらやると、
わかり易いと思います。

考えられるタイミングは、
移動の２つのボタンを押した時の処理ですね。
┌────┐
│前へ戻る│btnPREV は、前のデータを表示
└────┘
┌───┐
│ 次へ │  btnNEXTは、次のデータを表示
└───┘
とすると、現在表示されている行を何かで取っておかないといけないか。
＊グローバル変数の使い方、説明してないけど今回、使っちゃいます。

テキストボックスでデータが変更されたらシートの値も変えないとなぁ。

それと、フォームを開いた時に初期データを表示しないとね。

フォームを開いた時、
^^^^^^^^^^^^^^^^^^^
データ（２行目の初期データ）をまず表示したいので、
プログラムを書いてみたいと思います。
SubやFunctionの外に変数を宣言すると、
共通に使えるので、行カウンタの意味を込めて変数ｙＣＮＴを作成します。
Dim yCNT As Integer '行カウンター


次にフォームを開いた時のイベントを書きます
Private Sub UserForm_Initialize()

End Sub
_Initializeが該当イベントなので、
この中でやることは、
カウンターの初期値を２（行目）にする。＊タイトルが１行目なので
タイトル
[ ___________ ] txtTITLE                
ファイル名
[ ___________ ] txtFILENAME
コメント
[ ___________ ] txtMEMO    
のテキストボックスにシートの値（セルの値）を代入する
と
同時に、画像ファイルを表示
の大きく分けると３つかぁ。

Private Sub UserForm_Initialize()
    
    Dim strFNAME As String 'ファイル名作成用
    
    '初期処理
    yCNT = 2   '初期値の２行目を代入
    
    'セルの値をフォームのテキストボックスに代入する
    txtTITLE = Sheets("DATA").Cells(yCNT, "A")
    txtFILENAME = Sheets("DATA").Cells(yCNT, "B")
    txtMEMO = Sheets("DATA").Cells(yCNT, "C")
    
    '画像ファイルを表示する
    If Trim(txtFILENAME) = "" Then  'ファイル名が入力されているかチェック
        imgBOX.Picture = LoadPicture("")  '画像を消す
        Exit Sub
    End If
    strFNAME = Sheets("DATA").Range("E1") & txtFILENAME 'ファイル名を作成
    If Dir(strFNAME) = "" Then 'ファイルが存在するかチェック
        '存在していない時
        imgBOX.Picture = LoadPicture("") '画像を消す
    Else
        imgBOX.Picture = LoadPicture(strFNAME)  'ファイル名をセット
    End If
    
End Sub

初期処理
~~~~~~~~
    yCNT = 2   '初期値の２行目を代入
もう見たまんまですよね。
２行目の２を代入してます。

セルの値をフォームのテキストボックスに代入する
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    txtTITLE = Sheets("DATA").Cells(yCNT, "A")
    txtFILENAME = Sheets("DATA").Cells(yCNT, "B")
    txtMEMO = Sheets("DATA").Cells(yCNT, "C")
これもそのまま代入してます。
あっ、Sheets("DATA").Cells(yCNT, "A")の説明もして無いや。。。
イメージはわかると思うので今回はやらないけど、
近いうちにSheetsオブジェクト、Cellsオブジェクト説明します。

画像ファイルを表示する
~~~~~~~~~~~~~~~~~~~~~~
    If Trim(txtFILENAME) = "" Then  'ファイル名が入力されているかチェック
        imgBOX.Picture = LoadPicture("")  '画像を消す
        Exit Sub
    End If
ファイル名が入力されている時だけ処理したいので、
入力されていない場合は、
imgBOX.Picture = LoadPicture("")  '画像を消す
と画像を消してから、関数をExit Subで途中バックレしてます。

    strFNAME = Sheets("DATA").Range("E1") & txtFILENAME 'ファイル名を作成
ここは、ファイル名のフルパスを作成してて、
DATA!E1のセルに基本のディレクトリが入っているので、
それと、テキストボックスのファイル名を＆で連結してます。

    If Dir(strFNAME) = "" Then 'ファイルが存在するかチェック
        '存在していない時
        imgBOX.Picture = LoadPicture("") '画像を消す
    Else
        imgBOX.Picture = LoadPicture(strFNAME)  'ファイル名をセット
    End If
お約束のＤｉｒ関数でファイルの存在をチェックして、
””のファイル無しの時は、
imgBOX.Picture = LoadPicture("")
と画像を消して、在りの時は、
imgBOX.Picture = LoadPicture(strFNAME)
で、画像をセットしてます。
注).Picture の使い方がAccessと違って、LoadPicture関数の戻り値をセットです。

前、次のボタンが押された時
^^^^^^^^^^^^^^^^^^^^^^^^^^
イベントのタイミングであるのが、
前、次のボタンが押された時ですよね。
普通に考えて、
前のボタンが押されたら、カウンタのyCNT−１
次のボタンが押されたら、カウンタのyCNT＋１
ですよね。
その後、データセットと画像表示です。
┌────┐
│前へ戻る│btnPREV は、前のデータを表示
└────┘
┌───┐
│ 次へ │  btnNEXTは、次のデータを表示
└───┘
の_Clickイベントにコードを書きます

Private Sub btnNEXT_Click()
    
    Dim strFNAME As String 'ファイル名作成用
    
    '次の位置を計算
    yCNT = yCNT + 1   '行カウンタを増やす
    
    'セルの値をフォームのテキストボックスに代入する
    txtTITLE = Sheets("DATA").Cells(yCNT, "A")
    txtFILENAME = Sheets("DATA").Cells(yCNT, "B")
    txtMEMO = Sheets("DATA").Cells(yCNT, "C")
    
    '画像ファイルを表示する
    If Trim(txtFILENAME) = "" Then  'ファイル名が入力されているかチェック
        imgBOX.Picture = LoadPicture("")  '画像を消す
        Exit Sub
    End If
    strFNAME = Sheets("DATA").Range("E1") & txtFILENAME 'ファイル名を作成
    If Dir(strFNAME) = "" Then 'ファイルが存在するかチェック
        '存在していない時
        imgBOX.Picture = LoadPicture("") '画像を消す
    Else
        imgBOX.Picture = LoadPicture(strFNAME)  'ファイル名をセット
    End If

End Sub

Private Sub btnPREV_Click()
    
    Dim strFNAME As String 'ファイル名作成用
    
    '前の位置を計算
    yCNT = yCNT - 1   '行カウンタを減らす
    
    'セルの値をフォームのテキストボックスに代入する
    txtTITLE = Sheets("DATA").Cells(yCNT, "A")
    txtFILENAME = Sheets("DATA").Cells(yCNT, "B")
    txtMEMO = Sheets("DATA").Cells(yCNT, "C")
    
    '画像ファイルを表示する
    If Trim(txtFILENAME) = "" Then  'ファイル名が入力されているかチェック
        imgBOX.Picture = LoadPicture("")  '画像を消す
        Exit Sub
    End If
    strFNAME = Sheets("DATA").Range("E1") & txtFILENAME 'ファイル名を作成
    If Dir(strFNAME) = "" Then 'ファイルが存在するかチェック
        '存在していない時
        imgBOX.Picture = LoadPicture("") '画像を消す
    Else
        imgBOX.Picture = LoadPicture(strFNAME)  'ファイル名をセット
    End If

End Sub

ポイントは、
yCNT = yCNT + 1   '行カウンタを増やす
yCNT = yCNT - 1   '行カウンタを減らす
の増減です。
カウンターを移動させて、代入位置を変化させて対応してます。

ね、なんとか動作したでしょ。
なんかオレ、頭イタクなってきた。
なんで？

よくそんな同じ処理をダラダラ書けるよね。
カウンターの意味わかってないんじゃないの？
＊関数にまとめるのも次回かなぁ（先送りが多いよねこのメルマガ（笑））

テキストボックス入力後、シートに値をセットする
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
初期処理、前・次移動のボタンができたから、
次は、テキストボックスでデータが修正された時だよね。
タイトル
[___________] txtTITLE    A列
ファイル名
[___________] txtFILENAME B列
コメント
[___________] txtMEMO     C列
だから、それぞれ、_Changeのイベントで

Private Sub txtMEMO_Change()
    'テキストボックスの値をシートへ代入
    Sheets("DATA").Cells(yCNT, "C") = txtMEMO
End Sub

Private Sub txtTITLE_Change()
    'テキストボックスの値をシートへ代入
    Sheets("DATA").Cells(yCNT, "A") = txtTITLE
End Sub

値を普通に代入。
ファイル名は変わったら、画像も変えなきゃいけないので、
Private Sub txtFILENAME_Change()
    'テキストボックスの値をシートへ代入
    Sheets("DATA").Cells(yCNT, "B") = txtFILENAME
    '画像ファイル名が変更されたので、画像の処理を行う
    If Trim(txtFILENAME) = "" Then  'ファイル名が入力されているかチェック
        imgBOX.Picture = LoadPicture("")  '画像を消す
        Exit Sub
    End If
    strFNAME = Sheets("DATA").Range("E1") & txtFILENAME 'ファイル名を作成
    If Dir(strFNAME) = "" Then 'ファイルが存在するかチェック
        '存在していない時
        imgBOX.Picture = LoadPicture("") '画像を消す
    Else
        imgBOX.Picture = LoadPicture(strFNAME)  'ファイル名をセット
    End If
End Sub
と画像の表示処理を追加してと、ＯＫできたぁ。

ＯＫできたぁ。じゃねぇよ。
ここでも画像の同じ処理書くの？

えっ、だって、画像のファイル名が変わったから、同じ処理しなきゃ。

う〜ん、ダメか今日は。。。（同じ処理をまとめるのは次かなぁ、、まったく）


/*
 *　５.おわりの挨拶
*/

今回は、
・Excelフォームとシートのデータを連動させてみた
・注).Picture の使い方がAccessと違って、LoadPicture関数の戻り値をセット
でした。

残は、多くって
・グローバル変数って何？
・シート！セルの値の表現方法
・同じ処理をまとめるには？
・前のボタンを押しつづけるとエラー（そんなの本文で一言も書いてないよ）
・次のボタンを最終データ以降も押せる（そんなの本文で一言もフレテナイよ）
書きたくないほどいっぱいの課題アリ（笑）




サンプルファイルは、
http://www.ken3.org/vba/lzh/vba042.lzh
に
test042-book.xlsが保存されています。
.jpgや.gifを指定して、遊んでみてください。

拾い読みして、
１つでも何かの参考となれば幸いです。

Ｅｘｃｅｌ／Ａｃｃｅｓｓ大好き、三流プログラマーＫｅｎ３でした。

ページフッター

ここまで、読んでいただきどうもです。ここから下は、三流君のホームページの紹介・案内です
目的の情報が見つかったか？少々心配しつつ、、、

種類別のリンク や 広告など

Blogとリンク:[三流君の作業日記]/ [VBAやASPのサンプルコード]/ 広告-[通販人気商品の足跡]