Hi,

I would like for my work sheet to state that information has not been
entered when i save.

Titles are
A1 = name A2 = number A3 = Address

and the data is in the cell below i.e B1 = John .....etc

I would like a box stating if information has not been filled in the cell
when i save.
There could be 1 or many bits of information missing and i would like a list
showing them all (just the missing parts).

Then if possible a button inside that window which would change all those
cells to 'TBA' if theres no information in the cell.

Are any of these possible???
--
Jonno

Jonno wrote:
Hi,

I would like for my work sheet to state that information has not been
entered when i save.

Titles are
A1 = name A2 = number A3 = Address

and the data is in the cell below i.e B1 = John .....etc

I would like a box stating if information has not been filled in the cell
when i save.
There could be 1 or many bits of information missing and i would like a list
showing them all (just the missing parts).

Then if possible a button inside that window which would change all those
cells to 'TBA' if theres no information in the cell.

Are any of these possible???

Yes, with VBA. The code below should be pasted into the "ThisWorkbook"
module. To get there, right click the sheet tab | View Code, then double
click the "ThisWorkbook" icon in the project tree. You will need to make
sure the correct sheet name is in place about 9 lines down.

'BEGIN CODE --------------------------------------------------
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim B1, B2, B3
Dim Prompt As String

' replace "Sheet1" with the appropriate worksheet name below
Sheets("Sheet1").Activate

B1 = Range("B1").Text
B2 = Range("B2").Text
B3 = Range("B3").Text
If B1 = "" Or B2 = "" Or B3 = "" Then
Prompt = "Missing information: " & vbCrLf
If B1 = "" Then Prompt = Prompt & "Name" & vbCrLf
If B2 = "" Then Prompt = Prompt & "Number" & vbCrLf
If B3 = "" Then Prompt = Prompt & "Address" & vbCrLf
Prompt = Prompt & _
"Click OK to fill with 'TBA' or Cancel to cancel the save."
If MsgBox(Prompt, vbOKCancel Or vbDefaultButton2, _
"Missing Information") = vbCancel Then
Cancel = True
Exit Sub
End If
If B1 = "" Then Range("B1") = "TBA"
If B2 = "" Then Range("B2") = "TBA"
If B3 = "" Then Range("B3") = "TBA"
End If
End Sub
'END CODE ----------------------------------------------------

Hi Smartin,

This works great thank you very much!!!!!!!!!
--
Jonno


"smartin" wrote:

Jonno wrote:
Hi,

I would like for my work sheet to state that information has not been
entered when i save.

Titles are
A1 = name A2 = number A3 = Address

and the data is in the cell below i.e B1 = John .....etc

I would like a box stating if information has not been filled in the cell
when i save.
There could be 1 or many bits of information missing and i would like a list
showing them all (just the missing parts).

Then if possible a button inside that window which would change all those
cells to 'TBA' if theres no information in the cell.

Are any of these possible???

Yes, with VBA. The code below should be pasted into the "ThisWorkbook"
module. To get there, right click the sheet tab | View Code, then double
click the "ThisWorkbook" icon in the project tree. You will need to make
sure the correct sheet name is in place about 9 lines down.

'BEGIN CODE --------------------------------------------------
Option Explicit

Private Sub Workbook_BeforeSave( _
ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim B1, B2, B3
Dim Prompt As String

' replace "Sheet1" with the appropriate worksheet name below
Sheets("Sheet1").Activate

B1 = Range("B1").Text
B2 = Range("B2").Text
B3 = Range("B3").Text
If B1 = "" Or B2 = "" Or B3 = "" Then
Prompt = "Missing information: " & vbCrLf
If B1 = "" Then Prompt = Prompt & "Name" & vbCrLf
If B2 = "" Then Prompt = Prompt & "Number" & vbCrLf
If B3 = "" Then Prompt = Prompt & "Address" & vbCrLf
Prompt = Prompt & _
"Click OK to fill with 'TBA' or Cancel to cancel the save."
If MsgBox(Prompt, vbOKCancel Or vbDefaultButton2, _
"Missing Information") = vbCancel Then
Cancel = True
Exit Sub
End If
If B1 = "" Then Range("B1") = "TBA"
If B2 = "" Then Range("B2") = "TBA"
If B3 = "" Then Range("B3") = "TBA"
End If
End Sub
'END CODE ----------------------------------------------------