Getting MSG even though I have click on the Cancel button...

[albertc30]

Hi all.

I hope everyone is having a good weekend.

As for me, well here I am again.

I have found code and it was doing what I wanted to do but, I wanted to add a user msg to say user hasn't entered any data if this was the case, rather than just closing or stopping the code at that.

I managed to add the msgbox I wanted but I am now struggling with where to position my IF statments and/or Else if one is needed.

Where am I going wrong here please?

Private Sub CBUnHideSpecific_Click()

    Dim sName As String, sht As Worksheet

    sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")

    If sName = "" Then
    
        MsgBox "You have not entered any data!", vbInformation 'Getting this msg even IF I click on Cancel on the inputBox.
    
    Exit Sub 'Works and exists here when clicking the Okay button if no data has been entered.
    'Do I need another line of code?
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' does not exist or it has been invoiced!", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
End Sub

As per always, any help and advice is very much welcome.

Thanks everyone.

 

[albertc30]

Hi all.

Hope all had a good weekend and a great start of yet another week.

I have been going around countless pages and google searches and this is so far what I have managed to come up with.

Private Sub CBFindIfExists_Click()

        'Find and make it visible if it exists...
    
    Dim sName As String, sht As Worksheet
    Dim Rng As Range
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'On user clicking Cancel it should just close msgbox and exit but
                                                                                                                'it's returning the msgbox bellow.
        If sName = "" Then 'If no data has been entered user gets retry or cancel msg...
            MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user but retry click not working...
        End If
        
            'Retry runs inputbox again
            'Cancel exists sub
    
    If Trim(sName) <> "" Then
        With Sheets("Pro").Range("A:A")
            Set Rng = .Find(What:=sName, _
                        After:=.Cells(.Cells.count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            If Not Rng Is Nothing Then
                

    If sName = "" Then 'If no data has been entered user gets retry or cancel msg...
    
        MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user...
        
        'Retry runs inputbox again
        'Cancel exists sub
        
        Exit Sub
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'Maybe some sort of loop function?
        
        Exit Sub
    
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' no longer exists!" & vbNewLine & "It has already been invoiced.", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
            Else
                MsgBox "Proforma number typed has not yet been issued." & vbNewLine & "Please try another number!", vbInformation, "Ops... Proforma not found." 'value not found
            End If
        End With
    End If

End Sub

Needs a few more tweaks that I am currently struggling with.

For example, on a vbRetryCancel option if user clicks Retry then msgbox input dialog should appear again, but it's not. I know there's a line of code but I have tried such like IfSname="vbCancel then exit sub. The retry I was thinking about another line but guts are telling me this should loop back to run code again. How? lol...

As ever and always, any help or push in the right direction is always trully appreciated.

Thank you.

 

[albertc30]

An updated version.

Private Sub CBFindIfExists_Click()

        'Find and make it visible if it exists...
    
    Dim sName As String, sht As Worksheet
    Dim Rng As Range
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
                                                                                                            
        If sName = "" Then 'If no data has been entered user gets retry or cancel msg...
            
            Exit Sub 'It's exiting on click cancel
            
            Else
            
            If sName = "<>" Then 'Still exiting on click Okay with user having entered no value. msgBox bellow should run.
            
            MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user but retry click not working...
        End If
        End If
    
    If Trim(sName) <> "" Then
        With Sheets("Pro").Range("A:A")
            Set Rng = .Find(What:=sName, _
                        After:=.Cells(.Cells.count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            If Not Rng Is Nothing Then
            
    If sName = "" Then 'If no data has been entered user gets retry or cancel msg...
    
        MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user...
        
        Exit Sub
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'Maybe some sort of loop function?
        
        Exit Sub
    
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' no longer exists!" & vbNewLine & "It has already been invoiced.", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
            Else
                MsgBox "Proforma number typed has not yet been issued." & vbNewLine & "Please try another number!", vbInformation, "Ops... Proforma not found." 'value not found
            End If
        End With
    End If

End Sub

A few more things work now but still some issues. Like for example, code now stops running when user clicks Ok on input with no inputed value and it just exits without telling user no data has been entered, retry or cancel.

 

[albertc30]

Hi to all.

What a week this has been.

Haven't given this much thought but I'm back.

I have changed a few things and it does what's required but not without some issues. OCD for me... lol...

On the InputBox click Cancel exists, so that's fine. But if user clicks on Ok with no data being entered it's exit as well. I'm trying this to return a error message prompting user that no data has been entered with options Ok or Retry. Ok exists sub, and retry runs the initial InputBox again.

I've seen countless online examples but I'm now inclined to thing in order to do this I must move away from using sName and just use MsgBox? Or I could be wrong!

Private Sub CBFindIfExists_Click()

    Dim sName As String, sht As Worksheet
    Dim Rng As Range
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
                                                                                                            
        If sName = "" Then  'No value entered clicking either Ok or Cancel has the same effect!
                            
                            'Any change to propmpt user that no data has been entered if clicking Ok with no data? And prompt with Cancel/Retry options
                            
                            'If sName = vbCancel then (doesn't work)
                            'Exit Sub (doesn't work)
                            
                            'Else (Run again the InputBox how? Loop?)
        
            Exit Sub
            
            Else
    
    If Trim(sName) <> "" Then
        With Sheets("Pro").Range("A:A")
            Set Rng = .Find(What:=sName, _
                        After:=.Cells(.Cells.count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            If Not Rng Is Nothing Then
            
    If sName = "" Then 'If no data has been entered user gets retry or cancel msg...
    
        MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user...
        
        Exit Sub
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'Maybe some sort of loop function?
        
        Exit Sub
    
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' no longer exists!" & vbNewLine & "It has already been invoiced.", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
            Else
                MsgBox "Proforma number typed has not yet been issued." & vbNewLine & "Please try another number!", vbInformation, "Ops... Proforma not found." 'value not found
            End If
        End With
    End If
    End If
End Sub

As always any help is truly appreciated.

Cheers.

 

[Peter_SSs]

You appear to have forgotten post #7 ?

If you want to test if the Cancel button (or the 'X' at the top right of the input box) has been clicked, or no text has been entered or some text was entered, then I suggest that you use Application.InputBox and a test like this.

 

[albertc30]

You appear to have forgotten post #7 ?

Oh shoot.
I might have done.
Thanks.
I'll give it a go.
Sorry.

 

[albertc30]

Hello all.

Hi Peter_SSs. I have adhered you're aproach and have made a tad bit of progress.

I'm now stuck on the additional code I have put as a Ok or Retry if user has entered no data. I just can't seem to figure it out how to check if user clicked vbRetry and run again the initial box.

I also know this is likely to be so easy that I'll be kicking myself around the house.

Private Sub CBFindIfExists_Click()

    Dim sName As Variant, sht As Worksheet
    Dim Rng As Range

  sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
  Select Case sName
    Case False
      Exit Sub
    Case ""
      MsgBox "No text was entered", vbRetryCancel, "Ops..." 'Now strugling with the retry button to run code again! How is this achieved?
      
      'If MsgBox = vbRetry Then 'Nop!!! not working, lol...
      'sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
      
    Case Else
            
      If Trim(sName) <> "" Then
        With Sheets("Pro").Range("A:A")
            Set Rng = .Find(What:=sName, _
                        After:=.Cells(.Cells.count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            If Not Rng Is Nothing Then
            
    If sName = "" Then
    
        MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user...
        
        Exit Sub
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'Maybe some sort of loop function?
        
        Exit Sub
    
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' no longer exists!" & vbNewLine & "It has already been invoiced.", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
            Else
                MsgBox "Proforma number typed has not yet been issued." & vbNewLine & "Please try another number!", vbInformation, "Ops... Proforma not found." 'value not found
            End If
        End With
    End If
  End Select
    
End Sub

Many thanks as per always.

 

[CSmith]

Result = MsgBox "No text was entered", vbRetryCancel, "Ops..."

Then compare this with the constants...

Button Clicked	Constant	Value
Ok	vbOk	1
Cancel	vbCancel	2
Abort	vbAbort	3
Retry	vbRetry	4
Ignore	vbIgnore	5
Yes	vbYes	6
No	vbNo	7

MsgBox "No text was entered", vbRetryCancel, "Ops..." 'Now strugling with the retry button to run code again! How is this achieved?

 

[albertc30]

Hi CSmith.

It's a good attempt but I'm defeated...

Everything I tried keeps coming to a dead end.

Private Sub CBFindIfExists_Click()

    Dim sName As Variant, sht As Worksheet
    Dim Rng As Range

  sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
  Select Case sName
    Case False
      Exit Sub
    Case ""
      
      MsgBox "No text was entered", vbRetryCancel, "Ops..." 'Now strugling with the retry button to run code again! How is this achieved?
      
      If vbRetry = MsgBox("hello", vbRetryCancel, "status") Then
      
      sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
      
    Case Else 'Outside case error!!!! This is just so not for me... :(. almost a week trying this...
            
      If Trim(sName) <> "" Then
        With Sheets("Pro").Range("A:A")
            Set Rng = .Find(What:=sName, _
                        After:=.Cells(.Cells.count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            If Not Rng Is Nothing Then
            
    If sName = "" Then
    
        MsgBox "You have not entered any data!", vbRetryCancel 'Try or cancel options given to user...
        
        Exit Sub
        
        sName = InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...") 'Maybe some sort of loop function?
        
        Exit Sub
    
    Else

    On Error Resume Next
    Set sht = ActiveWorkbook.Sheets(sName)
    On Error GoTo 0

    If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' no longer exists!" & vbNewLine & "It has already been invoiced.", _
            Buttons:=vbExclamation, Title:="Search result..."
    Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
    End If
    End If
            Else
                MsgBox "Proforma number typed has not yet been issued." & vbNewLine & "Please try another number!", vbInformation, "Ops... Proforma not found." 'value not found
            End If
        End With
    End If
  End Select
    
End Sub

The above was my approach but now it returns error Case else outside select case...

Thanks for putting me in the right direction even though I'm just like a horse with blinders... lol...

Have a good weekend everyone.

 

[CSmith]

How does this look?

Hi CSmith.

It's a good attempt but I'm defeated...

Everything I tried keeps coming to a dead end.

The above was my approach but now it returns error Case else outside select case...

Thanks for putting me in the right direction even though I'm just like a horse with blinders... lol...

Have a good weekend everyone.

Private Sub CBFindIfExists_Click()

  Dim sName As Variant, sht As Worksheet
  Dim Rng As Range, exitFlag As Boolean
 
  Set sht = Nothing  ' For illustrative purposes
  Set Rng = Nothing  ' For illustrative purposes
  sName = ""  ' For illustrative purposes
  exitFlag = False
 
  Do Until exitFlag = True
    DoEvents
    sName = Application.InputBox(prompt:="Enter Proforma number you want to search for:", Title:="Proforma search...")
    Select Case sName
      Case ""
        If Not (vbRetry = MsgBox("No text was entered", vbRetryCancel, "Ops...")) Then 'Now strugling with the retry button to run code again! How is this achieved?
          exitFlag = True
          Exit Sub
        Else
          exitFlag = False
        End If
      Case False
        Exit Sub
      Case Else 'Outside case error!!!! This is just so not for me... :(. almost a week trying this...
        If Trim(sName) <> "" Then
          Set Rng = Sheets("Pro").Range("A:A").Find(What:=sName, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        Else
          If Not (vbRetry = MsgBox("You have entered invalid data!", vbRetryCancel, "Ops...")) Then 'Try or cancel options given to user...
            exitFlag = True
            Exit Sub
          Else
            exitFlag = False
          End If
        End If
    End Select
    If Not Rng Is Nothing Then exitFlag = True 'Found Proforma
    Set Rng = Nothing
  Loop
 
  'Either vaild data or sub will have been exited before this point.
    'Not sure exactly what you're doing but I think this should work for your logic.
   
  On Error Resume Next
  Set sht = ActiveWorkbook.Sheets(sName)
  On Error GoTo 0

  If sht Is Nothing Then
    MsgBox prompt:="The Proforma '" & sName & _
      "' no longer exists!" & vbNewLine & "It has already been invoiced.", _
      Buttons:=vbExclamation, Title:="Search result..."
  Else
    If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
      sht.Select
    End If
  End If
  Set sht = Nothing
End Sub

 

[Peter_SSs]

How does this look?

It doesn't compile.

@albertc30
See if this is any use. It builds the "Oops" message into the main InputBox text so that the user doesn't have to click so many buttons if a null string (or string of spaces) is entered.
I also note that your code was checking for leading/trailing spaces but it still used the original sName in some cases. For example, if " abc" was entered your code was still looking for " abc" in column A of 'Pro' whereas I think you mean to look for "abc"?

Anyway, give this a go. If it is not what you want, can you explain in words more clearly what the code is trying to do once a 'valid' entry is made in the Inputbox?

Private Sub CBFindIfExists_Click()
  Dim sName As Variant
  Dim sht As Worksheet
  Dim Rng As Range
  
  Do
    sName = Application.InputBox(prompt:=IIf(sName <> False And Len(Trim(sName) = 0), "Ooops, that wasn't a valid entry" & vbLf & vbLf, "") & _
            "Enter Proforma number you want to search for:", Title:="Proforma search...")
  Loop While sName <> False And Len(Trim(sName)) = 0
  
  If Not sName = False Or sName = "0" Then
    sName = Trim(sName)
    With Sheets("Pro").Columns("A")
      Set Rng = .Find(What:=sName, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False)
    End With
    If Rng Is Nothing Then
      MsgBox "Proforma number typed has not yet been issued." & vbNewLine & "Please try another number!", vbInformation, "Ops... Proforma not found."
    Else
      On Error Resume Next
      Set sht = ActiveWorkbook.Sheets(sName)
      On Error GoTo 0
      If sht Is Nothing Then
        MsgBox prompt:="The Proforma '" & sName & _
            "' no longer exists!" & vbNewLine & "It has already been invoiced.", _
            Buttons:=vbExclamation, Title:="Search result..."
      Else
        If sht.Visible = xlSheetHidden Then sht.Visible = xlSheetVisible
        sht.Select
      End If
    End If
  End If
End Sub