Making sure that cells within a row in a database aren't blank

[Pete Rooney]

Good afternoon, weekend Excelers!

I currently have a database in cells B14:L19

I'm trying to prevent users from having blank rows in the database by using
the Worksheet_Change event to check that if a change is made to any cells,
that this doesn't result in all eleven cells in the row being blank.

As you can see from the code below, I've tried lots of different ways to
(for example if I'm in row 16) to check that B16:L16 aren't blank, but
without much success. I get lots of "invalid qualifier" messages.

Can anyone advise me of the best way to go about this?

Thanks in advance

Pete Rooney


With Target

'---------------------------------------------------------------------------------
If .Column >= 2 Then
If .Column <= 12 Then
MsgBox (.Column & " " & .Row)

If Application.WorksheetFunction.isblank(Cells(Target.Row, 2)) Then
' If "C" & Target.Row.Value = 0 Then
' If "D" & Target.Row.Value = 0 Then
' If "E" & Target.Row.Value = 0 Then
' If "F" & Target.Row.Value = 0 Then
' If "G" & Target.Row.Value = 0 Then
' If "H" & Target.Row.Value = 0 Then
' If "I" & Target.Row.Value = 0 Then
' If "J" & Target.Row.Value = 0 Then
' If "K" & Target.Row.Value = 0 Then
' If "L" & Target.Row.Value = 0 Then
MsgBox ("You CAN'T have blank rows in the
database!")
' Range("F" & Target.Row).Formula = "Blank
Eliminator"
' End If
' End If
' End If
' End If
' End If
' End If
' End If
' End If
' End If
' End If
End If
End If
End If

'---------------------------------------------------------------------------------
If .Column = 11 Then
DefineDatabase
If .Row >= FirstDBRow Then
If .Row <= FinalDBRow Then

'---------------------------------------------------------------------------
If .Formula = "Core Infrastructure" Then

[Norman Jones]

Hi Pete,

Perhaps, something like this will do what you want:

'===========================>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rng2 As Range
Dim rw As Range

Set Rng = Range("A2:L20") '<<===== CHANGE
Set rng2 = Intersect(Rng, Target)


If Not rng2 Is Nothing Then
For Each rw In rng2.Rows
If Application.CountA(rw.Cells) = 0 Then
MsgBox ("Database row " & rw.Row _
& " is empty. You CAN'T have " _
& "blank rows in the database!")
End If
Next
End If
End Sub
'<<===========================

Change the range address to accord with the database range.


---
Regards,
Norman



"Pete Rooney" <Pete [email protected]> wrote in message
news:[email protected]...
> Good afternoon, weekend Excelers!
>
> I currently have a database in cells B14:L19
>
> I'm trying to prevent users from having blank rows in the database by
> using
> the Worksheet_Change event to check that if a change is made to any cells,
> that this doesn't result in all eleven cells in the row being blank.
>
> As you can see from the code below, I've tried lots of different ways to
> (for example if I'm in row 16) to check that B16:L16 aren't blank, but
> without much success. I get lots of "invalid qualifier" messages.
>
> Can anyone advise me of the best way to go about this?
>
> Thanks in advance
>
> Pete Rooney
>
>
> With Target
>
> '---------------------------------------------------------------------------------
> If .Column >= 2 Then
> If .Column <= 12 Then
> MsgBox (.Column & " " & .Row)
>
> If Application.WorksheetFunction.isblank(Cells(Target.Row, 2)) Then
> ' If "C" & Target.Row.Value = 0 Then
> ' If "D" & Target.Row.Value = 0 Then
> ' If "E" & Target.Row.Value = 0 Then
> ' If "F" & Target.Row.Value = 0 Then
> ' If "G" & Target.Row.Value = 0 Then
> ' If "H" & Target.Row.Value = 0 Then
> ' If "I" & Target.Row.Value = 0 Then
> ' If "J" & Target.Row.Value = 0 Then
> ' If "K" & Target.Row.Value = 0 Then
> ' If "L" & Target.Row.Value = 0 Then
> MsgBox ("You CAN'T have blank rows in the
> database!")
> ' Range("F" & Target.Row).Formula = "Blank
> Eliminator"
> ' End If
> ' End If
> ' End If
> ' End If
> ' End If
> ' End If
> ' End If
> ' End If
> ' End If
> ' End If
> End If
> End If
> End If
>
> '---------------------------------------------------------------------------------
> If .Column = 11 Then
> DefineDatabase
> If .Row >= FirstDBRow Then
> If .Row <= FinalDBRow Then
>
> '---------------------------------------------------------------------------
> If .Formula = "Core Infrastructure" Then
>

[Pete Rooney]

Norman,

Thanks for this.

A couple of issues - I need the range to be dynamic as the database will
extend or reduce, depending on user action. However, I have a routine that
determines the first and last cells of the range, so it's not too much of a
problem.

However, when I tried your code, I get the message even though there's
something in one of the columns.
I never have any luck with intersect!

Any thoughts?

Pete



"Norman Jones" wrote:

> Hi Pete,
>
> Perhaps, something like this will do what you want:
>
> '===========================>>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim Rng As Range
> Dim rng2 As Range
> Dim rw As Range
>
> Set Rng = Range("A2:L20") '<<===== CHANGE
> Set rng2 = Intersect(Rng, Target)
>
>
> If Not rng2 Is Nothing Then
> For Each rw In rng2.Rows
> If Application.CountA(rw.Cells) = 0 Then
> MsgBox ("Database row " & rw.Row _
> & " is empty. You CAN'T have " _
> & "blank rows in the database!")
> End If
> Next
> End If
> End Sub
> '<<===========================
>
> Change the range address to accord with the database range.
>
>
> ---
> Regards,
> Norman
>
>
>
> "Pete Rooney" <Pete [email protected]> wrote in message
> news:[email protected]...
> > Good afternoon, weekend Excelers!
> >
> > I currently have a database in cells B14:L19
> >
> > I'm trying to prevent users from having blank rows in the database by
> > using
> > the Worksheet_Change event to check that if a change is made to any cells,
> > that this doesn't result in all eleven cells in the row being blank.
> >
> > As you can see from the code below, I've tried lots of different ways to
> > (for example if I'm in row 16) to check that B16:L16 aren't blank, but
> > without much success. I get lots of "invalid qualifier" messages.
> >
> > Can anyone advise me of the best way to go about this?
> >
> > Thanks in advance
> >
> > Pete Rooney
> >
> >
> > With Target
> >
> > '---------------------------------------------------------------------------------
> > If .Column >= 2 Then
> > If .Column <= 12 Then
> > MsgBox (.Column & " " & .Row)
> >
> > If Application.WorksheetFunction.isblank(Cells(Target.Row, 2)) Then
> > ' If "C" & Target.Row.Value = 0 Then
> > ' If "D" & Target.Row.Value = 0 Then
> > ' If "E" & Target.Row.Value = 0 Then
> > ' If "F" & Target.Row.Value = 0 Then
> > ' If "G" & Target.Row.Value = 0 Then
> > ' If "H" & Target.Row.Value = 0 Then
> > ' If "I" & Target.Row.Value = 0 Then
> > ' If "J" & Target.Row.Value = 0 Then
> > ' If "K" & Target.Row.Value = 0 Then
> > ' If "L" & Target.Row.Value = 0 Then
> > MsgBox ("You CAN'T have blank rows in the
> > database!")
> > ' Range("F" & Target.Row).Formula = "Blank
> > Eliminator"
> > ' End If
> > ' End If
> > ' End If
> > ' End If
> > ' End If
> > ' End If
> > ' End If
> > ' End If
> > ' End If
> > ' End If
> > End If
> > End If
> > End If
> >
> > '---------------------------------------------------------------------------------
> > If .Column = 11 Then
> > DefineDatabase
> > If .Row >= FirstDBRow Then
> > If .Row <= FinalDBRow Then
> >
> > '---------------------------------------------------------------------------
> > If .Formula = "Core Infrastructure" Then
> >
>
>
>

[Norman Jones]

Hi Pete,

> A couple of issues - I need the range to be dynamic as the database will
> extend or reduce, depending on user action. However, I have a routine that
> determines the first and last cells of the range, so it's not too much of
> a
> problem.

Fine. As you intimate, instead of defining a static range, establish and
define the boundaries.

Alternatively, perhaps you can use a dynamic range (nammed, say
"MyDatabase") and set the rng variable to MyDatabase.

If you are not familiar with dynamic ranges, see debra Dalgleish at:

http://www.contextures.com/xlNames01.html#Dynamic


> However, when I tried your code, I get the message even though there's
> something in one of the columns.
> I never have any luck with intersect!

My fault! Try changing:

>> Set rng2 = Intersect(Rng, Target)

to

Set rng2 = Intersect(Rng, Target.EntireRow)

---
Regards,
Norman



"Pete Rooney" <[email protected]> wrote in message
news:[email protected]...
> Norman,
>
> Thanks for this.
>
> A couple of issues - I need the range to be dynamic as the database will
> extend or reduce, depending on user action. However, I have a routine that
> determines the first and last cells of the range, so it's not too much of
> a
> problem.
>
> However, when I tried your code, I get the message even though there's
> something in one of the columns.
> I never have any luck with intersect!
>
> Any thoughts?
>
> Pete
>
>
>
> "Norman Jones" wrote:
>
>> Hi Pete,
>>
>> Perhaps, something like this will do what you want:
>>
>> '===========================>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Dim Rng As Range
>> Dim rng2 As Range
>> Dim rw As Range
>>
>> Set Rng = Range("A2:L20") '<<===== CHANGE
>> Set rng2 = Intersect(Rng, Target)
>>
>>
>> If Not rng2 Is Nothing Then
>> For Each rw In rng2.Rows
>> If Application.CountA(rw.Cells) = 0 Then
>> MsgBox ("Database row " & rw.Row _
>> & " is empty. You CAN'T have " _
>> & "blank rows in the database!")
>> End If
>> Next
>> End If
>> End Sub
>> '<<===========================
>>
>> Change the range address to accord with the database range.
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Pete Rooney" <Pete [email protected]> wrote in message
>> news:[email protected]...
>> > Good afternoon, weekend Excelers!
>> >
>> > I currently have a database in cells B14:L19
>> >
>> > I'm trying to prevent users from having blank rows in the database by
>> > using
>> > the Worksheet_Change event to check that if a change is made to any
>> > cells,
>> > that this doesn't result in all eleven cells in the row being blank.
>> >
>> > As you can see from the code below, I've tried lots of different ways
>> > to
>> > (for example if I'm in row 16) to check that B16:L16 aren't blank, but
>> > without much success. I get lots of "invalid qualifier" messages.
>> >
>> > Can anyone advise me of the best way to go about this?
>> >
>> > Thanks in advance
>> >
>> > Pete Rooney
>> >
>> >
>> > With Target
>> >
>> > '---------------------------------------------------------------------------------
>> > If .Column >= 2 Then
>> > If .Column <= 12 Then
>> > MsgBox (.Column & " " & .Row)
>> >
>> > If Application.WorksheetFunction.isblank(Cells(Target.Row, 2))
>> > Then
>> > ' If "C" & Target.Row.Value = 0 Then
>> > ' If "D" & Target.Row.Value = 0 Then
>> > ' If "E" & Target.Row.Value = 0 Then
>> > ' If "F" & Target.Row.Value = 0 Then
>> > ' If "G" & Target.Row.Value = 0 Then
>> > ' If "H" & Target.Row.Value = 0 Then
>> > ' If "I" & Target.Row.Value = 0 Then
>> > ' If "J" & Target.Row.Value = 0 Then
>> > ' If "K" & Target.Row.Value = 0 Then
>> > ' If "L" & Target.Row.Value = 0 Then
>> > MsgBox ("You CAN'T have blank rows in the
>> > database!")
>> > ' Range("F" & Target.Row).Formula = "Blank
>> > Eliminator"
>> > ' End If
>> > ' End If
>> > ' End If
>> > ' End If
>> > ' End If
>> > ' End If
>> > ' End If
>> > ' End If
>> > ' End If
>> > ' End If
>> > End If
>> > End If
>> > End If
>> >
>> > '---------------------------------------------------------------------------------
>> > If .Column = 11 Then
>> > DefineDatabase
>> > If .Row >= FirstDBRow Then
>> > If .Row <= FinalDBRow Then
>> >
>> > '---------------------------------------------------------------------------
>> > If .Formula = "Core Infrastructure" Then
>> >
>>
>>
>>