Cancelling Msgbox if string is limited to set characters

[Happy_dancer]

Hi Everyone (first time poster, multiple time forum user),

I have part of a macro that is asking a user for a value and storing this as a String limited to 9 characters (a few reasons why this needs to be limited to 9).

However, if the user selects cancel, [X], or leaves blank the IF conditions to display "You pressed cancel or[X]" and "You did not enter anything" are not being displayed.

This code works if the string is not set to a character limit - anyone know why limiting the string characters is affecting this code and how to get around the issue?

Dim StrInput1 As String * 9
StrInput1 = InputBox("Please Scan or Type BARCODE below", "Barcode Checker")
If (StrPtr(StrInput1) = 0) Then
    MsgBox "You pressed cancel or[X]"
    Exit Sub
    ElseIf (StrInput1 = "") Then
    MsgBox "You did not enter anything"
    Exit Sub
End If

Appreciate the help!

 

[Akuini]

@Happy_dancer, welcome to MrExcel.
Try using Application.InputBox:

Sub test()
Dim x As Variant
x = Application.InputBox("Please Scan or Type BARCODE below", "Barcode Checker")
If x = False Then
    MsgBox "You pressed cancel or[X]"
ElseIf Len(x) = 0 Then
    MsgBox "You did not enter anything"
ElseIf Len(x) > 9 Then
    MsgBox "Too many char"
Else
    'do something here
End If
    
End Sub

 

[Happy_dancer]

Firstly, I appreciate the reply! However, I don't believe I can store the value as variable as I require the limit to 9 characters and wont be able to use Len().

This is because the barcode input maybe over 9 characters (which is expected) but I just want to store the first 9 characters for a further check in the macro. For an example I want to compare A12345678 and A12345678.001 and for the marco to know that they match. The macro works except for when the user tries to exit out of the macro early (selecting cancel or [x], or doesn't enter a value

I posted more of the macro below to show what I am trying to achieve.

Dim StrInput1 As String * 9
StrInput1 = InputBox("Please Scan or Type BARCODE below", "Barcode Checker")
If (StrPtr(StrInput1) = 0) Then
    MsgBox "You pressed cancel or[X]"
    Exit Sub
    ElseIf (StrInput1 = "") Then
    MsgBox "You did not enter anything"
    Exit Sub
End If
'Asks user for input and saved input as StrInput1 which is limited to 9 characters. If messagebox closed, cancelled, or blank then macro will end.

Dim StrInput2 As String * 9
StrInput2 = InputBox("Please Scan or Type BARCODE below", "Barcode Checker", "")
If (StrPtr(StrInput2) = 0) Then
    MsgBox "You pressed cancel or[X]"
    Exit Sub
    ElseIf (StrInput2 = "") Then
    MsgBox "You did not enter anything"
    Exit Sub
End If
'Asks user for input and saved input as StrInput2 which is limited to 9 characters. If messagebox closed, cancelled, or blank then macro will end.


If StrInput1 = StrInput2 Then
'Compares the different user inputed Values
    Beep
    MsgBox "Barcodes match", vbOKOnly
    Dim Match As String
    Match = "Match"
    Else
    
    MsgBox "BARCODES DO NOT MATCH!", vbCritical
    Dim Mismatch As String
    Mismatch = "Mismatch"
End If

The code goes on to audit these inputs in a specified sheet, hence why I am creating a "match" and "mismatch" strings.

 

[Akuini]

This is because the barcode input maybe over 9 characters (which is expected) but I just want to store the first 9 characters for a further check in the macro. For an example I want to compare A12345678 and A12345678.001 and for the marco to know that they match.

Are you saying it's ok to enter more than 9 characters but you need only the first 9 characters to match?

 

[Akuini]

Is it ok to enter less than 9 characters?

 

[Happy_dancer]

It is OK to enter more than 9 characters but NOT less. There wouldn't be a situation where there would be less than 9 characters though as 9 characters is the least amount of characters a barcode could be. I just want to macro to take the first 9 characters of the input even if 9 or more where entered.

I have thought of a work around which works but not elegant, using the Left(string, 9), see below.

Dim StrInput1Left9 As String
StrInput1Left9 = Left(StrInput1, 9)
'Stores the left most 9 characters of StrInput1 as StrInput1Char9

Dim StrInput2Left9 As String
StrInput2Left9 = Left(StrInput2, 9)
'Stores the left most 9 characters of StrInput2 as StrInput2Char9

If StrInput1Left9 = StrInput2Left9 Then
'Compares the different user inputed Strings(after the left most 9 character amendment)
    Beep
    MsgBox "Barcodes match", vbOKOnly
    Dim Match As String
    Match = "Match"
    Else
    
    MsgBox "BARCODES DO NOT MATCH!", vbCritical
    Dim Mismatch As String
    Mismatch = "Mismatch"

Appreciate the help Akuini and if you know of a better solution or foresee an issue with this let me know.

 

[johnnyL]

@Happy_dancer, one issue in regards to post #1 is StrInput1 = ""

StrInput1 = "" can't happen because you have set the length to 9, you would have to check for
StrInput1 = "SpaceSpaceSpaceSpaceSpaceSpaceSpaceSpaceSpace"
which is a string of 9 spaces.

You haven't provided code to explain how the StrPtr is being used for in the code so I can't speak to that part.

In the end, whether you click the 'X', or click the Cancel button, or leave the Barcode entry 'Blank' & click the OK button ... the end result is StrInput1 = "SpaceSpaceSpaceSpaceSpaceSpaceSpaceSpaceSpace"

 

[Akuini]

Maybe this:

Sub test_2()
Dim x As Variant, y As Variant
x = Application.InputBox("Please Scan or Type BARCODE below", "Barcode Checker")
If x = False Then
    MsgBox "You pressed cancel or[X]"
    Exit Sub
ElseIf Len(x) = 0 Then
    MsgBox "You did not enter anything"
    Exit Sub
End If

y = Application.InputBox("Please Scan or Type BARCODE below", "Barcode Checker")
If y = False Then
    MsgBox "You pressed cancel or[X]"
    Exit Sub
ElseIf Len(y) = 0 Then
    MsgBox "You did not enter anything"
    Exit Sub
End If

x = Left(x, 9)
y = Left(y, 9)

If x = y Then 'compare the first 9 characters
'Compares the different user inputed Values
    Beep
    MsgBox "Barcodes match", vbOKOnly
    Dim Match As String
    Match = "Match"
Else
    
    MsgBox "BARCODES DO NOT MATCH!", vbCritical
    Dim Mismatch As String
    Mismatch = "Mismatch"
End If
End Sub

x & y should be variant instead of string because their value is FALSE when you press Cancel or (X) button.