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

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,086
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
In fact, in 2 of the conditions you have the same inputbox, so it could look like this:

VBA Code:
Private Sub CBFindSheet_Click()
  Dim sName As String

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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,086
Office Version
  1. 2019
Platform
  1. Windows
In fact, in 2 of the conditions you have the same inputbox, so it could look like this:

VBA Code:
Private Sub CBFindSheet_Click()
  Dim sName As String

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

It works. Brilliant. Much less code, wow.

Much appreciated mate.

Thanks to all.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,455
Members
414,239
Latest member
xnanx

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