If cell not empty, msgbox yes no condition...

[albertc30]

Hello everyone.

I have been dabbling in Google for the past 2 hours and am stuck.

This is probably so easy but I'm rubbish at this. I have seen using;

Dim answer As Integer
answer = MsgBox("You have already selected a document type to be generated! Do you wish to continue?", vbYesNo + vbQuestion, "Empty Sheet")
If answer = vbYes Then
    'run required code
Else
    'do nothing
End If

I have a fell this is what I am looking for but I just can't change it to what I'm looking for.

Private Sub CBFindSheet_Click()

       'If IsEmpty(ThisWorkbook.Sheets("Create").Range("F3").Value) = False Then 'There's a value on this cell so...
      
        'MsgBox "You have already selected a document to be created!", vbYesNo, "Document" = vbYes  'Tell user a document type has already been selected.
                                                                                            'If user wants to look for a proforma, then F3 gets cleared by means of
       
        'Range("E3").Select      'Here there's a dropdown list with Invoice and Proforma, depending on what's chosen, the cell F3 gets the next document number.
        'Selection.ClearContents 'Clear the contents then run code bellow to call for proforma
       
       'Else
       'testing above
        'bellow works fine
    Dim sName As String
    Dim sFound As Boolean

        sName = InputBox(prompt:="Enter Proforma number you wish to find.", Title:="Find Proforma...")

    If sName = "" Then Exit Sub
    sFound = False

    On Error Resume Next
        ActiveWorkbook.Sheets(sName).Select
        If Err = 0 Then sFound = True
    On Error GoTo 0

    If sFound = False Then
        MsgBox prompt:="The Proforma number '" & sName & "' could not be found! This could be because it has already been issued as an invoice to the customer, or not yet issued.", Buttons:=vbExclamation, Title:="Search result"
    End If
    'End If
End Sub

Any help is, as always, much appreciated.
Thanks.

 

[Michael M]

So your code works, what is it you are actually looking for !!

 

[DanteAmor]

Sorry I did not understand.
Maybe this helps you.

Private Sub CBFindSheet_Click()
  Dim answer As Variant
  If ThisWorkbook.Sheets("Create").Range("F3").Value <> "" Then 'There's a value on this cell so...
    answer = MsgBox("You have already selected a document to be created!", vbYesNo, "Document")
    If answer = vbYes Then
      'do something
    Else
      'do something else
    End If
  End If
End Sub

 

[albertc30]

So your code works, what is it you are actually looking for !!

Trying to implement what's on green to the existing code.

 

[albertc30]

Hi.

This seems to be working now thanks to DanteAmor's suggestion.

Anything on the code you guys would change and if so maybe why please? Trying to understand these things.

Much appreciated guys.

Private Sub CBFindSheet_Click()

    Dim answer As Variant
    
    If ThisWorkbook.Sheets("Create").Range("F3").Value <> "" Then 'There's a value on this cell so...
    answer = MsgBox("You have already selected a document to be created!", vbYesNo, "Document")
    If answer = vbYes Then
       
       Range("E3").Select
        Selection.ClearContents
        
        Range("F3").Clear
    
        ActiveWorkbook.save
        
    Dim sName As String
    Dim sFound As Boolean

        sName = InputBox(prompt:="Enter Proforma number you wish to find.", Title:="Find Proforma...")

    If sName = "" Then Exit Sub
    sFound = False

    On Error Resume Next
        ActiveWorkbook.Sheets(sName).Select
        If Err = 0 Then sFound = True
    On Error GoTo 0

    If sFound = False Then
        MsgBox prompt:="The Proforma number '" & sName & "' could not be found! This could be because it has already been issued as an invoice to the customer, or not yet issued.", Buttons:=vbExclamation, Title:="Search result"
    End If
    End If
    Else

    sName = InputBox(prompt:="Enter Proforma number you wish to find.", Title:="Find Proforma...")

    If sName = "" Then Exit Sub
    sFound = False

    On Error Resume Next
        ActiveWorkbook.Sheets(sName).Select
        If Err = 0 Then sFound = True
    On Error GoTo 0

    If sFound = False Then
        MsgBox prompt:="The Proforma number '" & sName & "' could not be found! This could be because it has already been issued as an invoice to the customer, or not yet issued.", Buttons:=vbExclamation, Title:="Search result"
    End If
    End If
    Exit Sub
