Excel VBA質問箱 IV

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

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


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

【40941】入力規則をVBAで制御 すず 06/7/26(水) 16:42 質問[未読]
【40945】Re:入力規則をVBAで制御 Kein 06/7/26(水) 17:30 回答[未読]
【40969】Re:入力規則をVBAで制御 すず 06/7/27(木) 9:13 お礼[未読]
【40946】Re:入力規則をVBAで制御 ハト 06/7/26(水) 17:35 回答[未読]
【40970】Re:入力規則をVBAで制御 すず 06/7/27(木) 9:14 お礼[未読]

【40941】入力規則をVBAで制御
質問  すず  - 06/7/26(水) 16:42 -

引用なし
パスワード
   色々と方法を考えましたが思いつかず投稿しました。

Sheet1のC4セルにフォルダ名を入力します。
この名前は横に配置されたボタンを押すとフォルダ名選択ダイアログが出てきてそちらから選ぶとC4セルにフォルダ名が表示されるようになっています。
もし入力されなかった場合はエラーになりますが、フォルダ名以外の文字などが入力された場合にエラー回避できなくなってしまいます。
入力規則を使用しようかと思ったのですが、たくさんのフォルダがあるので、見難くなる為にダイアログ表示としています。

そこでC4のセルに入力規則を一応組み込んでおいて、他の文字などが入力された場合にエラーを出すようにするのがいいかなと思い考えました。
しかしそのフォルダ名ですが、一定ではありません。
リストが変化する可能性があるので、ブック読込みの際にSheet1のG列にフォルダ名一覧を読込させて非表示にし、何列あるかをそのたびごとに確認する為にH1列に数値を入力しています。
そこで動的に入力規則のリストを変化させるのに、以下のようにコードを書いてみましたがエラーが出ます。

Sub test()
Dim lRng    As String
Dim rRng    As String
Dim hRng    As String
Dim k      As Integer
  k = Worksheets("main").Range("H1").Value
  lRng = "G1"
  rRng = "G" & k
  hRng = lRng & ":" & rRng
  With Worksheets("main").Range("C4")
      .Add Type:=xlValidateList, AlertStyle:= _
        xlValidAlertStop, Operator:= xlBetween, _
        Formula1:=hRng 
      .ErrorMessage = "駐車場名を正しく選択してください"
      .IMEMode = xlIMEModeNoControl
  End With
End Sub

このような方法でなくても、うまいエラーへの対処方法がありましたら教えていただけるとありがたいです。

よろしくお願い致します。

【40945】Re:入力規則をVBAで制御
回答  Kein  - 06/7/26(水) 17:30 -

引用なし
パスワード
   一例をあげると IV1セルとか、ファイルのプロパティのコメント欄に、
フラグを立てておくという方法が考えられます。

[ThisWorkbookモジュール]

Private Sub Workbook_Open()
  ThisWorkbook.Comments = "False"
End Sub

[Sheet1のWorksheetモジュール]

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
   If .Address <> "$C$4 Then Exit Sub
   If .Count > 1 Then Exit Sub
   If IsEmpty(.Value) Then Exit Sub
   If ThisWorkbook.Comments = "False" Then
     Application.EnableEvents = False
     .ClearContents
     Application.EnableEvents = True
   End If
  End With
End Sub

[標準モジュール]

Sub MyFolder()
  Dim objShell As Object, objFolder As Object
  Dim FolN As String

  Set objShell = CreateObject("Shell.Application")
  Set objFolder = objShell _
  .BrowseForFolder(0, "フォルダを選択して下さい", 0, "C:\")
  If objFolder Is Nothing Then 
   Set objShell = Nothing: Exit Sub
  End If
  FolN = objFolder.Items().Item().Path
  With ThisWorkbook
   .Comments = "True": Range("C4").Value = FolN
   .Comments = "False"
  End With
  Set objFolder = Nothing: Set objShell = Nothing
End Sub

これ以外にも、いろいろやり方はあると思います。
例えばシートの Protect を"パスワード付き、かつUserInterFaceOnly:=True"で
しておくとか、SelectionChangeイベントで C4 を選択したら自動的に他のセルを
アクティブにして、入力できないようにしてしまうとか・・。
ま、そのへんは気に入ったやり方を、ご自分で試してみることです。
それらは少なくとも、入力規則を設定するやり方より簡単だと思います。

【40946】Re:入力規則をVBAで制御
回答  ハト  - 06/7/26(水) 17:35 -

引用なし
パスワード
   おはようございます

▼すず さん:

>そこで動的に入力規則のリストを変化させるのに、以下のようにコードを書いてみましたがエラーが出ます。

エラーの原因と思われるところは


>Sub test()
>Dim lRng    As String
>Dim rRng    As String
>Dim hRng    As String
>Dim k      As Integer
>  k = Worksheets("main").Range("H1").Value
>  lRng = "G1"
>  rRng = "G" & k
>  hRng = lRng & ":" & rRng        '←変更
  hRng = "=" & lRng & ":" & rRng  
>  With Worksheets("main").Range("C4")  '←変更
  With Worksheets("main").Range("C4").Validation
      .Delete             '←追加
>      .Add Type:=xlValidateList, AlertStyle:= _
>        xlValidAlertStop, Operator:= xlBetween, _
>        Formula1:=hRng 
>      .ErrorMessage = "駐車場名を正しく選択してください"
>      .IMEMode = xlIMEModeNoControl
>  End With
>End Sub

以上3箇所を修正して試してみて下さい

【40969】Re:入力規則をVBAで制御
お礼  すず  - 06/7/27(木) 9:13 -

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

おはようございます。
難しく考えすぎていたみたいです。
Keinさんのおっしゃる中で一番簡単そうなWorksheet_SelectionChangeイベントにて変更したくないセルを選択した場合には別のセルに飛ぶというような処理にしてみるとうまくいきました。

勉強になりました。
ありがとうございました。

【40970】Re:入力規則をVBAで制御
お礼  すず  - 06/7/27(木) 9:14 -

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

おはようございます。
今回のコードのエラーの原因は解決しました。
しかし難しく考えなくても他の方法で対処できることがわかりました。
勉強になりました。
ありがとうございました。

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