VBA100本ノック 68本目：全テキストボックスの転記

公開日：2021-01-16　最終更新日：2021-02-22

VBA100本ノック 68本目：全テキストボックスの転記

ユーザーフォームの全テキストボックスをシートに転記する問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

#VBA100本ノック 68本目
フォームの「登録」ボタンをクリックしたら、全てのTextBoxの値をアクティブシートに出力した後にフォームを閉じてください。
シート出力位置
・データ最終行のすぐ下の行
・シートの1行目にあるコントロール名で探した列
・列が見つからない場合は、右端に追加してください
・改行は全て削除する

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_68.xlsm
https://excel-ubara.com/vba100sample/VBA100_68.zip

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

フォームの全てのコントロールはMe.Controlsで取得できます。
この中からTypeName(...)="TextBox"でテキストボックスだけにします。
テキストボックス内の改行はCRLFです。


  
    
      Private Sub btn登録_Click()

　　Dim ws As Worksheet: Set ws = ActiveSheet

　　

　　Call setControls2Sheet(ws, "TextBox")

　　

　　Unload Me

End Sub


Private Sub setControls2Sheet(ByVal ws As Worksheet, ByVal aType As String)

　　Dim nextRow As Long: nextRow = getNextRow(ws)

　　Dim ctl As Control

      　　For Each ctl In Me.Controls

　　　　If TypeName(ctl) = aType Then

　　　　　　ws.Cells(nextRow, getColumn(ws, ctl.Name)).Value = delCrLf(ctl.Value)

　　　　End If

　　Next

End Sub


Private Function getNextRow(ByVal ws As Worksheet) As Long

　　getNextRow = ws.Range("A1").CurrentRegion.Rows.Count + 1

End Function

　　

Private Function getColumn(ByVal ws As Worksheet, ByVal aColName As String)

　　On Error Resume Next

      　　getColumn = WorksheetFunction.Match(aColName, ws.Rows(1), 0)

　　If Err.Number = 0 Then Exit Function

　　

　　getColumn = ws.Range("A1").CurrentRegion.Columns.Count + 1

　　ws.Cells(1, getColumn) = aColName

End Function


Private Function delCrLf(ByVal aValue As String) As String

　　delCrLf = Replace(Replace(aValue, vbCr, ""), vbLf, "")

      End Function

列の検索方法はいろいろな書き方がありますが、ここではMatch関数を使ってみました。
プロシージャーを細かく分けたので、全体としては長くなっています。
記事にVBAを掲載しました。補足はありません。

補足

サイト内関連ページをご覧ください。

サイト内関連ページ

ユーザーフォーム入門

エクセル(Excel)マクロ(VBA)をやっていればユーザーフォームの存在を知り、そしてユーザーフォームを使いたくなります。使いたくなるというより、使った方が良い場合が出てきます。しかし、ユーザーフォームは少々とっつきにくく、使い始めて使いこなすには、各種コントロールの特性を理解し、イベントについても深い知識が必要…

第7回．テキストボックスの値をセルへ

・前回までに作成したユーザーフォーム・ワークシートの用意・OKボタンクリック時のイベントプロシージャー作成・ユーザーフォームのインテリセンス

第22回．コントロールの動的作成

・コントロールの動的作成の動作説明・コントロールの表示/非表示を切り替える・動的に部品コントロールを追加/削除する・コントロールの動的作成の最後に

第23回．イベントプロシージャーの共通化

・サンプルのユーザーフォーム・イベントプロシージャーの共通化のVBA ・イベントプロシージャーの共通化の問題点・イベントプロシージャーの共通化の雛形VBA ・イベントプロシージャーの共通化の最後に

第24回．イベントプロシージャーの共通化（Enter,Exit）

・イベントプロシージャーの共通化の問題点・問題解決した経緯・API:ConnectToConnectionPointについて・ConnectToConnectionPointの使用例・イベントと対応するVB_UserMemIdの一覧とインポート用雛形・イベントプロシージャーの共通化の最後に

同じテーマ「VBA100本ノック」の記事

65本目：固定長テキスト出力
 66本目：全サブフォルダからファイルを探す
 67本目：ComboBoxとListBox
68本目：全テキストボックスの転記
69本目：ダブルクリックでセル結合を解除
 70本目：ステータスバーに1秒ごとに時刻が表示
 71本目：パワーポイントへグラフを貼り付け
 72本目：ITをDXに変換（文字列操作）
73本目：新規ブックにボタン作成しマクロ登録
 74本目：1シート複数表をDB形式に変換
 75本目：ログインフォームの作成

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
68本目：全テキストボックスの転記

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA練習問題
VBA100本ノック 68本目：全テキストボックスの転記