- 帖子
- 4901
- 主題
- 44
- 精華
- 24
- 積分
- 4916
- 點名
- 249
- 作業系統
- Windows 7
- 軟體版本
- Office 20xx
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台北
- 註冊時間
- 2010-4-30
- 最後登錄
- 2024-11-17
              
|
8#
發表於 2014-7-9 10:46
| 只看該作者
回復 7# bridetobe
要更改已經輸入內容就必須重新寫入- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim A As Range
- If Target.Column <> 2 Then Exit Sub
- With Sheet3
- ThisWorkbook.Names.Add "清單", "=OFFSET(" & .Name & "!$A$1,,,COUNTA(" & .Name & "!$A:$A),)"
- With Range("B:B").Validation
- .Delete
- .Add xlValidateList, , , "=清單"
- End With
- Select Case Target.Value
- Case "新增"
- newitem = InputBox("輸入新增項目")
- If Application.CountIf([清單], newitem) = 0 Then
- Set A = .Columns("A:A").Find("新增", lookat:=xlWhole)
- A.Insert xlShiftDown
- A.Offset(-1) = newitem
- Target = newitem
- Else
- MsgBox "項目已存在清單內"
- End If
- Case "刪除"
- delitem = InputBox("輸入刪除項目")
- Set A = .Columns("A:A").Find(delitem, lookat:=xlWhole)
- If A Is Nothing Then
- MsgBox delitem & "不存在清單內"
- Else
- A.Delete xlShiftUp
- End If
- Case "修改"
- chitem = InputBox("輸入修改項目")
- Set A = .Columns("A:A").Find(chitem, lookat:=xlWhole)
- If A Is Nothing Then
- MsgBox chitem & "不存在清單內"
- Else
- A.Value = InputBox("輸入更正項目", , chitem)
- Target = A
- Range("B:B").Replace chitem, A 'B欄所有已經輸入的資料一起替換
- End If
- End Select
- End With
- With Range("B:B").Validation
- .Modify xlValidateList, , , "=清單"
- End With
- End Sub
|
|
發表於 2014-7-8 14:14
| 
