时间戳

首页 > 解决方案 > VBA msgbox 是/否 然后做点什么

excel - VBA msgbox 是/否 然后做点什么

问题描述

这是我尝试使用的两个版本。它们都会在我想要的时候触发，但是当我单击“是”时它们不会更改任何值。它也陷入无限循环，我必须 CTRL+BREAK

If Target.Column = 6 And Len(Target.Value) > 1 Then
       For Each locationIdcell In locationIdRange
        If locationIdcell.Value <> Target.Value Then
      '  Dim answer As Integer
        Dim answer As Long
           answer = MsgBox("This Machine Has an attachment tied to it, Move Attachment with Machine?", vbYesNo)
           If answer = vbYes And locationIdcell.Value <> Target.Value Then
                bucketsAttachWorksheet.Cells(locationIdcell.Row, 6).Value = movesWorksheet.Cells(Target.Row, 9).Value
             Else ' do nothing
               MsgBox ("Attachments will stay in current location")
             End If
        End If
       Next locationIdcell

If Target.Column = 6 And Len(Target.Value) > 1 Then
        For Each locationIdcell In locationIdRange
         If locationIdcell.Value <> Target.Value Then
       '  Dim answer As Integer
        ' Dim answer As Long
            Select Case MsgBox(Prompt:="This Machine Has an attachment tied to it, Move Attachment with Machine?", Buttons:=vbYesNo)
            Case vbYes
                 bucketsAttachWorksheet.Cells(locationIdcell.Row, 6).Value = movesWorksheet.Cells(Target.Row, 9).Value
            Case vbNo ' do nothing
                MsgBox ("Attachments will stay in current location")
            
         End If
        Next locationIdcell
    End If

标签： excelvba

解决方案

  1. 您似乎不必要地检查 locationIdcell.Value <> Target.Value 两次。
  2. 您不需要将 MsgBox 响应读取到的变量，（并且您不应该在循环中间声明变量，而是在过程的顶部）。
  3. 你没有关闭你最初的 If...，我不清楚它的作用。

但是，这是您的第一次通过，如果您能提供进一步的说明，我会很乐意更新它：

If Target.Column = 6 And Len(Target.Value) > 1 Then
  For Each locationIdcell In locationIdRange
    If locationIdcell.Value <> Target.Value Then
      If MsgBox("This Machine Has an attachment tied to it, Move Attachment with Machine?", vbYesNo) = vbYes Then
        bucketsAttachWorksheet.Cells(locationIdcell.Row, 6).Value = movesWorksheet.Cells(Target.Row, 9).Value
      Else ' do nothing
        MsgBox ("Attachments will stay in current location")
      End If
    End If
  Next locationIdcell
End If

推荐阅读