Solved: Before Save Prompt Userform

[Emoncada]

I created a uf that I would like to Show if a cell = "" when the user tries to Save the workbook.

Example the cell is B15.

So if the user tries to save the workbook if B15 = "" (Blank) then Userform1 would show.

Now It would have a Yes Or No Question.

Basically letting them know that B15 is empty and if they want to proceed to save click "Yes" (CmdYes) Or "No" (CmdNo) to not save and go back to the workbook.

How can I make this happen. UF is done.

[Bob Phillips]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Sheet1").Range("B15")

If .Value = "" Then

If MsgBox("Cell B15 is empty, save (Yes or No)?", vbYesNo + vbQuestion, "Empty Cell") = vbNo Then

Cancel = True
End If
End If
End With
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[Emoncada]

Can I just replace
If MsgBox("Cell B15 is empty, save (Yes or No)?", vbYesNo + vbQuestion, "Empty Cell") = vbNo Then

With Userform1.show??

[mikerickson]

Perhaps something like
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim uiValue As String
With ThisWorkbook.Sheets("Sheet1").Range("B15")

If .Value = vbNullString Then
uiValue = Application.InputBox("B15 is empty" & vbCr & "What value should be stored in B15?", Type:=2)
If uiValue = "False" Then
Cancel = True
Else
.Value = uiValue
End If
End If

End With
End Sub

[Emoncada]

I prefer the Userform then the regular MsgBox because I make it stand out more.

[Bob Phillips]

Can I just replace
If MsgBox("Cell B15 is empty, save (Yes or No)?", vbYesNo + vbQuestion, "Empty Cell") = vbNo Then

With Userform1.show??

You could, but then you would need to pass back the response and handle that. Why bother when a simple MsgBox does the trick.

I see Mike took a similar approach to me.

[Emoncada]

I prefer the Userform then the regular MsgBox because I make it stand out more.

[mikerickson]

XLD's suggestion of a <DONT SAVE> button on the UF makes a smoother user interface than a MsgBox"Invoke UF? Yes/No" before the UF is called.

[Emoncada]

Well I went ahead and used XLD's script and worked.

Thanks for all the help.