| 1 | 入力規則のリストの元の値に、他のシートにあるデータを利用したい http://cgi.fuji.ne.jp/~fj2094/cgi-bin1/wwwlng.cgi?print+200210/02100110.txt 「別のシートからリストの範囲を設定するには？（入力規則 リスト）」 【編集ラウンジ】 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Excel97、Excel2000 共通 1. あらかじめ、表示したい内容を他のシートに入力しておきます。 2. 入力したリストの範囲を選択して、[挿入]-[名前]-[定義]で名前を付けます。 3. 次に、リストから選んで入力したいシートのセルを選択し、 ［データ］−［入力規則］−［設定］で、［入力値の種類］を［リスト］、［元の値］を ［=名前］にします。この名前は、2番でリストの範囲につけた名前です。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Excel97、Excel2000 共通 ［データ］−［入力規則］−［設定］で、［入力値の種類］を［リスト］、［元の値］を =INDIRECT("別なシート!$A$1"):INDIRECT("別なシート!$A$10") |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 2 | 入力規則のリストを、セル選択時に表示させるには？ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Private
Sub Worksheet_SelectionChange(ByVal Target As Range) Dim IsDropdown As Boolean If Target.Cells.Count > 1 Then Exit Sub On Error Resume Next IsDropdown = Target.Validation.InCellDropdown If Err.Number <> 0 Then Exit Sub If Target.Validation.Type = xlValidateList And IsDropdown Then SendKeys "%{down}" End If End Sub このコードの使い方は、マクロの使い方(２)該当するシートのモジュールにあります。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 3 | 入力規則のリストの元の値に、直接複数のリスト項目を入力するには？ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 商品Ａ,商品Ｂ,商品Ｃ という風に、半角の「，」カンマ で区切って入力します。 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 4 | 入力した値と同じ値がその列中にある場合、メッセージを表示するには？ http://cgi.fuji.ne.jp/~fj2094/cgi-bin2/wwwlng.cgi?print+200107/01070239.txt 「入力した値が、その列中にあればメッセージを表示するには？」 【関数ラウンジ】 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Ａ１〜Ａ１００の範囲で、同じ値が入力された場合警告を出します。Excel2002 1. Ａ１を選択し、【データ】【入力規則】【設定】【条件の設定】の【ユーザー設定】【数式】に =countif(A$1:A$100,A1)<2 と記入して、ＯＫとします。 2. Ａ１をコピーし、Ａ２:Ａ１００を選択し、右クリック、【形式を選択し貼付け】【入力規則】をＯＮ。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 5 | 入力規則で、リストの元の値に空白のセル以外を設定するには |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Ａ１〜Ａ１０、Ａ２０〜Ａ３０のセルに入力規則で、I１００のセルからＩ１５０の間に入力されている 文字列の分だけリストを設定します。 Sub miko_test() Dim i As Integer, L As String L = "" For i = 1 To Cells(Rows.Count, 9).End(xlUp).Row L = L & "," & Cells(i, 9).Value Next L = Right(L, Len(L) - 1) Range("A1:A10,A20:A30").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=L End With Range("A1").Select End Sub このコードの使い方は、マクロの使い方(１)標準モジュールにあります。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 6 | 入力規則のリストに、他のセルに入力した値に対応する項目を設定するには |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 以下は、マクロを使って科目リストの項目を、入力規則のリストに設定する方法です。 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
***** 科目リスト *****
|
***** 入力用シート ***** ＢＣ列選択時に、【科目リスト】から選択リストを 表示します。
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ※ 入力用シートをアクティブにすると、【借方科目】【貸方科目】（BC列）の入力規則のリスト項目に、 【科目リスト】シートの【入力科目】（A2以降）の項目が設定されます。 ※ 次に、【借方科目】または【貸方科目】に、リストから選択して入力すると、相手の【借方科目】または【貸方科目】に、 【科目リスト】シートのA列の項目に対応する、B列以降の値が、 入力規則のリスト項目に設定されます。 ※ 【借方科目】または【貸方科目】をクリアすると、両方のセルの入力規則のリストに、 最初に設定されていた【科目リスト】シートのA列の項目が設定されます。 Private Sub Worksheet_Activate() Dim i As Long, N As String On Error GoTo TRAP N = "" 'リスト項目の初期化 '科目リストA列（1行目）の１行目から最終行までを N に代入 For i = 1 To Worksheets("科目リスト").Cells(Rows.Count, 1).End(xlUp).Row N = N & "," & Worksheets("科目リスト").Cells(i, 1).Value Next N = Right(N, Len(N) - 1) 'BC列の入力規則のリスト項目に N を設定 With Columns("B:C").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=N End With TRAP: End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long, j As Long, N As String On Error GoTo TRAP '該当セルが4列目以降で、1目の場合処理しないで終わる If Target.Column > 3 Or Target.Row = 1 Then Exit Sub N = "" 'リスト項目の初期化 '科目リストA列（1行目）の１行目から最終行までを N に代入 '科目リストの1行目から最終行まで繰り返す For i = 1 To Worksheets("科目リスト").Cells(Rows.Count, 1).End(xlUp).Row '変更した値と同じものが科目リストのA列にあれば、その行のB列以降の値をリスト項目にする If Target.Value = Worksheets("科目リスト").Cells(i, 1) Then '科目リストi行目の2列目から最終列まで繰り返す For j = 2 To Worksheets("科目リスト").Cells(i, Columns.Count).End(xlToLeft).Column 'Nの値に、i行j列の値をつぎたす N = N & "," & Worksheets("科目リスト").Cells(i, j).Value Next End If Next N = Right(N, Len(N) - 1) '該当のセルがB列（2列目）の場合 If Target.Column = 2 Then 'その行のC列の入力規則のリスト範囲を N の値にする With Target.Offset(0, 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=N End With '該当のセルがC列（3列目）の場合 ElseIf Target.Column = 3 Then 'その行のB列の入力規則のリスト範囲を N の値にする With Target.Offset(0, -1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=N End With End If Exit Sub TRAP: 'クリアの場合、入力規則を戻す N = "" 'リスト項目の初期化 '科目リストA列（1行目）の１行目から最終行までを N に代入 For i = 1 To Worksheets("科目リスト").Cells(Rows.Count, 1).End(xlUp).Row N = N & "," & Worksheets("科目リスト").Cells(i, 1).Value Next 'BC列の入力規則のリスト項目に N を設定 With Columns("B:C").Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=N End With End Sub このコードの使い方は、マクロの使い方(２)該当するシートのモジュールにあります。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 7 | 入力規則のリストに、複数列のセルの値を設定するには？ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| A1〜B5の範囲のリストを、C1〜C10,C20〜C30のセルの入力規則に設定します。 Sub miko_test() Dim i As Integer, j As Integer, L As String L = "" For i = 1 To 2 For j = 1 To 5 L = L & "," & Cells(j, i).Value Next j Next i L = Right(L, Len(L) - 1) Range("C1:C10,C20:C30").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=L End With Range("A1").Select End Sub このコードの使い方は、マクロの使い方(１)標準モジュールにあります。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 8 | 入力規則のリストを、使用頻度の多い順に並び替えるには |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Ｂ列のリストを、Ａ列の使用頻度順に、Ｇ列に書き出します。 (1) Ｃ１からＣ４に１から４の数値を順番に入れます。 (2) Ｄ１に =COUNTIF(A:A,"="&B1) と入れてＤ４までコピーします。 (3) Ｅ１に =RANK(D1,$D$1:$D$4)*100+C1 と入れてＥ４までコピーします。 (4) Ｆ１に =RANK(E1,$E$1:$E$4,1) と入れてＦ４までコピーします。 (5) Ｇ１に =INDEX($B$1:$B$4,MATCH(C1,$F$1:$F$4,0),1) と入れてＧ４までコピーします。 (6) 入力規則のリスト範囲を =$G$1:$G$4 に変更します。 (7) Ｂ〜Ｇ列を非表示にします。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Ａ列に設定したＢ列の入力規則のリストを、Ａ列に値が変更される都度、使用頻度の多い順に並べ替えます Private Sub Worksheet_Change(ByVal Target As Range) Dim myList As Variant Dim i As Integer Dim a As String Dim MyFlg As Boolean If Target.Column <> 1 Then Exit Sub myList = Range("B1", Cells(Rows.Count, 2).End(xlUp)).Value With Application.WorksheetFunction Do MyFlg = True For i = 1 To UBound(myList) - 1 If .CountIf(Columns(1), myList(i, 1)) < .CountIf(Columns(1), myList(i + 1, 1)) Then a = myList(i, 1) myList(i, 1) = myList(i + 1, 1) myList(i + 1, 1) = a MyFlg = False End If Next Loop Until MyFlg End With Range("B1").Resize(UBound(myList)).Value = myList End Sub このコードの使い方は、マクロの使い方(２)該当するシートのモジュールにあります。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 9 | 入力規則のリストを他のファイルのリストから設定し、そのファイルを閉じた状態で使用するには？ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1. マスタ.xlsSheetXXのA1:A10をコピー、売上.xlsの任意のシートにリンク貼り付けします。 2. ='[マスタ.xls]SheetXX'!A1から10と参照された範囲を名前定義します。 3. 入力規則、リストにF3キーからその名前を選択して貼り付けます。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 売上.xlsのSheet1、A1〜A10の範囲にマスタ.xlsのsheet1、A1〜A10の値を入力規則のリスト範囲に設定します。 一つ目のコードは、一旦マスタ.xlsを開き、A1~A10の範囲の値を、売上.xlsの入力規則のリスト範囲に直接書き込んでいます。 二つ目のコードは、マスタ.xlsのリストの値が変更されたら、一つ目のコードを実行して入力規則のリストを更新するコードです。 尚、両方のファイルは同じフォルダに入れておく必要があります。 最初にコードを書き込んだ際には、【リスト設定】のマクロを実行してください。 これでマスタ.xlsが開いていなくても、売上.xlsの入力規則のリストを表示することができ、 マスタ.xlsのリストの値が変更されれば、売上.xlsの入力規則のリストも変更します。 '************************************************ '売上.xlsの標準モジュールに書き込みます。 '売上.xlsのSheet1、A1〜A10の範囲に、入力規則を設定しています。 '************************************************ Sub リスト設定() 'マスタ.xlsは、売上.xlsと同じフォルダにあるものとします Dim i As Integer, L As String, wb As Workbook, hantei As Integer Application.ScreenUpdating = False '画面の動きを固定 '既にBookが開いているかどうかをチェックして、開いていなければ開く For Each wb In Workbooks If wb.Name = "マスタ.xls" Then wb.Activate Exit For End If Next If ActiveWorkbook.Name <> "マスタ.xls" Then Workbooks.Open Filename:=ThisWorkbook.Path & "\マスタ.xls" hantei = 1 End If '入力規則のリスト用に、マスタブックのA1〜A10の値を設定 With Workbooks("マスタ.xls").Sheets("Sheet1") L = "" For i = 1 To 10 L = L & "," & .Cells(i, 1).Value Next L = Right(L, Len(L) - 1) End With '売上ブックのA1〜A10の範囲に、入力規則のリストを書き込む Workbooks("売上.xls").Sheets("Sheet1").Activate Range("A1:A10").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=L End With '既にマスタ.xlsが開いていなければ、保存して閉じます。 If hantei = 1 Then Workbooks("マスタ.xls").Save Workbooks("マスタ.xls").Close End If Application.ScreenUpdating = True '画面の固定を解除 End Sub ' このコードの使い方は、マクロの使い方(１)標準モジュールにあります。 '************************************************ 'マスタ.xlsのsheet1のモジュールに書き込みます。 'マスタ.xlsのsheet1、A1〜A10がのいずれかの値が変更されると '売上.xlsのリスト設定のマクロを実行して入力規則のリストを更新します '************************************************ Private Sub Worksheet_Change(ByVal Target As Range) Dim wb As Workbook, hantei As Integer Application.ScreenUpdating = False '画面の動きを固定 'A1〜A10以外のセルの場合、処理しないで終わる If Target.Row > 10 Or Target.Column <> 1 Then Exit Sub '既にBookが開いているかどうかをチェックして、開いていなければ開く For Each wb In Workbooks If wb.Name = "売上.xls" Then wb.Activate Exit For End If Next If ActiveWorkbook.Name <> "売上.xls" Then Workbooks.Open Filename:=ThisWorkbook.Path & "\売上.xls" hantei = 1 End If 'リスト設定のマクロを実行する Application.Run "売上.xls!リスト設定" '既に売上.xlsが開いていなければ、保存して閉じます。 If hantei = 1 Then Workbooks("売上.xls").Save Workbooks("売上.xls").Close End If Windows("マスタ.xls").Activate Application.ScreenUpdating = True '画面の固定を解除 End Sub ' このコードの使い方は、マクロの使い方(２)該当するシートのモジュールにあります。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 10 | セルを選択したとき、コメントを表示するには？ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 【データ】【入力規則】【入力時メッセージ】で、セル選択時にメッセージを表示するにチェックを入れ、 セル選択時に表示するメッセージを入力します。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 11 | 入力規則のドロップダウンリストの文字を大きくするには？ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 根本的にはできません。以下、無理やり大きさが変わっているように見せる方法です。 [方法] １．画面倍率を１５０％等適当な倍率にします．（リストを大きくしたいとき） ２．フォントサイズや，行間列間隔等をそれぞれ個別に６６％に縮小。 １００％表示のときと同じサイズに合わせます。 [結果] ３．これで，あたかもリストボックスが拡大したかのように見えます。 [制約] ４．行列番号が，表示倍率に合わせて拡縮されてしまう。 [ツール][オプション]で，[行列番号表示]をOFFしても問題なければよいのですが．．． ５．特定のリストボックスだけを拡縮することが出来ない。 全てのリストボックス等，大きさを可変できないコントロールは当倍率で拡縮されます。 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 「ダイアログフォント」というのを変更すればできるようですが、レジストリを変更しますので試していません。m(_
_)m [XL95]ダイアログ ボックス内のフォントを変更する方法 http://support.microsoft.com/default.aspx?scid=kb;ja;JP405456 上のサポート情報にあるフォルダは、バージョンごとに違うようです。 Excel95 HKEY_CURRENT_USER\SOFTWARE\Microsoft\Excel\7.0\Microsoft Excel Excel 97 HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\Microsoft Excel Excel 2000 HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 文字サイズ・リストの大きさを自由に設定できるコンボボックスを、入力規則のリストのように使用するには | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 12 | 入力規則のドロップダウンリストの幅を広くするには？ |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 1.Case "E1", "E2" の部分は入力規則（リスト）が設定されていてリストの幅を広げたいセル番地です。 2.s.Width = 150 の数値を変えると幅が変わります。 3.ドロップダウンの名前（"Drop Down 1" とか）はイミディエイトウィンドウで ?activesheet.shapes(1).name などど指定して調べてください。 4.セルを選択すると自動的にリストが表示されます。 自動表示をしたくない場合はSendKeys "%{down}" を削除してください。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim s As Shape Dim BtnWidth As Single With ActiveCell Select Case .Address(False, False) Case "E1", "E2" If .Validation.Type = xlValidateList Then Set s = ActiveSheet.Shapes("Drop Down 1") BtnWidth = s.Width - .Width s.Width = 150 s.Left = .Left + .Width - s.Width + BtnWidth Set s = Nothing SendKeys "%{down}" End If End Select End With End Sub |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||