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

Question

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

name_mm_ok · Accepted Answer

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

ASIMOV · Answer

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

name_mm_ok · Answer

今のソースコードでは、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

Wendy02 · Answer

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

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

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

この回答への補足

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

この回答への補足

こんにちは。

tmgolf さま

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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