输入框执行直到输入两个字母

问题描述

这是我第一次使用InputBox.希望用户将其姓名首字母插入到电子表格中，该电子表格将被导入数据库.我正在使用InputBox来提高一致性并自动填充必要的单元格.

This is my first time working with an InputBox. The desire is to have the user insert their initials for entry into a spreadsheet that will be imported into a database. I'm using the InputBox to promote consistency and AutoFill the necessary cells.

我无法理解用户输入信息的过程，如果输入的内容是接受并填充到单元格中的两个字母，否则会出现一条消息，指示需要两个字母，然后再次显示InputBox.通过测试，我相信我的循环无法正常运行.如果第一个条目是两个字母，则会按预期将信息填充到excel中.但是，如果第一个条目不正确，而随后的一个条目正确，则似乎没有退出循环.我不确定为什么会这样?任何帮助将不胜感激.

I am having trouble understanding a process whereby a user enters information, if the entry is two letters its accepted and populated into the cells, else a message appears indicating two letters are required and the InputBox displays again. Through testing I believe my loop is not working as I would expect. If the first entry is two letters it populates information into excel as expected. If, however, the first entry is incorrect and a subsequent entry is correct it does not seem to exit the loop. I'm not sure why this would be? Any help would be greatly appreciated.

Dim c As Range

Set c = Sheets("CompilePriceAdjustments").Range("E2")

    c = InputBox("Please Enter Initials", "PRICE INCREASE APPROVER")
Do Until c = vbString And Len(c) = 2
    MsgBox ("You must enter two letters")
    c = InputBox("Please Enter Initials", "PRICE INCREASE APPROVER")
Loop

Sheets("CompilePriceAdjustments").Range("E2").Value = UCase(c)
c.AutoFill Destination:=Sheets("CompilePriceAdjustments").Range("E2:E" & Cells    (Rows.Count, "D").End(xlUp).Row)

推荐答案

我认为这是您要尝试的?

I think this is what you are trying?

Sub Sample()
    Dim c As Range
    Dim Ret

    Set c = Sheets("CompilePriceAdjustments").Range("E2")

    Ret = InputBox("Please Enter Initials - (Only alphabets allowed of 2 Length)", "PRICE INCREASE APPROVER")

    Do Until (isString(Ret) And Len(Ret) = 2)
        Ret = InputBox("Please Enter Initials - (Only alphabets allowed of 2 Length)", "PRICE INCREASE APPROVER")
    Loop

    c.Value = UCase(Ret)
    '
    '~~> Rest of the code
    '
End Sub

Function isString(s As Variant) As Boolean
    Dim i As Long

    isString = True

    For i = 1 To Len(s)
        Select Case Asc(Mid(s, i, 1))
        Case 65 To 90, 97 To 122
        Case Else
            isString = False
            Exit Function
        End Select
    Next i
End Function

编辑

我发现您的方法存在一个缺陷.如果用户要取消并退出该怎么办?您可能要考虑此代码?

I see one flaw in your approach. What if the user wants to cancel and exit? You might want to consider this code?

Sub Sample()
    Dim c As Range
    Dim Ret

    Set c = Sheets("CompilePriceAdjustments").Range("E2")

    Ret = InputBox("Please Enter Initials-(Only alphabets allowed of 2 Length)", _
          "PRICE INCREASE APPROVER")

    '~~> Added  Or Ret = "" so that user can cancel the inputbox if required
    Do Until (isString(Ret) And Len(Ret) = 2) Or Ret = ""
        Ret = InputBox("Please Enter Initials-(Only alphabets allowed of 2 Length)", _
        "PRICE INCREASE APPROVER")
    Loop

    '~~> This is required so that user can press cancel and exit
    If Ret = "" Then Exit Sub

    c.Value = UCase(Ret)
    '
    '~~> Rest of the code
    '
End Sub

Function isString(s As Variant) As Boolean
    Dim i As Long

    isString = True

    For i = 1 To Len(s)
        Select Case Asc(Mid(s, i, 1))
        Case 65 To 90, 97 To 122
        Case Else
            isString = False
            Exit Function
        End Select
    Next i
End Function

这篇关于输入框执行直到输入两个字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！