Input box problems

E

Eric Carolus

Board Regular
Joined
Sep 17, 2012
Messages
133
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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

I
  • Question Question
Apply delay before running code
Replies
8
Views
242
ipbr21054
I
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
424
NoSparks
N
H
  • Question Question
Advanced Multi VBA - Creating multiple temporary files and attach them to one email on outlook
Replies
2
Views
242
Hayf13
H
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
281
Fluff
Fluff
R
  • Question Question
VBA Code Works in Visual Basic Coder, but not with Command Button in Excel
Replies
3
Views
230
rheintz
R

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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