Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6098 / 13645 ツリー ←次へ | 前へ→

【47113】セルと一緒に編集許可範囲もコピーしたい GHQ 07/3/1(木) 12:04 質問[未読]
【47116】Re:セルと一緒に編集許可範囲もコピーしたい りん 07/3/1(木) 14:30 発言[未読]
【47117】Re:セルと一緒に編集許可範囲もコピーしたい GHQ 07/3/1(木) 16:11 回答[未読]
【47130】Re:セルと一緒に編集許可範囲もコピーしたい りん 07/3/1(木) 19:35 回答[未読]
【47151】Re:セルと一緒に編集許可範囲もコピーしたい GHQ 07/3/2(金) 15:29 質問[未読]
【47172】Re:セルと一緒に編集許可範囲もコピーしたい りん 07/3/2(金) 21:09 回答[未読]
【47234】Re:セルと一緒に編集許可範囲もコピーしたい GHQ 07/3/5(月) 18:41 お礼[未読]

【47113】セルと一緒に編集許可範囲もコピーしたい
質問  GHQ  - 07/3/1(木) 12:04 -

引用なし
パスワード
   明細行をExcelの限界まで増やせることを想定した表を作成しています。
計算式の入ったセルは編集不可にしたいので、
シートを保護した上で、特定のセルを編集許可にする方針です。
明細をコピーすると、明細に適用された編集許可範囲も
同時にコピーするようにしたいと考えています。

以下の例の場合、どのようにすればよいでしょうか?
よろしくお願いします。

明細範囲セル B10〜F26
(1明細追加するとB27〜F43に上記と同じ書式や計算式が適用)
編集可能セル C11、D12〜F14、D15〜F16
(1明細追加するとB28、D29〜F41、D42〜F43が編集可能)

【47116】Re:セルと一緒に編集許可範囲もコピーし...
発言  りん E-MAIL  - 07/3/1(木) 14:30 -

引用なし
パスワード
   GHQ さん、こんにちわ。

>明細をコピーすると、明細に適用された編集許可範囲も
>同時にコピーするようにしたいと考えています。
コピーされませんか?

【47117】Re:セルと一緒に編集許可範囲もコピーし...
回答  GHQ  - 07/3/1(木) 16:11 -

引用なし
パスワード
   ▼りん さん:
>GHQ さん、こんにちわ。
>
>>明細をコピーすると、明細に適用された編集許可範囲も
>>同時にコピーするようにしたいと考えています。
>コピーされませんか?

手作業の場合、メニューのツール→保護→範囲の編集を許可を開き、
=$BG$22,$BF$24:$BI$26,$BK$24:$BM$26,・・・・・
てな具合に、複数の範囲をコンマで区切って記述しますが、
編集許可範囲の一タイトルあたり、22組が限度のようです。
コピーする範囲に編集許可範囲が10組ある場合、
2度目のコピーの途中までしか編集範囲が適用されないわけです。

そこで、セルのコピーに続いて編集許可範囲のタイトルを作るため
(コピーが10回なら10タイトル作る)マクロを作ろうと考えました。

Copycount=5(コピーは5回)
st_index(コピーするたびに増えるインデックス)
meisai_start=10(オリジナルの明細はB10から)
meisai_end(オリジナルの明細はF16まで)
hensyu1_start=12(オリジナルの編集可能範囲はD11から)
hensyu1_end=14(オリジナルの編集可能範囲はF14まで)

といった変数を定義し、指定したコピー明細数だけループして
セルの行番号が増えていく、
(1回目の貼り付けはB17〜F23、編集可能範囲はD18〜F21、
2回目の貼り付けはB24〜F30、編集可能範囲はD25〜F28・・・・)
というロジックを組もうとしているのですが・・・・・
手作業をマクロに記録してからメンテしたものの、うまくいきません。
コピーを重ねるごとに数が増えるセルの番号は、
D(meisai_start & "): F(meisai_end & "),G(meisai_start & "): H(meisai_end & "),
という表記ではまずいのでしょうか?

【47130】Re:セルと一緒に編集許可範囲もコピーし...
回答  りん E-MAIL  - 07/3/1(木) 19:35 -

引用なし
パスワード
   GHQ さん、こんばんわ。

>手作業の場合、メニューのツール→保護→範囲の編集を許可を開き、
>=$BG$22,$BF$24:$BI$26,$BK$24:$BM$26,・・・・・
>てな具合に、複数の範囲をコンマで区切って記述しますが、
>編集許可範囲の一タイトルあたり、22組が限度のようです。
22組というより、グラフ等と同じく文字数の制限な気もします。
Sub Test()
  Dim II As Integer
  Dim r1 As Range, r2 As Range, resp As Integer
  '
  With Application.ActiveSheet
   .Unprotect
   If .Protection.AllowEditRanges.Count = 1 Then
     '設定が一つだけあったらセット(ほかは無視)
     Set r2 = .Protection.AllowEditRanges.Item(1).Range
     resp = vbYes
   Else
     MsgBox .Protection.AllowEditRanges.Count
     resp = MsgBox("コピーしますか?", vbYesNo, "許可設定が0または複数でした")
   End If
   '
   If resp = vbYes Then
     For II = 1 To 100
      Set r1 = .Cells((II - 1) * 17 + 27, 2)
      '
      .Range("B10:F26").Copy Destination:=r1
      If Not r2 Is Nothing Then
        '編集許可をセルを追加
        .Protection.AllowEditRanges.Add _
         Title:="Add_" & Format(II, "0000"), _
         Range:=r2.Offset(17 * II, 0)
      End If
     Next
     '1つめは範囲が拡張されるので戻す
     If Not r2 Is Nothing Then
      Set .Protection.AllowEditRanges.Item(1).Range = r2
     End If
   End If
  End With
