XL2000: Check Macro for Missing Cell Value

[locutis]

Hello Excel gurus,

I have a lookup sheet with a particular value that is populated from a database via MS Query. This value is used by a display sheet. Works fine, but if the value is empty, I would like to be able to alert the user with a msgbox, then return false. I've been looking at a few things out there, here is one I found and tried to make work, but can't quite get it:

Public gOldN1 As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("N1")) Is Nothing Then
MsgBox "A Billing Contact is Required
End If
End If
End Sub

When the workbook starts:

Private Sub Workbook_Open()
gOldN1 = Sheets("Sheet2").Range("N1").Value
End Sub

Am I on the right track, or is there an easier way to do what I'm trying to do? I also want to return false and not let the sheet load.

Thanks in advance,
Jean Luc

 

[SkipVought]

Hi,

"...I would like to be able to alert the user with a msgbox..."

WHEN?

What event or condition should trigger this message?

If it's the Workbook_open event then

Private Sub Workbook_Open() 
with Sheets("Sheet2").Range("N1")
   if .Value ="" then
      Msgbox("enter data")
      .parent.activate
      .select
   end if
end with
End Sub

Skip,

_[red][/red]

 

[locutis]

Hi Skip,

If the cell on a particular sheet is empty, then alert the user that a value is required and return false. Another application will be used to enter the required value, not Excel, so not input at this time would be required.

Thanks,
Jean Luc

 

[locutis]

Hi Skip,

Let me explain further:

User enters a value, pulls data into lookup sheet which populates display sheet. Works great. There is one cell on the lookup sheet however that needs a certain value, otherwise the display sheet doesn't load properly. So, I just want to alert the user that this value is required and that it needs to be there in order for them to proceed with the sheet. They will go to a separate application to enter the value, so all I need here on worksheet_change event is to detect if this cell is empty, and if so, alert the user and stop loading the sheet.

Sorry if I didn't explain it better in the beginning.

Thanks for your help, you're very patient.

Jean Luc

 

[SkipVought]

So is it when the, "User enters a value, pulls data into lookup sheet which populates display sheet"

Is that the event that triggers the evaluation of Sheets("Sheet2").Range("N1")?

Skip,

_[red][/red]

 

[locutis]

Yes sir, you are correct.

 

[SkipVought]

then

Private Sub Worksheet_Change(ByVal Target As Range) 

if target.address = [b][the cell that the user enters the value in][/b].address then
  with Sheets("Sheet2").Range("N1")
    if .Value ="" then
      Msgbox("enter data")
      .parent.activate
      .select
    end if
  end with
end if
End Sub

Skip,

_[red][/red]

 

[locutis]

Thanks Skip! I'll work with that and let you know how it goes!

JL