[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『特定の範囲の値が変化したときにマクロを実行』（oyadi）
色々調べてみたのですが解らず、ご教授頂きたく書込みさせていただきます<(_"_)>
特定範囲（V37:V45）に、幾つかのフォームのチェックボックスのリンクがあり、
FALSE、TRUEが表示されるようにしてあります。
その値に変化があった場合、MailAdCopyというマクロを実行させたいのですが、
Private Sub Worksheet_Calculate()
If (Target.Row >= 37 And Target.Row <= 45) And Target.Column = 22 Then
MailAdCopy
End If
End Sub
とすると、実行時エラー'424' オブジェクトが必要です と出て実行できません。
試しに
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Row >= 37 And Target.Row <= 45) And Target.Column = 22 Then
MailAdCopy
End If
End Sub
として その範囲のセルを直接書き換えると MailAdCopyマクロは実行されます。
オブジェクト修飾子が足りないのでは？ とは思うのですが、何をどう足せば良いのか解らず悩んでおります。
どなたかご教授頂けないでしょうか。
何卒宜しくお願い致します。
Private Sub Worksheet_Change(ByVal Target As Range) これは、引数として Target As Range があって、プロシジャ内で、Targetの参照が可能。 一方、Worksheet_Calculate() 引数としては何も規定されていない。 逆にいえばどこかが計算されたということはわかるけど、どこが計算されたかという情報は渡されないということ。 いずれにしても規定されていないTargetを参照しようとしている。 Option Explicitの記述があれば変数の定義がないというコンパイルエラーになるところなので おそらく、記述なし。で、実行時に、オブジェクトじゃないのでエラー。
やるとすれば、領域内の各セルの値をすべて保存しておき、Calcurateイベントで、現在の値とどこがかわったかを調べる。 でも、あまりおすすめしないね、このやりかたは。 むしろ、フォームでチェックボックスをクリックされた時のイベントで処理するの素直だね。
ぶらっと立ち寄り
不躾で申し訳ございませんが、ご教授頂ければ幸いです。
何卒何卒宜しくお願い致します<(_"_)>
具体的にユーザーフォーム上の関連するチェックボックスの名前と、リンクしているセルを教えてくれる？
追加で、MailAdCopyのコードもアップしてもらえればありがたい。 ここに対して引数を与えていないけど、どんな処理なんだろうね。
ぶらっと立ち寄り
具体的には、表示＞ツールバー＞フォーム のチェックボックスをシート上に９つ配置し、
それぞれV37～V45まで9つのセルにチェックの有無で、FALSE、TRUE が表示されます。
チェックボックスの名前というか、代わりに表示される文字列は 空白 にして、下のセルに別セルのメールアドレスが表示されるようにしてあります。（=Sheet1!C22の様な感じです）
MailAdCopyは、
Sub MailAdCopy()
Sheets("Sheet1").Select
ActiveSheet.Unprotect
Worksheets("Sheet1").Range("B10:B18").Value = Worksheets("緊急メール").Range("Z37:Z45").Value
ActiveSheet.Protect
Sheets("緊急メール").Select
End Sub
恥ずかしながら・・・こんなものです(^^;
それぞれの9つのチェックボックスを右クリックして、マクロの登録 でMailAdCopyを登録すれば、今回の質問の結果が得られるのですが、実はそれに気が付くまでに寂しい思いをしまくりましたので、どうせならちゃんとマクロの勉強をしたいと思い質問させて頂きました。
初心者的な質問で、真に恐縮なのですが、何とかマクロでの記述方法をご教授頂ければ幸いです<(_"_)>
追加情報ありがとう。 ところで、Worksheet_Changeを使わず、あえてWorksheet_Calculateで処理する理由は？ それと、フォームの・・という言葉でユーザーフォームだと思ったんだけど、そうじゃなくフォームツールだったんだね。 いずれにしても、Worksheet_Changeが使えない理由が何かあるなら、考えてみるけど。
ぶらっと立ち寄り
そんなこともありまして、なんとかマクロの記述方法を会得いたしたく質問させて頂きました。
ご迷惑でしょうが、何卒ご教授頂ければ幸いです<(_"_)>
今、ゆっくりアップしてもらったコードを読んだ。 いくつかコード以外の、処理の構成で？？？のところが。 1.想像するに、緊急連絡が必要なメールアドレスをチェックボックスで選んでリストアップするんだよね。 2.で、チェックボックスだから、選択したり、選択解除したり・・だよね。 だけど、TrueでもFalseでも選択されたことになっているよ。 3.さらに、チェックされるたびに、無条件に９つのアドレスを緊急メールシートから転記してるよ？
本来は、 ・９つの中から必要なものにチェックする。 ・選択が終わったら、ボタン等を押して実行。選択されたものだっけを抽出。 こんな構成にしなきゃいけないんじゃない？ で、「はそれに気が付くまでに寂しい思いをしまくりましたので」の気持ちはわかるけどボタンにマクロ登録だよね。
今から、この線でコードを書いて、できあがったらアップします。
追伸 >Worksheet_Changeだと、FALSE、TRUEの表示は、計算結果に当たるらしく微動だにしてくれず シート上のコントロールだとそうだね。ユーザーフォームだと思い音でいたので。 Worksheet_Change は忘れてください。
ぶらっと立ち寄り
自分勝手な解釈で以下。
・チェックボックスからセルへのリンクは消しておいて。
・かりに配置したチェックボックスの名前が チェック 1 から チェック 9 だとする。
・シートにフォームツールのボタンを１つ配置して、そこにMailAdCopyをマクロ登録。
・で、標準モジュールに以下。
・最後の、Sheets("緊急メール").Select の意味がよくわからないけど、そのままにしてある。
Sub MailAdCopy()
Dim v(1 To 9, 1 To 1) As String
Dim c As Range
Dim i As Long, k As Long
With Sheets("Sheet1")
.Unprotect
For i = 1 To 9
If .CheckBoxes("チェック " & i).Value = xlOn Then
k = k + 1
v(k, 1) = Worksheets("緊急メール").Range("Z37").Offset(i - 1).Value
End If
Next
.Range("B10:B18").Value = v
.Protect
End With
Sheets("緊急メール").Select
End Sub
ぶらっと立ち寄り
＞無条件に９つのアドレスを緊急メールシートから転記
ですです。
別に メール送信のマクロがありまして、そのマクロの実行の為に、Sheet1の送信先アドレス記入欄(B10:B18)にメアドを記入する必要があるのですが、そこに式が入ると、送信時エラーが出るので、
つたない知識でMailAdCopyマクロを作成し、書き込んだメールアドレス以外は空白に、また、そのメールアドレスを変更する時の為にチェックボックスに表示されるメアドを他のシートに書込み、送る時に選べるようにしたいと考え、こんな変なことになっております。
（ですので、チェックボックスで選択メールアドレスに変更があった場合、一個でも変更があれば、そのままMailAdCopyが動作してくれるようにしたかったのです。）
マクロの知識が在れば、こんな変な物は作らないのかもしれませんが、なんとか自力で、とやったところこんな物に(^^: ＜ お恥ずかしいかぎりです・・・・・
実のところ 消防団での出火報等の配信に使うものを作成しております。
メーリングリストは作成されているようなのですが、その送り先が 幹部、団員、署員 等 幾つかのメーリングリスト自体の登録先に分かれているようでしたので 9つの配信先を 自由に選べるようなシートを作成しようと こんなことに・・・(^^:
元々は、HTMLにて作られていたようなのですが、作った人は既に誰だか解らず、送信先すら解らない代物だったので、どのPCからも一斉送信の出来る物を と EXCELでの作成を決意し、今に至っております。
しかし、私には知識が乏しく、思い通りのものが作れず、質問にいたった次第であります（T_T)
お手数お掛け致しました 本当に申し訳御座いませんが、何卒、何卒ご教授頂ければ幸いです<(_"_)>
>チェックボックスで選択メールアドレスに変更があった場合、一個でも変更があれば、そのままMailAdCopyが動作してくれるようにしたかったのです
う～ん・・・ たとえば２箇所選択を変更しようとした場合、１個変更してMailAdCopyが動き、２個目を選択変更して、またMailAdCopyが動く？ また、選択がどうあれ（選択がはずれても）メールアドレスを緊急メールシートから９つ転記？ それなら、マクロを動かさなくても、Sheet1のB10:B18に緊急メールシートのZ37:Z45と同じものを書いておけばそれでいいよね。 アドレスをSheet1に持ってくるかどうかを判断するためにチェックボックスがあるんじゃないの？ いまいち、明確に理解できないなぁ。 とりあえずアップした構成でアップしたコードを動かしてみてくれないかな？
ぶらっと立ち寄り
しかし、
If .CheckBoxes("チェック " & i).Value = xlOn Then
にて、実行時エラー'1004'
worksheetクラスのCheckBoxesプロパテイーを取得できません
と出て 実行に至りません（T_T)
初心者なりに ちょっと検索して勉強させていただきたいと思います！
本当にありがとうございます！（T_T)ｳﾙｳﾙ
ちなみに、最後の、Sheets("緊急メール").Select は、チェックボックスがそのシーﾄ上にありまして
チェックした＝そのシートを表示している（というより、そのシート以外、恥ずかしいから見ないで！の意味が込められております（笑
＞それなら、マクロを動かさなくても、Sheet1のB10:B18に緊急メールシートのZ37:Z45と同じものを書いておけばそれでいいよね
私もそのように思いまして そのような式を アドレス記入欄に記入したのですが、
式が入っている時点で、その式の記述の所にメールを送り、結果エラーで止まる
というメール送信マクロのようでして・・・(^^:
因みに
Sub Excel_Mail_Send()
' 変数設定
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Dim i, LastRow As Integer
' CDOオブジェクト初期設定
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = Worksheets("Sheet1").Range("C2").Value
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = Worksheets("Sheet1").Range("C3").Value
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Worksheets("Sheet1").Range("C6").Value
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = Worksheets("Sheet1").Range("C7").Value
.Update
End With
' 送信範囲設定
LastRow = Worksheets("Sheet1").Range("B9").End(xlDown).Row
' メール送信ループ
For i = 10 To LastRow
' 送信状況メッセージクリア
Worksheets("Sheet1").Range("F2").Value = ""
' メール本文作成
strbody = Worksheets("Sheet1").Range("F" & i).Value & vbCrLf & _
Worksheets("Sheet1").Range("G" & i).Value & vbCrLf & _
Worksheets("Sheet1").Range("H" & i).Value & vbCrLf & _
Worksheets("Sheet1").Range("I" & i).Value & vbCrLf & _
Worksheets("Sheet1").Range("J" & i).Value & vbCrLf & _
Worksheets("Sheet1").Range("K" & i).Value
' 改行変換（送信環境によってはここの修正が必要かも）
tmpstrbody = Replace(strbody, vbLf, vbCrLf)
strbody = Replace(tmpstrbody, vbCr & vbCrLf, vbCrLf)
' メール送信
With iMsg
Set .Configuration = iConf
.From = Worksheets("Sheet1").Range("C4").Value
.To = Worksheets("Sheet1").Range("B" & i).Value
.BCC = Worksheets("Sheet1").Range("C5").Value
.Subject = Worksheets("Sheet1").Range("C" & i).Value
.TextBody = strbody
.Send
End With
' 送信状況メッセージ更新
Worksheets("Sheet1").Range("F2").Value = Worksheets("Sheet1").Range("B" & i).Value & " まで送信成功！"
' １秒停止
Application.Wait [ NOW() + "0:00:01" ]
Next i
End Sub
というものです。
登録するメールアドレスは、上から順に記入しないと 空白があった時点で、その下のセルは送信しない＆空白でなく、式が入っている時点で その式の記述してある文字列に送信しようとしてエラーが出る。
というものなので、こんな事になっております。
コピーさせるセルには、
=IF(ROW(Z1)>SUMPRODUCT((LEN($W$37:$W$45)>0)*1),"",INDEX($W$1:$W$45,SMALL(INDEX(($W$37:$W$45="")*100+ROW($W$37:$W$45),),ROW(Z1))))
こんな式を記入し、上から順に途中には空白無く記入できるようにしております。
自力でなんとか出来ないかと試行錯誤したつもりなのですが、なにぶん堪え性が無く、思わず書込みしてしまった次第です・・・本当にお恥ずかしく申し訳御座いません・・<(_ _)>
年寄りは、そろそろオネムなので、これから先は明日。 どうも、コードというより、処理の構成をもう一度、レビューされたほうがいいような気もする。 どうしても１つの選択で即時、アドレス転記ということなら、初心に帰って（？）全てのチェックボックスに私がアップしたMailAdCopyを登録したらいかが。
私が指摘しているのは、オリジナルのコードはチェックボックスにレがはいっていてもはいっていなくても、 常に９つのアドレスがSeet1に転記されるけど、それでいいの？具合悪いでしょ？ ということ。 もし、選択された、されないにかかわらず９つのアドレスでいいなら、「式」ではなく、９つのアドレスをSheet1に書いておけばマクロはいらないよね。 でも、それでは意味がないんでしょ？ということ。
ぶらっと立ち寄り
＞コピーさせるセルには・・・こんな式を記入し、上から順に途中には空白無く記入できるようにしております。
これは、緊急メールシートのZ37:Z45の事ですよね？
でしたら
↓このコードを
Sub MailAdCopy_H1()
With Sheets("Sheet1")
.Unprotect
.Range("B10:B18").Value = Worksheets("緊急メール").Range("Z37:Z45").Value
.Protect
End With
End Sub
Excel_Mail_Sendマクロの先頭で、CALL（実行される様に）すれば良さそうに思います。
(HANA)
＞９つのアドレスをSheet1に書いておけばマクロはいらないよね
↑コレが出来ないのです。Sheet1のそのセルに =緊急メール!Z37 等の式を入れてしまうと、緊急メール!Z37という文字列ににメールを送ってしまいエラーでとまってしまうのです。
で、９つのアドレスのうち一つでも選択に変動があった場合は、常にSeet1に転記されるという事をしたかったのです（ですのでもっと登録したかったアドレスを９つまで減らしたのですが）、が、ぶらっと立ち寄りさんの仰るとおり、もっとスマートな方法があるのではないか・・・と思っており、元から考え直そうと思いました。
なんとかもっとスマートな方法を考えてチャレンジしてみます！お忙しい所、こんな変な書込みにお付き合い頂きまして本当にありがとう御座いました<(_"_)> もう少しVBAの基礎から勉強し、質問するにしても、もうすこし的を得た質問が出来るよう努力して出直してまいります。本当にご迷惑お掛けして申し訳御座いませんでした＆ﾎﾝﾄありがとう御座いました！！感謝！
寝る前に一言だけ。 >↑コレが出来ないのです。 私が言ってるのは、「アップされた最初のコードが無条件に９つのアドレスをSheet1に書いていますよ」ということ。 で、それじゃ具合悪いでしょということなんだけどなぁ・・・ Sheet1に式を書いておくなんてことは、一言も言ってないけど。 最初からSheet1に９つのアドレスを書いておいたらどうですかとは言ってるけど。 でも、これは、「それじゃ意味ないでしょ」ということなんだけどねぇ・・・
ぶらっと立ち寄り
あら？ 私の書き込み、なんだかスルーされてます？
(HANA)
＞アップされた最初のコードが無条件に９つのアドレスをSheet1に書いていますよ
はい、そのとおりです。とは言っても、9つとも文字列が必ず入っている訳ではなく、幾つかは空白の場合があるのです。（私としては、無条件に空白も含め、９つのアドレスをSheet1に書きだす必要がある と思っていたのです）
で、Sheet1のB10:B18の中に書き込むアドレスは、上から順に有効なメアドで埋まっていないと、メール送信時、その空白、または有効でないアドレス（関数等を含む）を読み込んでしまい、その時点で停止してしまうのです。
ですので例えばB11に空白のセルが入ってしまうと、B10のメアドには送信しますがB11で止まり、それ以降のB12以降に記入されているメアドには送信できなくなるのです。
ですので、MailAdCopyを作り、上から順に空白無くB10:B18の中にアドレスを記入出来るようにしたいのです。
その為、緊急メールのZ37:Z45は、上から順に有効なメールアドレスが並ぶように関数を入れ、それをSheet1のB10:B18にマクロでコピーする といったまどろっこしい事をさせたのですが、仰るとおり、甚だスマートではありません(^^;
なんとか勉強して、もう少しスマートな流れを考えてみます！こんな初心者の戯言にお付き合いさせ、更に私の解りにくく変な説明で惑わせてしまい、真に申し訳御座いませんでした。
こんな私にお付き合いいただき 本当にありがとう御座いました<(_"_)>
なにぶん素人で、失礼があったと思いますが、何卒ご容赦いただければ幸いです(^^;
ﾎﾝﾄありがとう御座いました！感謝！！
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.