Excel VBA質問箱 IV

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

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


3452 / 13644 ツリー ←次へ | 前へ→

【62105】入力規則エラーとシート保護 please 09/6/23(火) 5:00 質問[未読]
【62108】Re:入力規則エラーとシート保護 フリッパー 09/6/23(火) 11:29 発言[未読]
【62109】Re:入力規則エラーとシート保護 please 09/6/23(火) 14:08 回答[未読]
【62113】Re:入力規則エラーとシート保護 フリッパー 09/6/23(火) 15:28 発言[未読]
【62114】Re:入力規則エラーとシート保護 please 09/6/23(火) 15:39 回答[未読]
【62120】Re:入力規則エラーとシート保護 フリッパー 09/6/23(火) 17:17 発言[未読]
【62133】Re:入力規則エラーとシート保護 please 09/6/23(火) 22:49 お礼[未読]
【62110】Re:入力規則エラーとシート保護 もも 09/6/23(火) 14:32 発言[未読]
【62112】Re:入力規則エラーとシート保護 please 09/6/23(火) 15:19 回答[未読]
【62115】Re:入力規則エラーとシート保護 もも 09/6/23(火) 16:13 発言[未読]
【62124】Re:入力規則エラーとシート保護 please 09/6/23(火) 18:00 質問[未読]
【62125】Re:入力規則エラーとシート保護 もも 09/6/23(火) 18:09 発言[未読]
【62127】Re:入力規則エラーとシート保護 please 09/6/23(火) 20:27 お礼[未読]
【62139】Re:入力規則エラーとシート保護 フリッパー 09/6/24(水) 9:16 発言[未読]

【62105】入力規則エラーとシート保護
質問  please  - 09/6/23(火) 5:00 -

引用なし
パスワード
   初めて投稿します。
現在、vbaで入力規則を設定しようとしています。
シート保護をかける前は問題なく動作するのですが、保護をかける設定を記述するとエラーになってしまいます。。
最初に種類を選ぶと、その種類に該当するプランのリストがでて、その種類にサブ項目がある場合、サブ項目を選ぶことができるというような仕様です。

以下の「Range("ABC").Validation.Add Type:=xlValidateList, Formula1:="=" & testp」の部分でエラーになります。
他の箇所ではエラーが出ません。

どなたかわかる方ご教示ください。よろしくお願いします。

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("種類,プラン")) Is Nothing Then
  Const MYPW = "abc"
  Worksheets("入力").Unprotect MYPW
   
  Select Case Target.Name.Name
    Case "種類"
      Dim carname As Variant
      carname = Range("種類").Value
      If carname = "種類を選んでください" Then
        Dim Selna As Variant
        Selna = Array("テスト1", "テスト2", "テスト3")
        
        For Each N In Selna
          Range(N).Validation.Delete
          Range(N).Value = "種類を選んでください"
        Next
      Else
        Range("プラン").Validation.Delete
        Range("プラン").Validation.Add Type:=xlValidateList, Formula1:="=" & cname
        Range("サブ").Value = "種類・プランを選んでください"
        Range("サブ項目").Validation.Delete
        Range("サブ項目").Value = "プランを選んでください"
        Range("プラン").Value = "プランを選んでください"
        Range("サブ項目種類").Value = "プランを選んでください"
      End If
     Case "プラン"
      Dim chkn As Variant
      Dim chkh As Variant
      chkn = Range("chkn").Value
      chkh = Range("chkh").Value
      If Range("プラン").Value <> "プランを選択してください" Then
        If chkn = "あり" Then
          Range("サブ項目").Validation.Delete
          Range("サブ項目").Validation.Add Type:=xlValidateList, Formula1:="=glade"
          Range("サブ項目").Value = "サブ項目を選択できます"
          Range("サブ項目種類").Value = "サブ項目種類を選択してください"
        Else
          Range("サブ").Validation.Delete
          If Range("chkn").Value = "-" Then
            Range("サブ").Value = "サブ項目はありません"
          Else
            Range("サブ").Value = "プランを選んでください"
          End If
        End If
        If chkh <> "" Then
          Range("ABC").Value = "ABCを選択してください"
          Range("ABC").Validation.Add Type:=xlValidateList, Formula1:="=" & testp
        Else
           Range("ABC").Validation.Delete
          Range("ABC").Value = "種類・プランを選んでください"
        End If
      End If


  End Select
  
  Worksheets("入力").Protect Password:=MYPW, UserInterfaceOnly:=True
