[エクセルVBA]セルの値をテキストボックスに表示

質問

よろしくお願いします。

ユーザーフォームを使って作業しています。
その中で、コンボボックス１で指定した名前を検索値として、VLOOKUPを使ってあるシート（例：シート名「時間検索」）からセルの値をテキストボックスに表示させるようにしたいのです。

コードを作成し表示させることはできたのですが、セルに入っている値の書式が「[h]:mm」の時間形式（例：19:00）のためか、セルの値を引っ張ると小数点（例：0.79166....)で表示されます。

色々と調べてみた中で、format関数を使ったりしてみたんですが不首尾に終わっています。

なんとかテキストボックスに、セルと同じ形式で表示したいのですが、どのように考えればよろしいでしょうか？
ご教示お願いいたします。

質問の仕方が悪かった場合は補足・訂正させていただきますので、どうぞよろしくお願いします。

回答No.3

keithin · ベストアンサー

方法１
textbox1 = format(application.vlookup(combobox1, worksheets("シート名").range("検索対象範囲"),列,false), "[h]:mm")

方法２
dim r as long
r = application.match(combobox1, worksheets("シート名").range("左端列：列"), 0)　　’★
textbox1 = worksheets("シート名").cells(r, "欲しい列番号").text

★言わずもがなですが
　range("A:A")のように列：列指定して検索すれば直接の行番号が取得できる
　range("A4:A10")のように範囲指定したときは，勿論その先頭セルからの移動量が取得される

方法３
dim h as range
set h = worksheets("シート名").range("左端列").find(what:=combobox1, lookin:=xlvalues, lookat:=xlwhole)
if not h is nothing then
textbox1 = h.offset(0, 列数).text
end if

＃各方法共通：
Comboboxの値は「文字列」なので，シート上の「数値」を検索する場合は変換が必要です

お礼

ありがとうございます。
方法１が一番しっくりきましたので試してみたところばっちり動きました。VLOOKUP全体をフォーマット関数で規定すればよかったんですね！これで今後も応用できそうです。

方法２と３は、今回は上記操作を複数回行わなければいけない関係上、長さの面から私にはまだ早いと感じました。今後コードを熟読して理解していきたいと思います。

本当にありがとうございました！

追記
コンボボックスでは文字列を扱っているため、今回は問題なく検索できました。今後の参考にさせていただきます。

回答No.4

mt2008

ANo.1です。
何に困っているのかようやく判りました。
セルでは表示書式で24時を超える時間を表示できるが、VBAでは出来なくって困っているって事ですよね？

案1.Vlookupをワークシート上でやって、その結果セルのTextプロパティをTextBoxに入れる

案2.Application.WorksheetFunctionで24時を超える表示にしてしまう。

nRtn = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("○○○").Range("A3:G200"), 3, False)
TextBox1.Value = Application.WorksheetFunction.Text(nRtn, "[h]:mm")

お礼

たびたびお手数をおかけしました、ありがとうございます。

案２がすごくわかりやすかったです。一度変数に格納してそれをのちに変更するという形ですね。
こういったこともできるのかと目からうろこが落ちる思いです。

今回はN0.3さんの案をいただきましたが、こういったアプローチも今後必要になってくるかもしれませんので、大切に脳内にとどめておきたいと思います。

ありがとうございました！

回答No.2

imogasi

VBAの質問らしいが、エクセルの基礎が判ってないのに、VBAなどやろうとしている。
（１）エクセルの時刻データの「セルの値」は２４時間を１とする、小数点の値なのだ。
0.79166など当たり前。日付・時刻シリアル値のことが判ってない。
Googleででも照会して勉強のこと。
（２）関数でも出来ることをVBAなどでやってないか。関数は「セルの値」を判別・加工・計算するもの
VBAでセルの値などを触るときも、普通は「セルの値」を扱っていることが多いので認識すること。
セルの持つ情報の多畳性（セルの値、標示形式、コメントなど色々あるのだ）を判っていなくて、セルの値と表示形式が分けて理解されていないのでは。
書式については関数では触れられない。だから書式は式を入れたセルに別途設定しないとならない。
ただしTEXT関数（VBAではFormat）で表示形式を適用した結果にすることは出来る。
例
A1：A2
1 00:30 1 0:30:00
2 01:30 2 1:30:00
D1:E2
1 0:30:00
2 1:30:00
ーーー
B1セルに　=TEXT(VLOOKUP(A1,$D$1:$E$2,2,FALSE),"hh:mm")
下方向に式を複写。
,"hh:mm"の部分は自由に。
但しTEXT関数を使うと、全体が文字列になる。
そうしないでセルの標示形式の設定をやるほうが良い場合が多いと思う。

お礼

正直なところ、基礎がわかっていればこんな質問はしていませんし、あなたの仰っていることはわかっています。セルの値を扱っていることがわかっているが、セルの値をどう時刻表示に変えるかがわからないので教えてほしいということです。
そして、フォームを使ってきわめて一般的に表示をしたいので、エクセル上でVLookupを使うことができないんです。というかできればやっております。

ともあれ、ありがとうございました。

回答No.1

mt2008

セルに表示したままの文字列をTextboxに入れたいなら
TextBox1.Value = Worksheets(1).Range("A1").Text

こんな感じでTextプロパティを使ってみてください。

お礼

早速のご返答ありがとうございます。
現在以下のような形になっております。

TextBox1.Value = Application.WorksheetFunction.VLookup(ComboBox1.Value, Sheets("○○○").Range("A3:G200"), 3, False)

Textプロパティの挟みかたがわからず困っております。