Input box problems

E

Eric Carolus

Board Regular
Joined
Sep 17, 2012
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
Hi folks

I have searched extensively on Google for solution to my problem, to no avail.

I am testing the OK and Cancel buttons of my Input box (when pressing a button on a worksheet.
The correct responses (message boxes) occur when:
a). I choose a name/school class outside V18:ax18.
b). when I press OK without entering a name/class,
c). When I enter a class in the range mentioned above.

My problem is:
When I press Cancel, I get the same response as in a). above.
Can someone help please? Any and all help will be appreciated.
The code is as follows:


VBA Code: 
Private Sub CommandButton5_Click()


        Dim myRange As Variant
        Dim foundIt As Range

        Dim seekclass As Range

    Set seekclass = Worksheets("Sheet1").Range("V18:AX18")

    Dim class_per_to_count As String
    
    'Indicate the class whose periods you wish to find
        Application.DisplayAlerts = False
        class_per_to_count = UCase(Application.InputBox("Please indicate which class's periods do you wish to find." _
           & vbNewLine _
           & vbNewLine _
           & "                                               Thank you" _
           & vbNewLine _
           & vbNewLine, "                     Number periods of a class", Type:=2))
 Application.DisplayAlerts = True

'Where to look for the class
Set foundIt = seekclass.Find(What:=class_per_to_count, LookIn:=xlValues, LookAt:=xlWhole)

'Determine if the class is in the range of classes, here  Worksheets("Sheet1").Range("V18:AX18")
   Dim foundcell As Variant
   Set foundcell = seekclass.Find(class_per_to_count)
    If foundcell Is Nothing Then
        MsgBox "Sorry, the class that you entered," _
            & vbNewLine _
            & vbNewLine _
            & "is not in the list of classes!" _
            & vbNewLine _
            & vbNewLine _
            & ".........................................................." _
            & vbNewLine _
            & vbNewLine _
            & "Thanks", vbInformation, "Nothing found"
        Exit Sub
    End If

Exit Sub
    
    If StrPtr(class_per_to_count) = 0 Then
                MsgBox "[Cancel] or [X] clicked"

    
    ElseIf Len(class_per_to_count) = 0 Then

                MsgBox "You clicked 'OK' but DID NOT ENTER a class." _
                & vbCrLf _
                & vbCrLf _
                & "....................................................................." _
                & vbCrLf _
                & vbCrLf _
                & "                             Thank you" _
                , vbInformation, "                         NO class entered!"
  
    ElseIf Len(class_per_to_count) = 0 Then
                MsgBox "EYou did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
                Exit Sub
    
    ElseIf Len(class_per_to_count) = 2 And Not class_per_to_count Like "#[A-Za-z]" Then
                MsgBox "2You did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
                Exit Sub
            
    ElseIf Len(class_per_to_count) = 3 And Not class_per_to_count Like "##[A-Za-z]" Then
                MsgBox "3You did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
     
     ElseIf Len(class_per_to_count) > 3 Then
                MsgBox ">You did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
                Exit Sub
                
    Else

        If Not foundIt Is Nothing Then

            If Len(foundIt.Address) = 5 And foundIt.Offset(8, 0).MergeCells = True Then
                If Len(foundIt.Address) = 5 And foundIt.MergeCells = True Then
                    If foundIt.Offset(8, 0).MergeCells = True Then
                                  
                        'The class has zero (0) periods
                        If foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = 0 Or foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = "" Then
                                        MsgBox foundIt & " has 0 " & " periods" _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "..........................................." _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "         Thank you.", vbInformation, "       XNumber of periods per class"

                        Else
                                        MsgBox foundIt & " has " & foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value & " periods" _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "..........................................." _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "         Thank you.", vbInformation, "       Number of periods per class"
                        End If
                    End If
                End If
                            
                'The length of the class e.g.11A is like $11$A, i.e is 5 (five)
        ElseIf Len(foundIt.Address) = 6 And foundIt.Offset(8, 0).MergeCells = True Then
            If Len(foundIt.Address) = 6 And foundIt.MergeCells = True Then
                 
                If foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = 0 Or foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = "" Then
                     MsgBox foundIt & " has 0 " & " periods" _
                     & vbCrLf _
                     & vbCrLf _
                     & "..........................................." _
                     & vbCrLf _
                     & vbCrLf _
                     & "         Thank you.", vbInformation, "       XNumber of periods per class"
                Else
                    
                    MsgBox foundIt & " has " & foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value & " periods" _
                    & vbCrLf _
                    & vbCrLf _
                    & "..........................................." _
                    & vbCrLf _
                    & vbCrLf _
                    & "        Thank you.", vbInformation, "       Number of periods per class"
                End If
            End If
        End If
    Else
        MsgBox "You simply cancelled the process!" _
        & vbCrLf _
        & vbCrLf _
        & "......................................................." _
        & vbCrLf _
        & vbCrLf _
        & "                   Thank you" _
        , vbInformation, "                     Process cancelled!"
    End If
                End If
End Sub

Thank you so much.
Crow
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi,
you need to include a line in your code to test for cancel button press

Rich (BB code): 
Dim class_per_to_count As Variant
  
    'Indicate the class whose periods you wish to find
        Application.DisplayAlerts = True
        class_per_to_count = UCase(Application.InputBox("Please indicate which class's periods do you wish to find." _
           & vbNewLine _
           & vbNewLine _
           & "                                               Thank you" _
           & vbNewLine _
           & vbNewLine, "                     Number periods of a class", Type:=2))
         
        'cancel pressed
        If class_per_to_count = False Then Exit Sub

