• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

更新第一个单元格后强制用户更新另一个单元格

excel 来源:Oliver Marshall 3次浏览

我有一个电子表格,其中包含销售清单。更新第一个单元格后强制用户更新另一个单元格

当用户将“状态”列设置为“已关闭”时,我想强制他们在“已关闭日期”列中输入日期。

所以我有这个;

A1 (Status), B1 (Closed Date) 
Open, <blank> 
Open, <blank> 
Closed, 1/1/2018 

任何想法?

奥利


===========解决方案如下:

按什么安迪说,SO不是代码写作服务。但考虑到你不知道从哪里开始的想法,并为您提供一个起点,请按照下列步骤……

Right clickSheet Tab – 在下面给出入opened code windowsave您的工作簿作为Macro-Enabled Workbook>View codepaste the code

Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.CountLarge > 1 Then Exit Sub 
If Target.Column = 1 And Target.Row > 1 Then 
    If Target <> "" And LCase(Target.Value) = "closed" Then 
     With Target.Offset(0, 1) 
      .Select 
      On Error Resume Next 
      .Comment.Delete 
      On Error GoTo 0 
      .AddComment.Text "Please enter the Closed Date" 
      .Comment.Visible = True 
     End With 
    Else 
     On Error Resume Next 
     Target.Offset(0, 1).Comment.Delete 
     On Error GoTo 0 
    End If 
ElseIf Target.Column = 2 And Target.Row > 1 Then 
    If LCase(Target.Offset(0, -1)) = "closed" And IsDate(Target) Then 
     Target.Comment.Delete 
    End If 
End If 
End Sub 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
If Target.CountLarge > 1 Then Exit Sub 
Dim x 
Dim i As Long, lr As Long 

Application.ScreenUpdating = False 
Application.EnableEvents = False 

lr = Cells(Rows.Count, 1).End(xlUp).Row 
x = Range("A2:B" & lr) 

If Target <> "" Then 
    Application.EnableEvents = True 
    Exit Sub 
End If 

For i = 1 To UBound(x, 1) 
    If LCase(x(i, 1)) = "closed" And Not IsDate(x(i, 2)) Then 
     Cells(i + 1, 2).Select 
     Exit For 
    End If 
Next i 

Application.EnableEvents = True 
Application.ScreenUpdating = True 
End Sub 

版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)