End Sub
こんな感じです。
コピーした後で、参照位置をOFFSETでずらして設定していきます。
'
パスワードを設定しないなら、書式で保護をしないほうが簡単ですね。

おまけ
Sub test2()
  '一つ目の条件以外を削除
  Dim II As Integer, IMax As Integer
  With Application.ActiveSheet
   .Unprotect
   IMax = .Protection.AllowEditRanges.Count
   For II = IMax To 2 Step -1
     .Protection.AllowEditRanges.Item(II).Delete
   Next
  End With
End Sub

【47151】Re:セルと一緒に編集許可範囲もコピーし...
質問  GHQ  - 07/3/2(金) 15:29 -

引用なし
パスワード
   ▼りん さん:

>  With Application.ActiveSheet
>   .Unprotect
>   If .Protection.AllowEditRanges.Count = 1 Then
>     '設定が一つだけあったらセット(ほかは無視)
>     Set r2 = .Protection.AllowEditRanges.Item(1).Range
>     resp = vbYes

シートの中に範囲編集可能設定は複数あるので、
その中から該当する設定を選んで処理したものの、うまくいきません。
たとえば、上から3番目にある編集可能設定を当てはめるため、

Set r2 = .Protection.AllowEditRanges.Item(3).Range

とやると、一番上の設定をコピーしてくれたりします。
(Item番号と画面上の順番とは別物?)

ならば設定名称で判断しようと考え、

Dim stindx_st As Long
stindx_st = 1
Do Until Protection.AllowEditRanges.Title(stindex_st) = "内工費明細"
  stindx_st = stindx_st + 1
Loop

とやると、"実行時エラー424 オブジェクトが必要です"となります。

お目当ての設定名称を見つけるまでループする場合、
正しい書き方はどうすればよいでしょうか?

たびたびで申し訳ありません・・・・・

【47172】Re:セルと一緒に編集許可範囲もコピーし...
回答  りん E-MAIL  - 07/3/2(金) 21:09 -

引用なし
パスワード
   GHQ さん、こんばんわ。

>お目当ての設定名称を見つけるまでループする場合、
>正しい書き方はどうすればよいでしょうか?

正しいかどうかは不明ですがセットしてみました
Sub Test()
  Dim II As Integer, addname As String, obj1 As Object
  Dim r1 As Range, r2 As Range, resp As Integer, aer As String
  '
  aer = "内工費明細" '下方向にコピーする設定名
  '
  With Application.ActiveSheet
   .Unprotect
   'あったらセット
   On Error Resume Next
   Set r2 = .Protection.AllowEditRanges.Item(aer).Range
   On Error GoTo 0
   '
   If r2 Is Nothing Then
     resp = MsgBox("それでもコピーしますか?", vbYesNo, aer & "がありません")
   Else
     resp = vbYes
   End If
   '
   If resp = vbYes Then
     MsgBox r2.Address
     For II = 1 To 100
      Set r1 = .Cells((II - 1) * 17 + 27, 2)
      '
      .Range("B10:F26").Copy Destination:=r1
      If Not r2 Is Nothing Then
        '編集許可をセルを追加
        '適当な名前
        addname = "Add_" & Format(II, "0000")
        '有無チェック(あればDel)
        On Error Resume Next
        Set obj1 = .Protection.AllowEditRanges.Item(addname)
        On Error GoTo 0
        If Not obj1 Is Nothing Then
         obj1.Delete
         Set obj1 = Nothing
        End If
        '再設定
        .Protection.AllowEditRanges.Add _
         Title:=addname, _
         Range:=r2.Offset(17 * II, 0)
      End If
     Next
     '範囲が拡張されるので戻す
     If Not r2 Is Nothing Then
      Set .Protection.AllowEditRanges.Item(aer).Range = r2
     End If
   End If
   '
   Set r1 = Nothing: Set r2 = Nothing
  End With
End Sub

複数ある場合はコピーで範囲が変わる可能性が高いので注意が必要かもしれません。

【47234】Re:セルと一緒に編集許可範囲もコピーし...
お礼  GHQ  - 07/3/5(月) 18:41 -

引用なし
パスワード
   >複数ある場合はコピーで範囲が変わる可能性が高いので注意が必要かもしれません。

一シート内でコピーが必要な編集可能範囲が四つというベラボーなシートですが、
エンドユーザが操作するものではなく、
初期設定として一回コピーしてしまえばOKなので、
おかげさまでこの方式でどうにかなりました。
本当にありがとうございました。

6098 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free