Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2016)
コンボボックスの値によってその後コピー&ペーストを変える
投稿日時: 17/07/12 18:26:01
投稿者: misaluna

VBAは、見よう見まねで使っていて良く分かっていません。
そのため、きっと変な書き方をしていると思うので申し訳ないのですが
宜しくお願いします。
 
Private Sub UserForm_Initialize()
    
        PPComboBox1.AddItem "●BBBBB"
        PPComboBox1.AddItem "●CCCCC"
               
        PPComboBox2.AddItem "●AAAAA"
        PPComboBox2.AddItem "●DDDDD"
 
     'コンボボックスの選択値
   
End Sub
 
Private Sub PPTextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 
  On Error Resume Next
      
  Workbooks("見積もり.xlsm").Sheets("見積書1").Range(PPTextBox3.Value).Value = PPComboBox2.Value
        
 'コンボボックス2の値は、テキストボックス3のセルに入る
  
End Sub
 
Private Sub PPPaste2_Click() ここを教えてください!!
    
 On Error Resume Next
      
   IF Workbooks("見積もり.xlsm").Sheets("見積書1").Range(PPTextBox3.Value).Value ="●AAAAA"
     
   Then
    
 'テキストボックス3のセルが●AAAAA ならば
 
   With Workbooks("見積データ.xlsm").Worksheets("定型")
   Intersect(.Range("B3:B11"), .AutoFilter.Range.Offset(0)).SpecialCells(xlCellTypeVisible).Copy
 
 'ワークブック「見積もりデータ」のワークシート「定型」のB3からB11をコピー
 
  End With
  
    Workbooks("見積もり.xlsm").Sheets("見積書1").Range(PPTextBox4.Value).PasteSpecial Paste:=xlPasteValues
 
 'ワークブック「見積もり」のワークシート「見積書1」のテキストボックス4の値のセルに貼り付け
 
>同様に●BBBBならば等続けて書きたいと思っています。
 
 End Sub

回答
投稿日時: 17/07/12 20:23:05
投稿者: simple

>同様に●BBBBならば等続けて書きたいと思っています。
ElseIf ・・・・
と続けて行ってもよいでしょうけど、
ここは、Select Case ステートメント を使うのがよいと思います。
ヘルプを調べてみてください。
 
ところで、On Error Resume Nextをむやみに使っている印象があります。
やめたほうがよいと思います。エラーを隠蔽してしまうからです。

回答
投稿日時: 17/07/13 10:29:56
投稿者: mattuwan44

意味がさっぱり解りませんね。
 
転記先に名前を付けているなら、
その名前を選択できるようにして選択すればいいと思いますが、
なんでテキストボックスが出てくるんですかね?
コンボボックスで選択してるんですよね???
 
Option Explicit
 
Private Const mcKeyA As String = "●AAAA"
Private Const mcKeyB As String = "●BBBB"
Private Const mcKeyC As String = "●CCCC"
Private Const mcKeyD As String = "●DDDD"
 
 
Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .AddItem mcKeyA
        .AddItem mcKeyB
    End With
     
    With Me.ComboBox2
        .AddItem mcKeyC
        .AddItem mcKeyD
    End With
End Sub
 
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    On Error Resume Next
    Application.Range(Me.TextBox1.Text).Value = Me.ComboBox2.Value
End Sub
 
Private Sub CommandButton1_Click()
    On Error GoTo ErrH
    Workbooks("見積データ.xlsm").Worksheets("定型").AutoFilter.Range.Copy _
        Application.Range(Me.ComboBox2.Value)
    Exit Sub
 
ErrH:
    MsgBox "エラーです。セルの名前の定義状況を確認してください。"
End Sub
 
何と何があって何をしたときどうなればいいのですか?
前提条件を明確にしましょう。。。

投稿日時: 17/07/13 17:37:50
投稿者: misaluna

返信有難うございます。
コンボボックスの選択値がテキストボックス3に入れた
 ワークブック「見積もり」のワークシート「見積書1」の
 セル(例:A20)に入るようにしています。
そのセルA20に入る文字によって
ワークブック「見積もり」のワークシート「見積書1」の
 テキストボックス4のセル(例:A21)に貼り付ける文字を変えたいと思っています。
●AAAAAならば、ワークブック「見積もりデータ」のワークシート「定型」のB3からB11
をコピーした値を貼り付、
コンボボックスの選択値(セルA20の値)が●DDDDD
ならばB12からBの15を貼り付けたいと思っています。
こんな感じの説明で通じるでしょうか?
 宜しくお願いします。m(_ _)m

回答
投稿日時: 17/07/13 21:18:58
投稿者: mattuwan44