End If

End Sub

【62108】Re:入力規則エラーとシート保護
発言  フリッパー  - 09/6/23(火) 11:29 -

引用なし
パスワード
   >シート保護をかける前は問題なく動作するのですが、保護をかける設定を記述するとエラーになってしまいます。。

>以下の「Range("ABC").Validation.Add Type:=xlValidateList, Formula1:="=" & testp」の部分でエラーになります。
の因果関係がわかりませんけど、2つほど、
「testp」は、変数なのでしょうか?
変数だといたら中身が何も入ってません。

Range("ABC").Validation.Delete
Range("ABC").Validation.Add・・・・
作成する前に削除しないとダメなんじゃないかと・・・。
(新規だけってわけじゃなさそうだし)

【62109】Re:入力規則エラーとシート保護
回答  please  - 09/6/23(火) 14:08 -

引用なし
パスワード
   ご返信ありがとうございます。

すいません。。testpは投稿のために名前を整形した時のミスです。
testp ではなく、chkh が正しいです。

そうでした。削除が必要ですよね。。
Range("ABC").Validation.Delete を追加してみましたが、
やはり同じ個所でエラーが出てしまいます。

動作的には上の部分で行っている
Range("プラン").Validation.Add Type:=xlValidateList, Formula1:="=" & cname
と同じなのですが。。
ここはエラーが出ません。。

【62110】Re:入力規則エラーとシート保護
発言  もも  - 09/6/23(火) 14:32 -

引用なし
パスワード
   ▼please さん:
こんにちは

変数cnameとchkhの中にはどんな値が入るのですか?
場合によっては"="は要らないと思います。

また、別のことですが途中でセルの中身を変えているので

Application.EnableEvents = False

Application.EnableEvents = True

で挟んだ方が宜しいかと思います。

【62112】Re:入力規則エラーとシート保護
回答  please  - 09/6/23(火) 15:19 -

引用なし
パスワード
   ももさんご指摘ありがとうございます。
初心者ですので、Application.EnableEventsは知りませんでした。。
後で調べてみたいと思います。

cnameとchkhはともに、リストの選択肢と同じ名前でそれが選ばれた時に表示する次のリスト名を定義しています。

こんな説明で分って頂けるでしょうか?

ちなみに、テストで

If chkn = "あり" Then 
〜〜〜
End If
(↑If chkh <> "" Thenの上の行)

までをコメントにすると、もちろんその箇所は反映されないのですが、
必ずエラーが出ていた箇所は問題なく動作します。。

        

【62113】Re:入力規則エラーとシート保護
発言  フリッパー  - 09/6/23(火) 15:28 -

引用なし
パスワード
    ▼please さん:
>動作的には上の部分で行っている
>Range("プラン").Validation.Add Type:=xlValidateList, Formula1:="=" & cname
>と同じなのですが。。
>ここはエラーが出ません。。

>シート保護をかける前は問題なく動作するのですが、保護をかける設定を記述するとエラーになってしまいます。
これも当てはまるのでしょうか?
単にセルのロックを外し忘れているとか・・・?


【62114】Re:入力規則エラーとシート保護
回答  please  - 09/6/23(火) 15:39 -

引用なし
パスワード
   ▼フリッパー さん:

ありがとうございます。

>>シート保護をかける前は問題なく動作するのですが、保護をかける設定を記述するとエラーになってしまいます。
>これも当てはまるのでしょうか?
>単にセルのロックを外し忘れているとか・・・?

はじめ、セルのロックかもしれないと思い、今はロックを外していますが
変わりませんでした。。

【62115】Re:入力規則エラーとシート保護
発言  もも  - 09/6/23(火) 16:13 -

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

>cnameとchkhはともに、リストの選択肢と同じ名前でそれが選ばれた時に表示する次のリスト名を定義しています。

という事でしたら、ステップ実行してみて、

>Range("ABC").Validation.Add Type:=xlValidateList, Formula1:="=" & chkh
のときにchkhという変数に入っている名前が設定されていないとか?


>If chkh <> "" Then
> Range("ABC").Value = "ABCを選択してください"
> Range("ABC").Validation.Add Type:=xlValidateList, Formula1:="=" & testp
>Else
> Range("ABC").Validation.Delete
> Range("ABC").Value = "種類・プランを選んでください"
>End If