End Sub

 

[Michael M]

Maybe this

Private Sub CBFindSheet_Click()

       If Sheets("Create").Range("F3").Value <> "" Then 'There's a value on this cell so...
       
        MsgBox "You have already selected a document to be created!, Do you wish to continue?", vbYesNo + vbQuestion, "Empty Sheet"
        
        Range("E3").Select      'Here there's a dropdown list with Invoice and Proforma, depending on what's chosen, the cell F3 gets the next document number.
        Selection.ClearContents 'Clear the contents then run code bellow to call for proforma
        
       Else
       'testing above
        'bellow works fine
    End If
    Dim sName As String
    Dim sFound As Boolean

        sName = InputBox(prompt:="Enter Proforma number you wish to find.", Title:="Find Proforma...")

    If sName = "" Then Exit Sub
    sFound = False

    On Error Resume Next
        ActiveWorkbook.Sheets(sName).Select
        If Err = 0 Then sFound = True
    On Error GoTo 0

    If sFound = False Then
        MsgBox prompt:="The Proforma number '" & sName & "' could not be found! This could be because it has already been issued as an invoice to the customer, or not yet issued.", Buttons:=vbExclamation, Title:="Search result"
    End If
    'End If
End Sub

 

[Michael M]

Maybe this line compared to what you had.....

 If ThisWorkbook.Sheets("Create").Range("F3").Value <> "" Then 'There's a value on this cell so...

 

[DanteAmor]

Maybe this:

Some tips:
Declaration of variables at the beginning.
Take care of the indentation of the IF statements

Private Sub CBFindSheet_Click()
  
  Dim answer As Variant
  Dim sName As String

  If ThisWorkbook.Sheets("Create").Range("F3").Value <> "" Then 'There's a value on this cell so...
    answer = MsgBox("You have already selected a document to be created!", vbYesNo, "Document")
    If answer = vbYes Then
      Range("E3").ClearContents
      Range("F3").Clear
      ActiveWorkbook.Save
      sName = InputBox(prompt:="Enter Proforma number you wish to find.", Title:="Find Proforma...")
      If sName = "" Then Exit Sub
      If Evaluate("ISREF('" & sName & "'!A1)") = False Then
        MsgBox prompt:="The Proforma number '" & sName & "' could not be found! This could be because it has already been issued as an invoice to the customer, or not yet issued.", Buttons:=vbExclamation, Title:="Search result"
      Else
        Sheets(sName).Select
      End If
    End If
  Else
    sName = InputBox(prompt:="Enter Proforma number you wish to find.", Title:="Find Proforma...")
    If sName = "" Then Exit Sub
    If Evaluate("ISREF('" & sName & "'!A1)") = False Then
      MsgBox prompt:="The Proforma number '" & sName & "' could not be found! This could be because it has already been issued as an invoice to the customer, or not yet issued.", Buttons:=vbExclamation, Title:="Search result"
    Else
      Sheets(sName).Select
    End If
  End If
End Sub

 

[albertc30]

You guys make it look so nice.

My code is all over the place.

I had 3 variables now there's only 2!

I need to really look into your code and mine and see and try to understand the changes/differences.

Thanks you guys.

 

[DanteAmor]

Private Sub CBFindSheet_Click()
If Sheets("Create").Range("F3").Value <> "" Then 'There's a value on this cell so...
MsgBox "You have already selected a document to be created!, Do you wish to continue?", vbYesNo + vbQuestion, "Empty Sheet"

Hi Michael, the Yes / No buttons appear, but any option you select the code continues with the next line.