misaluna さんの引用:
返信有難うございます。
コンボボックスの選択値がテキストボックス3に入れた
 ワークブック「見積もり」のワークシート「見積書1」の
 セル(例:A20)に入るようにしています。
そのセルA20に入る文字によって
ワークブック「見積もり」のワークシート「見積書1」の
 テキストボックス4のセル(例:A21)に貼り付ける文字を変えたいと思っています。
●AAAAAならば、ワークブック「見積もりデータ」のワークシート「定型」のB3からB11
をコピーした値を貼り付、
コンボボックスの選択値(セルA20の値)が●DDDDD
ならばB12からBの15を貼り付けたいと思っています。
こんな感じの説明で通じるでしょうか?
 宜しくお願いします。m(_ _)m

 
いや、意味が分かりません。
 
コンボボックスで選択した値を、
テキストボックスに転記して、
それをまたシートに転記していて、
コマンドボタンを押したときに、
そのシートの値をみてコピー元を変えて、
所定の位置にコピペしたいということですか?
 
なんで、
そんな回りくどいことをするのですか?
 
コマンドボタンを押したときにコンボボックスの値で、
コピペの範囲を変えればよくないですか?
 
>Workbooks("見積もり.xlsm").Sheets("見積書1").Range(PPTextBox4.Value).PasteSpecial Paste:=xlPasteValues
↑テキストボックスは名前の定義をした名前ではなくて、セルアドレスが入っているんですか?

投稿日時: 17/07/14 10:10:33
投稿者: misaluna

TextBoxにはセルアドレスを入れています。
 
ComboBoxで、タイトルを選んで(例:●AAAAA)
TextBox1にタイトルを入力したいセルアドレス(例:A20)を入れます。
 
→A20にタイトル●AAAAAが入る。
 
タイトルによって項目が変わります。
 
そこで、ワークブック「見積もりデータ」のワークシート「定型」から
●AAAAAの項目B3〜B11をコピーしてきて
TextBox2に入力したセルアドレス(例:B21)に貼り付けます。
 
要約するとタイトルを選んでそれに対する項目数行をコピーして
貼り付けです。
(入力は、固定したセルではなく好きなセルに入れたいのでセルアドレスを指定します。)
 
こんな感じで分かっていただけますでしょうか。
 
現在ComboBoxで、タイトルを選んで(例:●AAAAA)
TextBox1にタイトルを入力したいセルアドレス(例:A20)を入れるところまでは
うまくいっています。
なので、その文字によって項目を選んでコピーして指定したセルに入力できるようにしたいと思っています。
 
宜しくお願いします。m(_ _)m

回答
投稿日時: 17/07/14 15:39:31
投稿者: mattuwan44

う〜ん。
 
いま、やりたい一連の作業の流れを説明してもらえますか?
日本語で説明できることがプログラミングの第一歩と考えます。
 
例>>
1)ユーザーフォームのコマンドボタンクリックでプログラム起動
2)(どのブックのどのシートのどのセルの値を見て)
もし、それの値がAならどうする。
そうでなくて、もし、その値がBならどうする。
そうでなくて、もし、その値がCならどうする。
それ以外なら、どうする。
3)プログラム終わり

投稿日時: 17/07/14 17:22:47
投稿者: misaluna

1.ワークブック「見積もり」のシート「見積書」上のボタンを押すとユーザーフォームがでてくる。
2.ユーザーフォームのコンボボックス2で(●AAAAAA)を選ぶ。
3.ユーザーフォームのテキストボックス3に(●AAAAA)を入力したいセルを入力(例:A20)
 →A20に●AAAAAがはいる
 
ここまでは動いています。
 
4.ユーザーフォームのテキストボックス4にタイトル●AAAAAの場合の項目を
 入力したいセル(例:B21)を入力
5.ユーザーフォームのボタンを押す
6.●AAAAAの場合の項目をワークブック「見積もりデータ」のシート「定型」の(例:B10〜15)を
 コピーしてきてワークブック「見積もり」のシート「見積書」B21に貼りつく
 
7.2.のコンボボックス2でタイトル(●BBBBB)と違うものを選ぶと6.でコピーするセルが違ってきます。
 B16〜18等
 
こんな感じでつうじますでしょうか・・・
 

回答
投稿日時: 17/07/15 07:14:09
投稿者: simple

横から失礼します。
 
ご指摘のとおり、コンボボックスを直接参照せずに、
なぜテキストボックスを経由するのか不明ですね。
 
Select Case の使い方は、こんな風なことなんでしょうか?
 