あとは、ここだけセルに直接入力規則を
=INDIRECT(chkh) と設定しておくとか。

【62120】Re:入力規則エラーとシート保護
発言  フリッパー  - 09/6/23(火) 17:17 -

引用なし
パスワード
   >Range("プラン").Validation.Add Type:=xlValidateList, Formula1:="=" & cname
>Range("サブ項目").Validation.Add Type:=xlValidateList, Formula1:="=glade"
>Range("ABC").Validation.Add Type:=xlValidateList, Formula1:="=" & testp
書き方が統一されていないようですが、この辺こんがらがってませんか?

【62124】Re:入力規則エラーとシート保護
質問  please  - 09/6/23(火) 18:00 -

引用なし
パスワード
   フリッパーさん、ももさん

ご質問にお答えいただきありがとうございます。

質問の仕方が悪くてすいません。。

検証した結果、一度に2ヶ所のセルにそれぞれ入力規則を設定しようとすると
必ず2つ目でエラーがでることがわかりました。

1回の動作で入力規則は1ヶ所だけという制限とかはあったりするのでしょうか??

【62125】Re:入力規則エラーとシート保護
発言  もも  - 09/6/23(火) 18:09 -

引用なし
パスワード
   ▼please さん:
>フリッパーさん、ももさん
>
>ご質問にお答えいただきありがとうございます。
>
>質問の仕方が悪くてすいません。。
>
>検証した結果、一度に2ヶ所のセルにそれぞれ入力規則を設定しようとすると
>必ず2つ目でエラーがでることがわかりました。
>
>1回の動作で入力規則は1ヶ所だけという制限とかはあったりするのでしょうか??

2回目というより
1つめの入力規則によって2つめの入力規則のリスト先の名前を指定しようと
した場合に、1つめに設定したセルが空白であったり設定されていない名前ですと
1つめを参照して名前から範囲を設定する事が出来ずにエラーとなります。

もう少し厳密に範囲指定した方が良いかもしれません。
また、SelectionChangeイベントに変更して、
入力リストが必要なセルを選択した時点で設定してあげたほうが
解りやすいかもしれません。

【62127】Re:入力規則エラーとシート保護
お礼  please  - 09/6/23(火) 20:27 -

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

何度もご丁寧に恐れ入ります。。

>2回目というより
>1つめの入力規則によって2つめの入力規則のリスト先の名前を指定しようと
>した場合に、1つめに設定したセルが空白であったり設定されていない名前ですと
>1つめを参照して名前から範囲を設定する事が出来ずにエラーとなります。
>
>もう少し厳密に範囲指定した方が良いかもしれません。

そういうことなのですね。。
うまくいかなかったので、vbaで入力規則を変更せず、セルに直にIF文でリストを仕分ける方法でなんとかエラーが出ないようになりました。

>また、SelectionChangeイベントに変更して、
>入力リストが必要なセルを選択した時点で設定してあげたほうが
>解りやすいかもしれません。

ありがとうございます。
IF文でリストを仕分ける方法だと必ずなんかしらリスト表示されてしまうので、行く行くは頑張ってvbaでも分岐できるように修正したいと思います。

この度はほんとにありがとうございました。

【62133】Re:入力規則エラーとシート保護
お礼  please  - 09/6/23(火) 22:49 -

引用なし
パスワード
   ▼フリッパー さん:
>>Range("プラン").Validation.Add Type:=xlValidateList, Formula1:="=" & cname
>>Range("サブ項目").Validation.Add Type:=xlValidateList, Formula1:="=glade"
>>Range("ABC").Validation.Add Type:=xlValidateList, Formula1:="=" & testp
>書き方が統一されていないようですが、この辺こんがらがってませんか?

すいません。。慌ててしまい投稿するときに移し間違えた箇所がありました。

なんとか、違う方法で対応できました。
この度はいろいろとお世話になりました。ありがとうございました。

【62139】Re:入力規則エラーとシート保護
発言  フリッパー  - 09/6/24(水) 9:16 -

引用なし
パスワード
   ▼please さん:
>検証した結果、一度に2ヶ所のセルにそれぞれ入力規則を設定しようとすると
>必ず2つ目でエラーがでることがわかりました。
>
>1回の動作で入力規則は1ヶ所だけという制限とかはあったりするのでしょうか??
前に、手動で同じセルを参照した時に片方の入力規則が破棄されていたような記憶があるのですが、もしかしたらその辺りかもしれませんね。

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