エクセルVBA ユーザーフォームからシートの操作

質問

エクセルでユーザーフォームを用いて
顧客管理データーベース入力フォームを作成しています。
ユーザーフォーム上で入力した値を複数のシートに
またがってデーターを入力していくつもりです。
それで困っていることなのですが、
ユーザーフォームを起動したときに、それぞれの
シートの空白の場所を変数で記憶させているのですが
以下に示す部分でエラーが発生します。
エラーの原因は、アクティブシートでないので
セルを取得できないようなのですが、アクティブでない
場合の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」を入れる
と動くようなのですが、呼び出さずに動かす方法ってありますか？
宜しくお願いいたします。

回答No.4

name_mm_ok · ベストアンサー

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を含むワークブックの場合、
'ループは２回発生します。
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

お礼

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

回答No.3

Wendy02

こんにちは。

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

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

お礼

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

回答No.2

name_mm_ok

今のソースコードでは、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」
どう言う動きになっているのか理解しきれて
いないもので、補足していただけないでしょうか。
宜しくお願いいたします。

回答No.1

ASIMOV

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

補足

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