エクセルでユーザーフォームを用いて
顧客管理データーベース入力フォームを作成しています。
ユーザーフォーム上で入力した値を複数のシートに
またがってデーターを入力していくつもりです。
それで困っていることなのですが、
ユーザーフォームを起動したときに、それぞれの
シートの空白の場所を変数で記憶させているのですが
以下に示す部分でエラーが発生します。
エラーの原因は、アクティブシートでないので
セルを取得できないようなのですが、アクティブでない
場合のselectに変わるやり方ってありませんか?

-----sheet1がアクティブな状態で--------------------------------
Dim myRowNo As Integer
Dim myRowNoS As Integer
---------------------------------------------------------------
Private Sub MyRowNo1()
 myRowNo = 4
 Sheets("sheet1").Cells(myRowNo, 1).Select
 Do Until ActiveCell.Value = ""
  myRowNo = myRowNo + 1
  Sheets("sheet1").Cells(myRowNo, 1).Select
 Loop
End Sub
----------------------------------------------------------------
Private Sub MyRowNo2()
 myRowNoS = 4
 Sheets("sheet2").Cells(myRowNoS, 1).Select <<<<<<<<エラー箇所
 Do Until ActiveCell.Value = ""
  myRowNoS = myRowNoS + 1
  Sheets("sheet2").Cells(myRowNoS, 1).Select
 Loop
End Sub
---------------------------------------------------------------
Private Sub OpenButton2_Click() 'ユーザーフォームボタンをクリック
 Call MyRowNo1
 Call MyRowNo2
End Sub
---------------------------------------------------------------
MyRowNo1と2の間に「Worksheets("sheet2").Select」を入れる
と動くようなのですが、呼び出さずに動かす方法ってありますか?
宜しくお願いいたします。

A 回答 (4件)

tmgolf さま


