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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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;

VBA Code:
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.

VBA Code:
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.
 
Last edited:

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So your code works, what is it you are actually looking for !!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Sorry I did not understand.
Maybe this helps you.

VBA Code:
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

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe this
VBA Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Maybe this line compared to what you had.....
VBA Code:
 If ThisWorkbook.Sheets("Create").Range("F3").Value <> "" Then 'There's a value on this cell so...
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Maybe this:

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

VBA Code:
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

Well-known Member
Joined
May 7, 2012
Messages
1,088
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,765
Members
416,202
Latest member
donya ba

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
Top