関数とリスト

質問

お世話になってます。

エクセル２００３で、
経理の書類を作っています。

そのなかで、A2に１－（１）、１－（２）といったコードを入れています。
５種類ほどあり、たとえば、同じ消しゴムでも１－（１）だったり１－（２）だったりします。

しかし、A1に○がついている物品（３０品ほど）のときは
コードが常に２－（１）になります。

コードはいつも入力規則のリストから選んでいるのですが、
A1に○があるときは、常にA2に○が表示され、
それ以外のときはリストから選べるようにする
ということはかのうでしょうか？

よろしくおねがいします。

回答No.7

Masa2072 · ベストアンサー

> 実際には、A1にあたるのは、L54:P54　（セルの結合しています。） B2は、R53:V53　（結合）です。

リストボックスを表示させるセルはR53（R53:B53)だけでよろしいのでしょうか？
処理対象セルをL54、R53に限定した場合のソースは以下の様になります

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$54" Then
If Target.Value = "○" Then
Range("R53").Value = "2-(1)"
End If
ElseIf Target.Address = "$R$53" Then
If Range("L54").Value = "○" And Target.Value <> "2-(1)" Then
Target.Value = "2-(1)"
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.ListBox1
'R53にリスト外の文字が入っているとエラーになるので、エラーはスキップさせる
On Error Resume Next
'セル結合を行っているためTarget.Addressは$R$53ではない
If Target.Address = "$R$53:$V$53" Then
If Range("L54").Value = "○" Then
.Visible = False
Target.Value = "2-(1)"
Exit Sub
End If
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
.Visible = True
Else
.LinkedCell = vbNullString
.Visible = False
End If

End With
End Sub

お礼

新しいコードを作っていただきありがとうございました。
正しく作動しました。
ありがとうございました。

回答No.6

imogasi

質問の内容が全貌が見えないが、
特にA2だけを問題にしてよいのか、A3,A4・・はどうなるのか質問に書いてないから不安だが
ーー
質問内容に忠実にやるとVBAが必要になり、コードは下記。
リストはE列にある例で、コードの後半Range("A2").Select
以下はマクロの記録をとって手直しすれば出来上がる。
シートのChangeイベントに入れる。
ーー
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("A2") = ""
If Target = "○" Then
Range("A2").Validation.Delete
Range("A2") = "2-(1)"
Else
Range("A2").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$E$1:$E$7"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End If
End If
End Sub
ーー
これに、効果上で極く近いのが、＃４のご回答と思う。
A1にXを入れないとダメな点が少し違う。
上記例では○以外の場合、A1の値は○以外であればよいので、A1は空白でも、例えばaでも１でもスペースでもよい。

回答No.5

Masa2072

ANo3です。
> 「左のツリー」がわかりません。
Visual Basic Editorを開くと画面が左（1/4）、右（3/4）くらいに分かれていて
左側のほうに
VBAProject （Book1)
　Microsoft Excel Object
　　Sheet1 （Sheet1)
　　Sheet2 （Sheet2)
　　Sheet3 （Sheet3)
　　ThisWorbook

といったリストが無いでしょうか？
なければキーボードでCtrlキーを押しながらRを押してみてください。
（表示メニュー － プロジェクト エクスプローラー　　でもOKです。）

あと、先ほどのコードには一部不具合がありB列を手で編集し、リスト以外の値を入力すると、次回選択時にエラーが発生します。
Worksheet_SelectionChang（・・・・の下に
On Error Resume Nextと追加してください。

補足

とてもわかりやすい説明ありがとうございます。
消えていた状態でした。

申し訳ないのですが、さきほどのA1,A2は
便宜上のもので、
実際には、A1にあたるのは、L54:P54　（セルの結合しています。）
B2は、R53:V53　（結合）です。

もしお時間がおありでしたら、セルの位置の直し方を教えていただきたく思います。

回答No.4

mshr1962

Sheet2
　A　　　　B
1 ×　　 　○　　　
2 1-(1)　2-(1)
3 1-(2)
4 1-(3)
5 1-(4)
6 1-(5)
として「挿入」「名前」「作成」で「上端行」のみチェックでOK

A2の入力規則にて
入力値の種類「リスト」
元の値「=INDIRECT(IF(COUNTIF(A1,"*○*"),"○","×"))」

これでA1に○の付く品名が入ると2-(1)しか選べなくなります。

回答No.3

Masa2072

入力規則やリストですと条件によって、表示・非表示の切り替えができません。
コントロールオブジェクトのリストボックスを使用しVBAを利用することで可能になります。
１．ワークシートにリストボックスを作成します。
　　表示メニュー － ツールボックス － コントロール ツールボックスを表示し
　　ワークシート上にリストボックスを作成
２．ListBox1のプロパティ変更
　　ListFillRangeに入力するデータの一覧があるセル範囲のアドレスを入力する
　仮にSheet1のF1～F5に入っていれば Sheet1!F1:F5
　　VisibleをFalseに変更
３．デザインモードを終了
　　コントロール ツールボックスの「デザインモードの終了」ボタンをクリック（三角定規のマーク）
４．ツールメニュー － マクロ － Visual Basic Editorを開きマクロを記述
　　※以下ブックがBook1.xls シートがSheet1だと仮定
　　左側のツリーよりVBA Project（Book1）の下Microsoft Excel Objectのさらに下にあるSheet1 (Sheet1)を選択し右クリックから「コードの表示」し以下のソースを貼り付けする

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "○" Then
Target.Offset(0, 1).Value = "2-(1)"
End If
ElseIf Target.Column = 2 Then
If Target.Offset(0, -1).Value = "○" And Target.Value <> "2-(1)" Then
Target.Value = "2-(1)"
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.ListBox1

If Target.Column = 2 Then
If Target.Offset(0, -1) = "○" Then
.Visible = False
Target.Value = "2-(1)"
Exit Sub
End If
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
.Visible = True
Else
.LinkedCell = vbNullString
.Visible = False
End If

End With
End Sub

以上で準備が完了です。
動きとしてはB列を選択するとリストが表示、B列以外を選択でリストが非表示になります。
Ａ列に○を入力すると隣のセルに2-(1)を自動で入力
Ｂ列を変更した際に隣のＡ列セルに○が入力されていれば、2-(1)に戻す。

補足

ありがとうございます。
VBEを開くところまではできましたが、
「左のツリー」がわかりません。

回答No.2

mu2011

条件によってリスト選択する方法は駄目でしょうか。
（１）「○」又は「２－（１）」リストの名前定義（仮にリスト1）と他データリストの名前定義（リスト2）を別シートに設定
（２）入力規則で「リスト」選択、元の値欄に=IF($A$1="○",リスト1,リスト2)

補足

ありがとうございます。
だいぶ楽になりました。

高望みなのかもしれませんが、
教えていただいた方法は、２－（１）でも一度リストから
選ばなくてはならないです。
A1に○がある段階で、A2に２－（１）と表示される方法はありませんか？

回答No.1

hallo-2007

例えば
　 A　　　B　　　　　　　　　　　　　　　　　C
1　　　=IF(A$1="○","２－（１）",C1)　　１－（１）
2　　　　　　　　　　　　　　　　　　　　　　　１－（２）
・・・・
とB列に式を入れて下フィル
入力規則のリストの範囲をB列を指定する
とA1に○を入れると、A2のリストは　２－（１）
しか表示されない
A1に○以外だと、C列のリストが表示される
いうトリックではダメでしょうか。