Private Sub PPPaste2_Click()
    Dim ws_s As Worksheet   ' source sheet
    Dim ws_d As Worksheet   ' destination sheet
    
    Set ws_s = Workbooks("見積データ.xlsm").Worksheets("定型")
    Set ws_d = Workbooks("見積もり.xlsm").Sheets("見積書1")
    
    ''Select Case ws_d.Range(PPTextBox3.Value).Value
    
    Select Case ComboBox2.Value
    Case "●AAAAA"
        Intersect(ws_s.Range("B3:B11"), ws_s.AutoFilter.Range).Copy
        ws_d.Range(PPTextBox4.Value).PasteSpecial Paste:=xlPasteValues
    Case "●BBBBB"
        Intersect(ws_s.Range("B16:B18"), ws_s.AutoFilter.Range).Copy
        ws_d.Range(PPTextBox5.Value).PasteSpecial Paste:=xlPasteValues
    End Select
End Sub

いくつか気づき。
・オートフィルタが掛かっているときは、可視セルという限定をしなくても
  可視セルだけがコピーされる仕様です。(限定しても間違いではないですが)
 
・オートフィルタを使用しているのに、なぜ、行方向の限定("B3:B11"や、"B16:B18")を
  するのですか?
  その都度、Autofilterを更新することで対応できないのですか?
 
・書込先のアドレスをテキストボックスで指定するのも、ちょっとという感じです。
  予め決まっているなら、2列のコンボボックスで値と書込先のアドレスを指定すれば、
  コンボボックスを選択するだけで済みます。

回答
投稿日時: 17/07/17 10:15:20
投稿者: mattuwan44

'1)ユーザーフォームのコマンドボタンクリックでプログラム起動
Private Sub CommandButton1_Click()
    Dim strFrom As String       'コピー元のセルアドレス
    Dim strTo As String         '貼り付け先のセルアドレス
    
    strFrom = Me.TextBox1.Text
    
    '2)テキストボックスに入力された文字を見て条件分岐する
    Select Case Me.TextBox2.Text
        'もし、それの値がAならどうする。
        Case "A"
            strFrom = "B10:B15"
        'そうでなくて、もし、その値がBならどうする。
        Case "B"
            strFrom = "B16:B18"
        'そうでなくて、もし、その値がCならどうする。
        Case "C"
            strFrom = "???"
        'それ以外なら、どうする。
        Case Else
            strFrom = ""
    End Select
        
    '3)ワークブック「見積もりデータ」のシート「定型」の(例:B10〜15)を
    ' コピーしてきてワークブック「見積もり」のシート「見積書」B21に貼りつく
    Workbooks("見積もりデータ").Worksheets("定型").Range(strFrom).Copy Workbooks("見積もり").Worksheets("見積書").Range(strTo)
'プログラム終わり
End Sub

 
こんな風に作業の手順を書き連ねるだけなので、
もっとシンプルに説明できるようになってください。
変わる部分に「変数」を使うのがコツですかね?
 
で、話変わりますが、
テキストボックスにセルのアドレスを入力するのはキーボードから手入力ですよね?
誤入力の抑止はどのように考えているのでしょうか?
それから、そうすると手がキーボードに行ったりマウスに行ったり、
大変ですよね?
わざわざ、作業を難しくしている印象です。
普通にマウスで選択してコピペしたら、手がマウスだけでいいので、
そちらのほうがめんどくさくないと思います。
マウスを使いこなせないなら、マウスの練習をしてもらったほうが、
マクロを作るより、より有効だと思いますがいかがでしょうか?
 
また、文字入力が必須な場合は、
ショートカットキーなどを利用して、
手をマウスにできるだけ移動することのないように、
してあげるのが親切かなぁと思います。
 
 

回答
投稿日時: 17/07/17 16:52:50
投稿者: WinArrow
投稿者のウェブサイトに移動

  

引用:
Workbooks("見積もり.xlsm").Sheets("見積書1").Range(PPTextBox3.Value).Value = PPComboBox2.Value
          
 'コンボボックス2の値は、テキストボックス3のセルに入る
  

 
この説明では、コンボボックス2の値が、セルアドレスになっている
という前提でしょうが、果たして、その通りになっているのか?
 
ステップ実行で確かめてみましょう。

投稿日時: 17/08/04 17:34:00
投稿者: misaluna

 
他で忙しくなかなか手が付けられなかったのですが、
simpleさんの書いてくださったものを少し変更して思い通りに動くようになりました。
有難うございました。
mattuwan44 さんも何度も回答頂き考えて頂きまして有難うございました。
 
もう少し勉強して基礎くらいは分かるようになるよう努力してみます。