'rest of code

note change of variable class_per_to_count data type to Variant to handle boolean values
Dave
 
Upvote 0
The most reliable way is to take UCase out of the InputBox line, and use it later.

VBA Code: 
class_per_to_count = Application.InputBox("Long prompt", Type:=2)
If class_per_to_count = False Then Exit Sub
class_per_to_count = UCase$(class_per_to_count)
' rest of code
 
Upvote 0
Hi dmt32 and Jon Peltier

Thanks for your for help.

I have tweaked the code a bit and its now doing what it is suppose to do!.

I place the revised code.

VBA Code: 
Private Sub CommandButton5_Click()
        
        Dim myRange As Variant
        Dim foundIt As Range

        Dim seekclass As Range

    Set seekclass = Worksheets("Sheet1").Range("V18:AX18")

'    Dim class_per_to_count As String
    Dim class_per_to_count As Variant
    
    'Indicate the class whose periods you wish to find
        Application.DisplayAlerts = False
        class_per_to_count = UCase(Application.InputBox("Please indicate which class's periods do you wish to find." _
           & vbNewLine _
           & vbNewLine _
           & "                                               Thank you" _
           & vbNewLine _
           & vbNewLine, "                     Number periods of a class", Type:=2))
 Application.DisplayAlerts = True

'Where to look for the class
Set foundIt = seekclass.Find(What:=class_per_to_count, LookIn:=xlValues, LookAt:=xlWhole)


    If class_per_to_count = False Then
    MsgBox "You clicked [Cancel] or [X] clicked!", vbInformation, "                            Process Cancelled"
'     Exit Sub
    
    ElseIf StrPtr(class_per_to_count) = 0 Then
                MsgBox "You clicked [Cancel] or [X] clicked", vbInformation, "                            Process Cancelled"

    
    ElseIf Len(class_per_to_count) = 0 Then

                MsgBox "You clicked 'OK' but DID NOT ENTER a class." _
                & vbCrLf _
                & vbCrLf _
                & "....................................................................." _
                & vbCrLf _
                & vbCrLf _
                & "                             Thank you" _
                , vbInformation, "                         NO class entered!"
  
    ElseIf Len(class_per_to_count) = 0 Then
                MsgBox "EYou did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
                Exit Sub
    
    ElseIf Len(class_per_to_count) = 2 And Not class_per_to_count Like "#[A-Za-z]" Then
                MsgBox "2You did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
                Exit Sub
            
    ElseIf Len(class_per_to_count) = 3 And Not class_per_to_count Like "##[A-Za-z]" Then
                MsgBox "3You did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
     
     ElseIf Len(class_per_to_count) > 3 Then
                MsgBox ">You did not enter a valid class." _
                & vbCrLf _
                & vbCrLf _
                & "..........................................." _
                & vbCrLf _
                & vbCrLf _
                & "         Thank you.", vbInformation, "                    No valid class entered."
                Exit Sub
                
    Else

        If Not foundIt Is Nothing Then

            If Len(foundIt.Address) = 5 And foundIt.Offset(8, 0).MergeCells = True Then
                If Len(foundIt.Address) = 5 And foundIt.MergeCells = True Then
                    If foundIt.Offset(8, 0).MergeCells = True Then

                        'The class has zero (0) periods
                        If foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = 0 Or foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = "" Then
                                        MsgBox foundIt & " has 0 " & " periods" _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "..........................................." _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "         Thank you.", vbInformation, "       XNumber of periods per class"

                        Else
                                        MsgBox foundIt & " has " & foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value & " periods" _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "..........................................." _
                                        & vbCrLf _
                                        & vbCrLf _
                                        & "         Thank you.", vbInformation, "       Number of periods per class"
                        End If
                    End If
                End If

                'The length of the class e.g.11A is like $11$A, i.e is 6 (five)
        ElseIf Len(foundIt.Address) = 6 And foundIt.Offset(8, 0).MergeCells = True Then
            If Len(foundIt.Address) = 6 And foundIt.MergeCells = True Then

                If foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = 0 Or foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value = "" Then
                     MsgBox foundIt & " has 0 " & " periods" _
                     & vbCrLf _
                     & vbCrLf _
                     & "..........................................." _
                     & vbCrLf _
                     & vbCrLf _
                     & "         Thank you.", vbInformation, "       XNumber of periods per class"
                Else

                    MsgBox foundIt & " has " & foundIt.MergeArea.Offset(8, 0).Cells(1, 1).Value & " periods" _
                    & vbCrLf _
                    & vbCrLf _
                    & "..........................................." _
                    & vbCrLf _
                    & vbCrLf _
                    & "        Thank you.", vbInformation, "       Number of periods per class"
                End If
            End If
        End If
    Else

        MsgBox "Sorry, the class that you entered," _
            & vbNewLine _
            & vbNewLine _
            & "is not in the list of classes!" _
            & vbNewLine _
            & vbNewLine _
            & ".........................................................." _
            & vbNewLine _
            & vbNewLine _
            & "Thanks", vbInformation, "Nothing found"
        Exit Sub
    End If
                End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Check two different statements on the Exit Routine
Replies
3
Views
227
Micron
M
I
  • Solved
Copy and Paste from One workbook to another
Replies
3
Views
450
ipon70
I
Z
  • Solved
Excel VBA: Using OR with <>
Replies
3
Views
107
rlv01
R
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
338
sspatriots
S
P
  • Solved
VBYes/No Responses
Replies
5
Views
345
Pacman52
P

Forum statistics

Threads
1,215,022
Messages
6,122,716
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top