Call MyRowNoXXX("XXX") 内のXXXはワークシート名称です。
アクティブになっているワークブック内にXXXが含まれない
場合、メッセージが表示されるようにしています。
大文字、小文字の区別があるので、おそらく新規ワークブック
でテストされ、シート名称がSheet1、Sheet2のSが大文字の
シート名称でテストされていないでしょうか?
再度確認してみて下さい。
下記に先程の回答にコメントを追加しました、わかりやすく
記述したつもりですが、一部どうしても代替できない部分が
ありました。
意味が理解できない単語がありましたら、ネットの辞書で検索
されると私が説明するよりも正しく詳しく記載されています。(笑

Private myRowNoS As Long

Private Sub testcall()
Call MyRowNoXXX("sheet1")
Call MyRowNoXXX("sheet2")
End Sub

Sub MyRowNoXXX(ByVal wshName As String)
'現在アクティブになっているワークブック内の
'ワークシートクラスを一時的に格納するための
'宣言
'クラスについての詳しい説明は避けますが、
'簡単にいうと、データとメソッド(関数、プロシージャ)を
'ひとまとめにし、再利用するための雛形です。
'詳しい内容については、少しずつネット又は書籍で
'知識を深めて下さい。
Dim WSh As Worksheet

'ここは現在作成中の処理の仕様に基づき適切な
'処理を記述して下さい。
myRowNoS = 4

'現在アクティブになっているワークブック内の
'全てのワークシート数分ループを繰り返します。
'同時に、WSh変数に対しワークシートクラスを
'順に割り当てます。
'仮に、Sheet1、Sheet2を含むワークブックの場合、
'ループは2回発生します。
For Each WSh In ActiveWorkbook.Worksheets
'ワークシート名称を判定し、引数で指定された
'ワークシート名称と完全一致するかを判定します。
If WSh.Name = wshName Then
'ワークシート名称と完全一致した場合ループ
'処理を抜けます。
Exit For
End If
Next WSh

'以下の判定は、ループ内で行われた、ワークシート
'名称の判定処理において、一度も一致しなかった場合、
'メッセージを出力するようにしています。
'上記のループで一致する場合は、途中でループ処理を
'抜けるため、wshName に格納されたシート名称を持つ
'ワークシートクラスのインスタンスが格納されている
'ため、WSh は解放された状態(Nothing)になりません。
If WSh Is Nothing Then
MsgBox "ワークシート[" & wshName & "]が見つかりません。", _
vbCritical + vbOKOnly
Exit Sub
End If

Do Until WSh.Cells(myRowNoS, 1) = ""
myRowNoS = myRowNoS + 1
Loop

'一時的に使用したオブジェクト変数の解放
Set WSh = Nothing

MsgBox "未入力は" & Format(myRowNoS) & "行目です。", _
vbInformation + vbOKOnly

End Sub

参考URL:http://e-words.jp/p/c-programming-object.html
    • good
    • 0
この回答へのお礼

補足と解説を丁寧にしていただいてありがとう
ございます。ご指摘の通り、sheetのsが小文字
だったためでした(汗)
解説の方もとても丁寧にしていただき、誠に
ありがとうございます。また、URLもつけて
いただきありがとうございます。
とても勉強になりました。

お礼日時:2007/06/27 00:40

こんにちは。



Select するからエラーが出るわけです。その前に、Sheet をアクティブにしないといけません。それと、わざわざ、ご丁寧にPrivate ステートメントを入れているようですが、ローカルモジュールに入れる必要はありませんね。

Dim myRowNo As Long
Dim myRowNoS As Long

Sub GetRowSheetCol_A()
Dim i As Long
 i = 4
 myRowNo = Worksheets("Sheet1").Cells(i, 1).End(xlDown).Row + 1
' myRowNo = Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row + 1
 
 myRowNoS = Worksheets("Sheet2").Cells(i, 1).End(xlDown).Row + 1
' myRowNoS = Worksheets("Sheet2").Cells(65536, 1).End(xlUp).Row + 1

 MsgBox myRowNo & ", " & myRowNoS
End Sub

ローカル変数とモジュールスコープの変数の名前は、別にしたほうがよいです。コードは、問題のないほうを使ってください。上から下がる方法と下から上がる方法のどちらかです。ローカルモジュールでも可能です。
    • good
    • 0
この回答へのお礼

ご返答ありがとうございます。
コードの方、ありがとうございます。
こちらのコードも参考にこれからも
勉強させていただきます。
ありがとうございます。

お礼日時:2007/06/27 00:42

今のソースコードでは、sheet2 をアクティブにしないとエラーとなってしまいます。


アクティブにする必要がないのであれば、以下のコードでどうでしょうか。
Private myRowNoS As Long

Private Sub testcall()
Call MyRowNoXXX("sheet1")
Call MyRowNoXXX("sheet2")
End Sub

Private Sub MyRowNoXXX(ByVal wshName As String)
Dim WSh As Worksheet
myRowNoS = 4
'仕様によっては、ワークブックの検索も必要です。
For Each WSh In ActiveWorkbook.Worksheets
If WSh.Name = wshName Then
Exit For
End If
Next WSh
If WSh Is Nothing Then
MsgBox "ワークシート[" & wshName & "]が見つかりません。", _
vbCritical + vbOKOnly
Exit Sub
End If
Do Until WSh.Cells(myRowNoS, 1) = ""
myRowNoS = myRowNoS + 1
Loop
Set WSh = Nothing
MsgBox "未入力は" & Format(myRowNoS) & "行目です。", _
vbInformation + vbOKOnly
End Sub

この回答への補足

ご返答ありがとうございます。
コードを試しているのですが。
[sheet1]が見つかりません。
[sheet2]が見つかりません。
と出てきます。
<<If WSh.Name = wshName Then
のところで、変数を確認したのですが
WSh.Name="sheet1"
wshName="sheet1"
となっていました。
<<If WSh Is Nothing Then
のところで
WShは変数が無いようなのですが、
「If WSh Is Nothing Then」
どう言う動きになっているのか理解しきれて
いないもので、補足していただけないでしょうか。
宜しくお願いいたします。

補足日時:2007/06/26 15:28
    • good
    • 0

標準モジュールに記述すれば、エラーにならないと思いますが...

この回答への補足

お返事ありがとうございます。
標準モジュールに記述と言うことなのですが、
ユーザーフォームを操作している時に
実行していきたいのですが、標準モジュールへの記述でも
良いのでしょうか?
まだ、VBAを勉強しだして半年ほどなので、記述場所や変数
の宣言場所、有効期間等など理解しきれていないので。
ユーザーフォーム実行中にVBAを動かしていくには、ユーザー
フォームにコードを記述していくものだと思っているのですが。
標準モジュールで大丈夫なのでしょうか?

補足日時:2007/06/26 15:22
    • good
    • 0

このQ&Aと関連する良く見られている質問

QPrivate Sub Worksheet_Calculate()のことでお聞きします

いつもgooの皆さんに大変お世話になっております
マクロの勉強を初めて2ヶ月の者です

Private Sub Worksheet_Change(ByVal Target As Range)にマクロを書いているのですが
「特定のセル範囲が変更されたときに処理を実行する」は分かるのですが
特定のセル範囲が変更されなくても、例えば、セルの値がインストールされているアドインで
変化した時の処理はどこに書いたらいいのでしょうか?

今現在、H列のTarget.RowとJ列のTarget.Rowにはアドインで、機器の温度が表示されていますが
この2点の温度変化で
Application.Speech.Speak "温度接近", True
などどやりたいのですが、Private Sub Worksheet_Calculate()に書けばいいのでしょうか
その場合、Private Sub Worksheet_Change(ByVal Target As Range)内の
Target.Row等の値はどうやったらPrivate Sub Worksheet_Calculate()で使えますか?
それとも、もしかしたら標準モジュールに書くのでしょうか?

うまく説明できていませんが、どなたか教えてください

いつもgooの皆さんに大変お世話になっております
マクロの勉強を初めて2ヶ月の者です

Private Sub Worksheet_Change(ByVal Target As Range)にマクロを書いているのですが
「特定のセル範囲が変更されたときに処理を実行する」は分かるのですが
特定のセル範囲が変更されなくても、例えば、セルの値がインストールされているアドインで
変化した時の処理はどこに書いたらいいのでしょうか?

今現在、H列のTarget.RowとJ列のTarget.Rowにはアドインで、機器の温度が表示されていますが
この2点の...続きを読む

Aベストアンサー

こんばんは。

今回の質問は、ご質問者さんには、別のイベントというか、紹介しましたね。その中に含まれていたかもしれません。

今回の件は、マクロを勉強して2ヶ月の人の話ではなく、3年やっても、5年やっても知らない人は知らないことで、かなりハイレベルというか、古めかしいというか、ほとんど知られていない技術だと思います。こういう話は、1冊の本にもなっています。本来、計測メーカー側の専門業者に頼んでしまったほうが早い気もします。たぶん、彼らは、公表はしていませんが、それ専用のVBA用のマクロを持っているはずです。

>特定のセル範囲が変更されなくても、例えば、セルの値がインストールされているアドインで
>変化した時の処理はどこに書いたらいいのでしょうか?

ふつうは、どこでもよいので、
=Now()
という式を書いて、
Private Sub Worksheet_Calculate()
に、イベントを拾わせるわけです。
しかし、少し、工夫が必要で、時々、これが困った動きをします。

Calculate のイベントが、ワークブック全体に働いてしまい、どこのシートを使っても、Calculateイベントが動いてしまいます。この方針で話が本格的になった時に、この対処法は、あらためて紹介します。

>(Worksheet_Change)のTarget.Row等の値はどうやったらPrivate Sub Worksheet_Calculate()で使えますか?

単に、モジュール変数を使って橋渡しするのか、それとも、同時で働くということなら、 Worksheet_Calculateのイベントでなくてもよいはずです。どのように使うのか、こちらではわかりかねます。本来、別の働きをするものです。もちろん、キーで入力した際は、Worksheet_Calculateのイベントは、起動します。

>Application.Speech.Speak "温度接近", True
>などどやりたいのですが、Private Sub Worksheet_Calculate()に書けばいいのでしょうか

ActiveX コントロールで入ってきた値が、どのように加わっていくのかにもよりますが、セルの位置は、Worksheet_Changeイベントのようにセルの位置は感知しませんから、インプットの場所が動いていくのでしたら、マクロで最後尾を探さなくてはなりません。

>それとも、もしかしたら標準モジュールに書くのでしょうか?
同様のコマンドで、標準モジュールに書く方法もあります。とても古い方法で、オブジェクト・ウィンドウにも出てこないはずです。しかし、まだ、同様の働きはしますが、今は、シートモジュールが主流になっています。

こんばんは。

今回の質問は、ご質問者さんには、別のイベントというか、紹介しましたね。その中に含まれていたかもしれません。

今回の件は、マクロを勉強して2ヶ月の人の話ではなく、3年やっても、5年やっても知らない人は知らないことで、かなりハイレベルというか、古めかしいというか、ほとんど知られていない技術だと思います。こういう話は、1冊の本にもなっています。本来、計測メーカー側の専門業者に頼んでしまったほうが早い気もします。たぶん、彼らは、公表はしていませんが、それ専用のVBA用の...続きを読む

QPrivate Sub Workbook_open()で、マクロが止まってしまいます

よろしくお願いします。昨年はgooの皆さんに大変お世話になりました
お陰様でマクロもエクセルも大いに進歩できました
今年もよろしくお願いします

質問です
Private Sub Workbook_open()で、マクロが止まってしまいます
原因は
'***********************

'***********************
で、はさまれた部分にあるようです(REM文にするとマクロは止まりません)
なぜマクロが止まってしまうのでしょうか?
どなたか教えてください

Private Sub Workbook_open()
'実行順序はWorkbook_open()がauto_openより先
With Sheets("T60012")
'エクセルopen時に前回close時のデータは全部クリアー(終わったことだから比較は意味がない)
.Range("AP42:AP62").ClearContents '管理上限オーバー 旧温度クリアー
.Range("BA42:BA62").ClearContents '管理下限アンダー 旧温度クリアー
.Range("CE42:CE62").ClearContents 'OK NOの文字 デフォルトは空白
'****************************
Application.EnableEvents = True
Application.Speech.Speak "ボリュームチェック、ボリュームチェック", True
Application.EnableEvents = False
'****************************

End With
End Sub

よろしくお願いします。昨年はgooの皆さんに大変お世話になりました
お陰様でマクロもエクセルも大いに進歩できました
今年もよろしくお願いします

質問です
Private Sub Workbook_open()で、マクロが止まってしまいます
原因は
'***********************

'***********************
で、はさまれた部分にあるようです(REM文にするとマクロは止まりません)
なぜマクロが止まってしまうのでしょうか?
どなたか教えてください

Private Sub Workbook_open()
'実行順序はWorkbook_open(...続きを読む

Aベストアンサー

こんにちは!

>Application.EnableEvents
のTRUEとFALSEが逆なのでは?

お示しのコードでは「イベントを止める」で終わっていますので
そこでマクロそのものが止まってしまうと思います。

あくまで個人的見解ですが
今回のコードでは
>Application.EnableEvents = False

>Application.EnableEvents = True
の2行は不要な気がします。
(検証はしていません)

一般的にはChangeイベントなどで無限ループに陥るのを防ぐ場合、
この使い方をすることが多いと思います。

※ 万一エラーでマクロが止まったのであれば
Application.EnableEvents = True
の1行だけの別マクロで戻してあげないと
お示しのプロシージャそのものがうんともすんとも言わなくなるのでは?
(これも未検証なので無意味だったらごめんなさい)m(_ _)m

QExcel2013 VBA sheet1とsheet2のデータを合成してsheet3を作るには

sheet1に氏名、sheet2にその氏名の人の趣味が入っています。

新たにsheet3を作成して、
氏名1
趣味
氏名1

氏名2
趣味
氏名2

氏名3
趣味
氏名3

氏名4
趣味
氏名4

としたいです。
VBAのコードを教えて下さい。

例えば
①sheet1には
A1;1 B1;阿部 C1;あべ
A2;2 B2;佐藤 C2;さとう
A3;3 B3;山名 C3;やまな
A4;4 B4;山本 C4;やまもと

②sheet2にはその人の趣味が入っています。

A1;1  B1;釣り C1;つり
A2;空白 B2;踊り C2;おどり
A3;空白 B3;歌 C3;うた

A4;2  B4;読書 C4;どくしょ
A5;空白 B5;野球 C5;やきゅう


A6;3 B6;映画鑑賞 C6;えいがかんしょう

A7;4  B7;釣り C7;つり
A8;空白 B8;踊り C8;おどり
A9;空白 B9;歌 C9;うた

③sheet3を新に作成して

A1;1 B1;阿部 C1;あべ
A2;空白  B2;釣り C2;つり
A3;空白 B3;踊り C3;おどり
A4;空白 B4;歌 C4;うた
A5;空白 B5;阿部 C5;あべ

A6;2 B6;佐藤 C6;さとう
A7;空白 B7;読書 C7;どくしょ
A8;空白 B8;野球 C8;やきゅう
A9;空白 B9;佐藤 C9;さとう

A10;3 B10;山名 C10;やまな
A11;空白 B11;映画鑑賞 C11;えいがかんしょう
A12;空白 B12;山名 C12;やまな

A13;4 B13;山本 C13;やまもと
A14;空白  B14;釣り C14;つり
A15;空白 B15;踊り C15;おどり
A16;空白 B16;歌 C16;うた
A17;空白 B17;山本 C17;やまもと



のようにしたいです。

実際、データは、sheet1は419列、sheet2は2563列あります。

sheet1に氏名、sheet2にその氏名の人の趣味が入っています。

新たにsheet3を作成して、
氏名1
趣味
氏名1

氏名2
趣味
氏名2

氏名3
趣味
氏名3

氏名4
趣味
氏名4

としたいです。
VBAのコードを教えて下さい。

例えば
①sheet1には
A1;1 B1;阿部 C1;あべ
A2;2 B2;佐藤 C2;さとう
A3;3 B3;山名 C3;やまな
A4;4 B4;山本 C4;やまもと

②sheet2にはその人の趣味が入っています。

A1;1  B1;釣り C1;つり
A2;空白 B2;踊り C2;おどり
A3;空白 B3;歌 ...続きを読む

Aベストアンサー

こんばんは!

Sheet3にSheet1のデータを二度表示させるのがイマイチ理解できませんが、
ご質問通りにやってみました。

Sub Sample1()
Dim i As Long, lastRow As Long, myCnt As Long
Dim c As Range, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
wS2.Rows(1).Insert
wS2.Range("D:D").Insert
With Worksheets("Sheet3")
.Cells.ClearContents
lastRow = wS2.Cells(Rows.Count, "B").End(xlUp).Row
Range(wS2.Cells(2, "D"), wS2.Cells(lastRow, "D")).Formula = "=IF(A2="""",D1,A2)"
For i = 1 To wS1.Cells(Rows.Count, "A").End(xlUp).Row
With .Cells(Rows.Count, "B").End(xlUp).Offset(1)
.Value = wS1.Cells(i, "B")
.Offset(, -1) = wS1.Cells(i, "A")
.Offset(, 1) = wS1.Cells(i, "C")
End With
Set c = wS2.Range("A:A").Find(what:=wS1.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
myCnt = WorksheetFunction.CountIf(wS2.Range("D:D"), wS1.Cells(i, "A"))
.Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(myCnt, 2).Value = _
c.Offset(, 1).Resize(myCnt, 2).Value
End If
'▼
.Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(, 2).Value = _
wS1.Cells(i, "B").Resize(, 2).Value
'▲
Next i
.Rows(1).Delete
wS2.Rows(1).Delete
wS2.Range("D:D").Delete
Application.ScreenUpdating = True
.Activate
End With
MsgBox "完了"
End Sub

※ コード内の▼から▲までがもう一度Sheet1のデータを表示させているコードです。
細かい検証はしていませんが、
こんな感じではどうでしょうか?m(_ _)m

こんばんは!

Sheet3にSheet1のデータを二度表示させるのがイマイチ理解できませんが、
ご質問通りにやってみました。

Sub Sample1()
Dim i As Long, lastRow As Long, myCnt As Long
Dim c As Range, wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")
Application.ScreenUpdating = False
wS2.Rows(1).Insert
wS2.Range("D:D").Insert
With Worksheets("Sheet3")
.Cells.ClearContents
lastRow = ...続きを読む

QExcel 2010 Sheet1の内容をSheet2に条件で抽出する方法

Sheet1で「担当者が山田で完成日がブランクの作業名」をSheet2のA2から下に自動で入るようにしたい。他の担当者も同様の処理を行いたい。
※もしSheet1の完成日に日付が入力されたらSheet2からは自動で削除されるようにしたい。

これは関数でどのような処理を行えば可能になるでしょうか。
自分でもいろいろと試してみたのですが、力不足でできませんでした。
詳しい方いらっしゃいましたら教えて頂ければと思います。
よろしくお願いいたします。

Aベストアンサー

こんばんは!

画像の配置通りだとSheet2のA2セルに
=IFERROR(INDEX(Sheet1!$A$1:$A$1000,SMALL(IF((Sheet1!$B$1:$B$1000=A$1)*(Sheet1!$C$1:$C$1000=""),ROW($A$1:$A$1000)),ROW(A1))),"")

配列数式になりますのでCtrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → Sheet2のA2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!

最後にA2セルのフィルハンドルで列・行方向にコピーしてみてください。m(_ _)m

Qエクセルでsheet2からsheet1へデータを抽出し必要な部分だけを抽出する方法を教えてください。

データベースからエクセルファイルのデータにしたものから
必要データだけを表示できるように編集したいのですが、日々20~30件増えます。
今までは列ごと削除して作成していましたが、A列のみをコピーしてそれを参照して
必要列のみを表示させるにはどうすればいいでしょうか。

すべてのデータをsheet2に貼り付け、そこから必要項目をsheet1に表示させたいです。
A列を参照して表示させるようにデータ部分の数式を↓に方向にドラッグコピーして使用するには、
おぼろげにVLOOKUPを使うのでは?と思うのですが方法がわかりません。

画像を見ていただきアドバイスをいただけないでしょうか。
よろしくお願いします。

Aベストアンサー

こんにちは!

両SheetともA列の並びは一緒なのですね?

Sheet1のB2セルに
=IF(OR($A2="",B$1=""),"",INDEX(Sheet2!$A:$Z,ROW(),MATCH(B$1,Sheet2!$1:$1,0)))
という数式を入れフィルハンドルで列・行方向にコピーしてみてください。
(Sheet2のセル全体を範囲指定しても良かったのですが、無意味のようなのでA~Z列を範囲としてみました)

※ 両SheetのA列並びが異なる場合は
=IF(OR($A2="",B$1=""),"",INDEX(Sheet2!$A:$Z,MATCH($A2,Sheet2!$A:$A,0),MATCH(B$1,Sheet2!$1:$1,0)))
という数式にしてください。m(_ _)m

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報