excel - msgbox 要求用户以特定格式输入

Question

我正在尝试编写一个快速的小宏，要求用户输入，然后将其复制到特定单元格(Sheet1 中的 B14)。这是我到目前为止所得到的:

Option Explicit    
Sub updatesheet()

Dim vReply As String     
vReply = InputBox("Enter period (format: Q4 2010) to update, or hit enter to escape")
If vReply = vbNullString Then Exit Sub

Sheets("Sheet1").Activate
ActiveSheet.Range("B14").Value = vReply    
End Sub

我还想知道是否有某种方法可以包含检查以确保用户输入的格式正确，如果不是，则标记错误并要求用户重新输入？

非常感谢帮助:)

Answer 1

我对之前的两个答案都感到困难。

我同意验证是必不可少的；如果用户没有认真考虑提示，他们可能会输入“2011-4”。检查其格式是否为“Q# ####”绝对是朝着正确方向迈出的一步。但是:

我会指出这种级别的检查是不够的。例如，“Q5 1234”将匹配此格式。 “Q5 1234”表明用户试图破坏系统，但“Q4 2101”是一个很容易犯的错误。

Like 运算符是 Excel 2003 中唯一的选择，但对于更高版本，我建议考虑正则表达式。我一直在 VB 2010 中尝试它们。我不否认它们很难理解，但它们为您做了很多事情。也许重武器目前有足够的学习内容，但我仍然建议查看一些最近有关其使用的问题。

正如前面的答案中所使用的，InputBox 没有实现重武器的目标。如果我输入“Q4 2101”而不是“Q4 2011”，并且宏被增强以检查不可能的日期，我将不知道我的简单错误，除非错误消息包含我输入的值。另外，我无法将“Q4 2101”编辑为我想要输入的值。 InputBox 的语法为 vReply = InputBox(Prompt, Title, Default, ...)。因此，如果我要推荐使用 Like 运算符，我会建议:

Sub updatesheet()

  Dim vReply As String
  Dim Prompt As String
  Dim Title As String
  Dim UpdateQuarter As Integer
  Dim UpdateYear As Integer

  ' I have found users respond better to something like "Qn ccyy" 
  Prompt = "Enter period (format: Qn ccyy) to update, or hit enter to escape"
  ' I find a title that gives context can be helpful.
  Title = "Update sheet"

  vReply = InputBox(Prompt, Title)

  Do While True
    ' I have had too many users add a space at the end of beginning of a string
    ' or an extra space in the middle not to fix these errors for them.
    ' Particularly as spotting extra spaces can be very difficult. 
    vReply = UCase(Trim(VReply))
    vReply = Replace(vReply, "  ", " ") ' Does not cater for three spaces 
    If Len(vReply) = 0 Then Exit Sub
    If vReply Like "Q# ####" Then
      ' I assume your macro will need these value so get them now
      ' so you can check them.
      UpdateQuarter = Mid(vReply, 2, 1)
      UpdateYear = Mid(vReply, 4)
      ' The check here is still not as full as I would include in a macro
      ' released for general use.  I assume "Q4-2011" is not valid because
      ' the quarter is not finished yet.  Is "Q3-2011" available yet?  I
      ' would use today's date to calculate the latest possible quarter.
      ' I know "You cannot make software foolproof because fools are so
      ' ingenious" but I have learnt the hard way that you must try.
      If UpdateQuarter >= 1 And UpdateQuarter <= 4 And _
         UpdateYear >= 2009 And UpdateYear <= 2012 Then
        Exit Do
      Else
        ' Use MsgBox to output error message or include it in Prompt
      End If
    Else
      ' Use MsgBox to output error message or include it in Prompt
    End If
    vReply = InputBox(Prompt, Title, vReply)
  Loop

End Sub

最后，我很少使用 InputBox，因为表单一旦掌握，就非常容易创建并提供更多控制。