他のシートのセル範囲を変数で指定して罫線を引くには?

Question 31.2 Previous Next
いつも閲覧させていただいてます。このようなHPがあることに、大変感謝しております。早速ですが、1つ教えて頂きたいことがあります。
変数名で範囲指定して選択するには R1C1」を参考に、コマンドボタンクリック時に、選択されたセルに罫線を引きたいのですが、コマンドボタンのあるsheetと罫線を引くsheetが違うためか、「rangeクラスのselectメソッドが正しくありません」のエラーが出てしまいます。
コマンドボタンのあるsheet上のセルを範囲指定する場合は、正しく動きます。rangeの前にsheet名を書いてみたりしましたが、成功しませんでした。
このような場合、どのように記述すれば良いのでしょう?よろしくお願い致します。
Answer   Copyright (C) 2000.9.13 永井善王
あなたが参考にされた「変数名で範囲指定して選択するには R1C1」を下に写しておきました。
Sub 変数名で範囲指定して選択する_R1C1方式()
    Worksheets("SSS").Activate                  'ワークシートをアクティブにする ※1
        左 = 2                                  '選択する範囲の左端セルの行番号 ※3
        上 = 3                                  '   〃   上端 〃 列番号 ※3
        右 = 5                                  '   〃   右端 〃 行番号 ※3
        下 = 6                                  '   〃   下端 〃 列番号 ※3
    Range(Cells(上, 左), Cells(下, 右)).Select  '指定された範囲を選択する
End Sub
2行目がポイントで、ワークシートをアクティブにしています。
範囲を選択するためのコードは 7行目にありますが、Rangeプロパティと Cellsプロパティを併用する書き方になっています。

単一セルを選択する場合は、そんなに神経を使わなくてもよいのですが、セル範囲(矩形)を指定して選択する場合には、対象シートがアクティブになっているかどうかで、コードの書き方を変える必要がある場合があります。

「コマンドボタンクリック時に、選択されたセルに罫線を引く」ための方法は、いくつか考えられます。

A案 選択された他シートをアクティブにしてから罫線を引き、終ったらコマンドボタンのあるシートに戻す方法
この場合は画面のチラツキを避けるために、
画面更新の制御 のコードを入れておきます。

B案 Rangeプロパティと Cellsプロパティにワークシートを指定する方法
コマンドボタンのあるシートの名前を SSS、他のシートの名前を SS2 として罫線を引くマクロを書いてみると、次のようになります。
Sub 罫線の設定_他のシートのセル範囲へ()
  Worksheets("SSS").Activate                  'SSSをアクティブにする
  Worksheets("SS2").Cells.Clear               'SS2をすべてクリアする
  左 = 2                                      '罫線設定範囲の左端
  上 = 2                                      '上端
  右 = 4                                      '右端
  下 = 3                                      '下端
  Worksheets("SS2"). _
  Range(Worksheets("SS2").Cells(上, 左), Worksheets("SS2").Cells(下, 左 + 1)). _
  Borders.LineStyle = xlContinuous            '罫線のスタイルを設定
  Worksheets("SS2"). _
  Range(Worksheets("SS2").Cells(上, 左), Worksheets("SS2").Cells(下, 左 + 1)). _
  Borders.Weight = xlMedium                   '罫線の太さを設定
  Worksheets("SS2"). _
  Range(Worksheets("SS2").Cells(上, 左), Worksheets("SS2").Cells(下, 左 + 1)). _
  Borders.Color = RGB(255, 0, 0)              '罫線の色を赤に設定
End Sub
いちいちワークシート名が入るので 1行が長くなってしまいます。そのため罫線を引くための 1つ目のマクロは、8~10行目の 3行にまたがって書かれています。2つ目は11~13行目、3つ目は14~16行目です。
必ずしも 3行に分けないといけない訳ではありませんが、見やすくするためです。また、いちいちワークシート名を入れた書き方をした理由は、ルールがわかりやすいからです。きちんと理解できたら、Setステートメントで簡略化してください。

また、質問の主旨が「他のシートのセル範囲を変数で指定する」ことのようですので、罫線を引くためのコードについては、くわしく解説しませんが、Withステートメントでまとめることも可能です。
なお、あなたは Excel2000をお使いで必要ないかと思いますが、LineStyleプロパティと Weightプロパティの定数は Excelのバージョンによって相違があります。よって、作ったマクロを Excel95で実行する場合は、注意してください。
以下は参考情報です。
罫線を引くとエラーが出るが
罫線のプロパティの定数は
罫線の設定FS罫線の設定(95文法)罫線の消去

C案
A1方式で記述する方法
その都度セル範囲が変化する場合は、マクロの書き方が複雑になり工夫が必要ですが、あらかじめセル範囲がわかっている場合には、同じシートの書き方に
Worksheets("SS2"). を付加すれば可能になるはずです。

Excel VBA Macro