Excell(2003) VBA UserForm上のSheetの参照方法は

質問

Excell2003にてuserForm1にコントロールとしてスプレッドシートを載せています。
このuserForm1には、別にテキストボックス(textbox1)とコマンドボタンが二つ(commandButton1とCommandButton2)があります。
commandButton1がクリックされたとき、textbox1に入力された値をチェックしたのち、Me(userForm)上のSheet1の適当なセル(たとえばA2)にその値を入力したい。
　また、CommandButton2がクリックされたときは、Sheet2のA2に入力したい。その場合Sheet2を表示する動作も必要です。
　この場合
Q:本体のSheetではなく、フォーム上のSheetを指定する方法
Q:複数のSheetからなるフォーム上のspledSheetのいずれかを表示する方法
　はどのようにしたら良いのでしょう。

回答No.6

end-u

できたんならそれでいいんじゃないですか？
スマートな方法＜自分が理解できる方法　じゃないですかね。
>RecordsetオブジェクトのGetRowsメソッドを使う方法もありますけどね。
がダメな理由が理解らんけど　-"-

#まさか変数myRsをwkRstって置きかえたせいで理解できなく試してないって事はないよね。
いずれにしても私はこのヘンで失礼します。

お礼

>RecordsetオブジェクトのGetRowsメソッドを使う方法もありますけどね。
がダメな理由が理解らんけど　-"-

　単に、データベースのすべてのフィールドが必要ではなく、ごく一部のフィールドしか要らないので、WorkBookのSheetからコピーする手順のほうがマクロの記録で対応可能なので楽というだけの理由です。手抜きといわれればそのとおりですが・・m(__)m

回答No.5

end-u

>内容的には問題はないのですが、ちょっと見栄えがよろしくない。
CopyFromRecordsetメソッドを使う場合は、仕様なので妥協するしかないんじゃないでしょうか。

RecordsetオブジェクトのGetRowsメソッドを使う方法もありますけどね。
Dim v
'：
v = Application.Transpose(wkRst.GetRows)
With Me.Spreadsheet1.Sheets(1)
　　.Range(.Cells(1, 1), .Cells(UBound(v, 1), UBound(v, 2))).Value = v
End With

お礼

散々悩んだ挙句・・・
　いったん本体のWorkSheetに貼り付けて、再度そのデータのうち必要な部分をユーザーフォーム上のSheetに貼り付けることにしました。

