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 CSmith.

It looks so much different from the previous code. So many ways to go about it.

I'm a bit under the weather. Took my boys to bed and dosed off too. Got a bit of a cold to.

I have just tried it and it's coming up with an error.

I do apologize if what I am trying to do hasn't really been explained the best way possible but, I'll give it a go.

1) User calls msgbox (macro) to find proforma in hidden sheets.
2) If user clicks Cancel button, exits sub.
3) If user clicks Ok button but no data has been entered, msgbox to alert user no data has been entered, Retry or Cancel.
4) If Cancel then exists sub.
5) If Retry then show initial msgbox referred in point 1.
6) If Proforma is found in hidden sheets, unhide it and set focus to it.
7) If Proforma is not found then compare the number entered to data in Pro sheets last row (last issued proforma number) and...
8) If Number entered is lower but not found in hidden sheets it's because it no longer exists and has been converted to an invoice. The conversion to invoice DELETES the sheet and in the Invoices sheet there's reference that invoice was a result of "XYZ" Proforma.
9) If number entered is higher than last row column A value then Proforma has not yet been issued, so msgbox to alert user Proforma not yet issued. Again, options to Cancel or Retry.

Maybe not the best of logic I am following as I have not a programmer minded thinking.

This is well and truly appreciated you guys, as nothing I have achieved this far in this little project, has not been possible had it not been for your time, dedication and patience in helping others like me.

So a much big thank you to all.

 

[albertc30]

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

Hi.

Lol... I am ecstatic...

So many different approaches of doing something.

It works mate. One less msgbox as in when user enters no data and it just loops if user keeps clicking ok without entering any data. Nice.

I would NOT ever made this work I don't think.

I made it work to an extent but I am a bit OCD and like the small details.

A big big thank you guys, Peter_SSs, CSmith, everyone really. This is the weekend and there you are giving your time and knowledge in helping others, so thank you.

On another note, and this is now me trying to make it clear what is that the proforma project does.

There's a macro that creates a separate sheet for each proforma issued and names the sheet with the proforma number and hides the created sheet. Some data from proforma is collected onto Proforma sheets, as for example the proforma number.

On these sheets, there's a CommandButton that becomes active and linked to a macro that allows proforma to be issued as the next invoice. When this happens successfully, the sheet is then deleted as it's no longer needed and to try and maintain the size of the excel file small.

So, the find proforma that you have kindly helped out with sort of interacts with the above.

Many many thanks again you guys.

A very good night from the United Kingdom here.

I'm sure I'll be back sooner rather than later.

 

[Peter_SSs]

Lol... I am ecstatic...

It works mate.


Many many thanks again you guys.

Good news! You're welcome.

 

[CSmith]

Opps tru, it was gettin rid of the 'with' statement and missed the new line in the if statement near bottom.

It doesn't compile.

Most welcome! Thanks for letting us know! Sorry for not checking it before I posted I was playing some WC3 Reforged Campaign and didn't double check opps

Lol... I am ecstatic...

So many different approaches of doing something.

It works mate. One less msgbox as in when user enters no data and it just loops if user keeps clicking ok without entering any data. Nice.

Many many thanks again you guys.