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.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.繰り返し処理（For Next)｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

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

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

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

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