Range("A1:D" & Rows.Count &").Select
Selection.Copy
UserForm1.Spreadsheet1.Sheets("sheet1").Range("A2").Paste

　もっとスマートな方法がありそうですが？？・・あれば教えてください。
　とりあえず、他の目的にもちょうど良いのでとりあえずこれで行ってみよう。

回答No.4

end-u

当初の質問内容から随分隔たってますねえ。
『漠然とした質問だった』のではありません。補足に書かれている事は明らかに別件の質問です。
貴方が最初に提示した条件だけで、私の先のアドバイスを試してみたとして、それでも『どうもうまくいきません』か？
質問する時の情報提示の仕方や、補足のつけ方自体を見直してみたほうが良いですよ。

>UserForm1.Spreadsheet1.Sheets("sheet1").Range("A1").CopyFromRecordset newData
>'だと、「A1セルの見残して他はシート自体消える。」
UserForm1.Spreadsheet1.Sheets("sheet1").Cells.CopyFromRecordset newData
としなければなりません。

『CopyFromRecordset』にカーソルをあてて[F1]キーを押下すると
『SpreadsheetオブジェクトのRangeオブジェクトのCopyFromRecordsetメソッド』のヘルプにアクセスできます。
RangeコレクションのCopyFromRecordsetメソッドとは別のヘルプです。
---以下ヘルプ引用---
解説
コピーは Recordset オブジェクトの現在の行を開始点として行われます。コピーが完了すると、Recordset オブジェクトの EOF プロパティは True になります。
このメソッドでレコードセットをワークシートにコピーするとき、レコードセットの内容を保持できる十分な大きさの範囲を指定しないと、結果の一部が切り捨てられます。
---引用ここまで---

また、
>' Worksheets("Sheet1").Range("A1").CopyFromRecordset newData
>'だと「実行時エラー '9':インデックスが有効範囲にありません。」
これは、Spreadsheetではなくて、ActiveWorkbookのシートにコピーしたいという事ですか？
その場合、ActiveWorkbookに"Sheet1"という名前のシートがないためのエラーでしょう。確認して下さい。

補足の最後に
>' UserForm1.Spreadsheet1.Sheets("sheet1").Range("A1").CopyFromRecordset myRs
>' Worksheets("Sheet1").Range("A1").CopyFromRecordset myRs
この２行が書いてありますが、UserForm1.Spreadsheet1.Sheets("sheet1") と Worksheets("Sheet1")の両方にコピーしたいという意味ですか？
もしそうなら、間に
myRs.MoveFirst
が必要です。

補足

ありがとうございます。
　やっとユーザーフォーム上のSheetの参照方法がわかりかけてきたような・・気分にはなってますが、まだまだわかってないでしょう。

　実は、もともとやりたかったことは最後の補足の内容でしたが、最初の質問時点では、まだそこまで出来上がってなくて、
・・すなわち、単にユーザーフォームのテキストボックスなどでなられた値を代入するところまでしか・・・
　それで、あたかも別の内容のようになってしまいました。

　本体のWorkSheetのほうは、将来的にはまったく表示させる必要がないので、ユーザーフォームの処理ができたら、最小化でもして隠しておこうかと思っています。終了時に他のBookが開いてなければExcel自体を終了、残っていれば他のBookを開いて終了がスマートかなと・・

　で、wooksheetのほうにレコードをコピーした場合は、sheetの他の部分はそのまま残りますが、
>UserForm1.Spreadsheet1.Sheets("sheet1").Cells.CopyFromRecordset newData
　の場合は、データのない部分は灰色になってSheet自体が小さくなります。内容的には問題はないのですが、ちょっと見栄えがよろしくない。最悪５行程度は生かしておきたい・・って難しいでしょうか？
　というのは、次の空白行のどこかがクリックされたとき、データ追加のコントロールを表示させたいのです。
　値のある行はクリックされると修正フォームを開きたいし。

　VBAは、今まで経験してきた言語と違うので本当に良くわかりません。今回は時間がないけど、終わったら初歩から学びなおさなくては・・

回答No.3

Trick--o--

＃２の回答にあるように、Rangeが「どのシートの」かを指定しませう。
Me.Spreadsheet1.Sheets("sheet2").Range("B2")
Worksheets("Sheet3").Range("B2")
といった具合。

補足

たびたびお付き合いありがとうございます。
どうもうまく行きません。
先に書きましたように
___________________オリジナル(動作確認済み)
'いったんフォーカスをシートに返して
Range("A1").Select
'書かれているデータをクリアして
Range("B2:M" & Rows.Count).ClearContents '★
'複数行からなるデータ(newData)を貼り付ける。
Range("B2").CopyFromRecordset newData
￣￣￣￣￣￣￣￣￣￣￣￣ここまで
　のうち★までは

UserForm1.Spreadsheet1.Sheets("sheet1").Range("A1").Select
'いったんフォーカスをシートに返して
'書かれているデータをクリアして
UserForm1.Spreadsheet1.Sheets("sheet1")Range("B2:M" & _
Rows.Count).ClearContents '★
'ここまではOK、ところが
UserForm1.Spreadsheet1.Sheets("sheet1").Range("A1").CopyFromRecordset newData
'だと、「A1セルの見残して他はシート自体消える。」
' Worksheets("Sheet1").Range("A1").CopyFromRecordset newData
'だと「実行時エラー '9':インデックスが有効範囲にありません。」
となり、
→ Worksheets("Sheet1").Range("A1").CopyFromRecordset newData
を示して停止します。
なお、newDataはMDBからADOで読み取ったテーブルの全データです。
　どこが違うのだろう。

' UserForm1.Spreadsheet1.Sheets("sheet1").Range("A1").CopyFromRecordset myRs
' Worksheets("Sheet1").Range("A1").CopyFromRecordset myRs

回答No.2

end-u

>Q:複数のSheetからなるフォーム上のspledSheetのいずれかを表示する方法
『SpreadsheetオブジェクトのSheetsプロパティ』で指定してあげれば良いです。
Private Sub CommandButton1_Click()
　　Dim s As String

　　s = Me.TextBox1.Text
　　If Len(s) > 0 Then
　　　　With Me.Spreadsheet1.Sheets(1)
　　　　　　.Range("A2").Value = s
　　　　　　.Activate
　　　　End With
　　End If
End Sub

Me.Spreadsheet1.Sheets(2)
や
Me.Spreadsheet1.Sheets("sheet2")
など。

補足

お二人ともありがとうございます。
どうもうまくいきません。あまりにも漠然とした質問だったので、もう少し具体的に書いてみます。

今のスクリプトは

'いったんフォーカスをシートに返して
Range("A1").Select
'書かれているデータをクリアして
Range("B2:M" & Rows.Count).ClearContents
'複数行からなるデータ(newData)を貼り付ける。
Range("B2").CopyFromRecordset newData
としています。
この場合だと、単純に今開いている本体のsheetに記入されます。

回答No.1

Trick--o--

spledSheetを置いたときに名前がつけられるはずです
（デフォルトではSpreadsheet1、Spreadsheet2……）
プロパティウインドウを開いてオブジェクト名を確認してください

Private Sub CommandButton1_Click()
' Spreadsheet1のA1セルにTextBox1の内容を表示
a = Me.TextBox1.Text

Me.Spreadsheet1.Cells(1, 1).Value = a
End Sub

Private Sub CommandButton2_Click()
' Sheet2のA1セルにTextBox1の内容を表示
a = Me.TextBox1.Text

Worksheets("Sheet2").Cells(1, 1).Value = a
Worksheets("Sheet2").Activate

' ダイアログを閉じる必要があるなら
' Unload Me
End Sub