checking for a blank field
 

I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank field in
col b then a msg box displayed

oldjay

checking for a blank field
 

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Tom When I run this code I always get the MsgBox even when there are no blank
cells. If I do an End - Arrow down on col b i go to the last record here is
my code

Private Sub CommandButton1_Click() 'This sets up data entry form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to check
the last row.

---
Regards,
Norman



"Oldjay" wrote in message
...
Tom When I run this code I always get the MsgBox even when there are no
blank
cells. If I do an End - Arrow down on col b i go to the last record here
is
my code

Private Sub CommandButton1_Click() 'This sets up data entry form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank
field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Crrl-End puts me way over and down from my list. I don't know how to delete
these extra rows and columns. I tried to select and delete, delete rows below
the liste and delete columns to the right of the list.

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to check
the last row.

---
Regards,
Norman



"Oldjay" wrote in message
...
Tom When I run this code I always get the MsgBox even when there are no
blank
cells. If I do an End - Arrow down on col b i go to the last record here
is
my code

Private Sub CommandButton1_Click() 'This sets up data entry form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank
field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Hi Oldjay,

See Debra Dalgleish's page on resetting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Oldjay" wrote in message
...
Crrl-End puts me way over and down from my list. I don't know how to
delete
these extra rows and columns. I tried to select and delete, delete rows
below
the liste and delete columns to the right of the list.

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to check
the last row.

---
Regards,
Norman



"Oldjay" wrote in message
...
Tom When I run this code I always get the MsgBox even when there are no
blank
cells. If I do an End - Arrow down on col b i go to the last record
here
is
my code

Private Sub CommandButton1_Click() 'This sets up data entry form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank
field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Thanks

"Norman Jones" wrote:

Hi Oldjay,

See Debra Dalgleish's page on resetting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Oldjay" wrote in message
...
Crrl-End puts me way over and down from my list. I don't know how to
delete
these extra rows and columns. I tried to select and delete, delete rows
below
the liste and delete columns to the right of the list.

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to check
the last row.

---
Regards,
Norman



"Oldjay" wrote in message
...
Tom When I run this code I always get the MsgBox even when there are no
blank
cells. If I do an End - Arrow down on col b i go to the last record
here
is
my code

Private Sub CommandButton1_Click() 'This sets up data entry form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank
field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Opps I spoke too fast. After I deleted the entire row and cols I did a
Ctrl-End and it went to the last record. I then ran the macro and got the msg
again

"Oldjay" wrote:

Thanks

"Norman Jones" wrote:

Hi Oldjay,

See Debra Dalgleish's page on resetting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Oldjay" wrote in message
...
Crrl-End puts me way over and down from my list. I don't know how to
delete
these extra rows and columns. I tried to select and delete, delete rows
below
the liste and delete columns to the right of the list.

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to check
the last row.

---
Regards,
Norman



"Oldjay" wrote in message
...
Tom When I run this code I always get the MsgBox even when there are no
blank
cells. If I do an End - Arrow down on col b i go to the last record
here
is
my code

Private Sub CommandButton1_Click() 'This sets up data entry form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a blank
field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Hi Oldjay,

Did you see Debra's note #4:

Save the file. Note: In older versions of Excel, you may have to Save, then
close and re-open the file before the used range is reset.


---
Regards,
Norman


"Oldjay" wrote in message
...
Opps I spoke too fast. After I deleted the entire row and cols I did a
Ctrl-End and it went to the last record. I then ran the macro and got the
msg
again

"Oldjay" wrote:

Thanks

"Norman Jones" wrote:

Hi Oldjay,

See Debra Dalgleish's page on resetting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Oldjay" wrote in message
...
Crrl-End puts me way over and down from my list. I don't know how to
delete
these extra rows and columns. I tried to select and delete, delete
rows
below
the liste and delete columns to the right of the list.

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to
check
the last row.

---
Regards,
Norman



"Oldjay" wrote in message
...
Tom When I run this code I always get the MsgBox even when there
are no
blank
cells. If I do an End - Arrow down on col b i go to the last
record
here
is
my code

Private Sub CommandButton1_Click() 'This sets up data entry
form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a
blank
field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

I had to reopen the file to reset the used range. it still always opens the
MsgBox

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Did you see Debra's note #4:

Save the file. Note: In older versions of Excel, you may have to Save, then
close and re-open the file before the used range is reset.


---
Regards,
Norman


"Oldjay" wrote in message
...
Opps I spoke too fast. After I deleted the entire row and cols I did a
Ctrl-End and it went to the last record. I then ran the macro and got the
msg
again

"Oldjay" wrote:

Thanks

"Norman Jones" wrote:

Hi Oldjay,

See Debra Dalgleish's page on resetting the used range at:

http://www.contextures.com/xlfaqApp.html#Unused


---
Regards,
Norman


"Oldjay" wrote in message
...
Crrl-End puts me way over and down from my list. I don't know how to
delete
these extra rows and columns. I tried to select and delete, delete
rows
below
the liste and delete columns to the right of the list.

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Tom's code works for me.

Perhaps, column B extends further than you imagine: try Ctrl-End to
check
the last row.

---
Regards,
Norman



"Oldjay" wrote in message
...
Tom When I run this code I always get the MsgBox even when there
are no
blank
cells. If I do an End - Arrow down on col b i go to the last
record
here
is
my code

Private Sub CommandButton1_Click() 'This sets up data entry
form


MainMenu.Hide ' Hides "What do you want to do today" form

Sheets("Records").Select 'Goes to membership list

Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Sorts
membership alphabetically

ActiveSheet.ShowDataForm

Application.Goto Reference:="Database"
Dim rng As Range
On Error Resume Next
Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
MsgBox "Column B contains blanks"
End If

Sheets("Form").Select
Range("A1").Select
Range("C10").Select

MainMenu.Show

End Sub

"Tom Ogilvy" wrote:

Dim rng as Range
On Error Resume Next
set rng = Columns(2).SpecialCells(xlBlanks)
On Error goto 0
if not rng is nothing then
msgbox "Column B contains blanks"
End if

--
Regards,
Tom Ogilvy

"Oldjay" wrote in message
...
I have a database that must have an entry in Col b
I want to filter the records (or what ever) and if it finds a
blank
field
in
col b then a msg box displayed

oldjay

checking for a blank field
 

Hi Oldjay,

If you wish, send me your file.


norman_jones@NOSPAMbtconnectDOTcom

Delete'NOSPAM' and replace 'DOT' with a period (full stop).


---
Regards,
Norman



"Oldjay" wrote in message
...
I had to reopen the file to reset the used range. it still always opens the
MsgBox

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Did you see Debra's note #4:

Save the file. Note: In older versions of Excel, you may have to Save,
then
close and re-open the file before the used range is reset.


---
Regards,
Norman

checking for a blank field
 

The OP sent me his workbook and two relevant problems were apparent:

(1) The used range on the sheet of interest needed to be rest to exclude
extraneous blank rows, and

(2) The B1 cell was empty, although this may not have been immediately
obvious as A1 contained a title header which flowed through B1. To resolve
this problem, I changed the OP's code condition:

if not rng is nothing then

to:

If rng.count 1 Then

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Oldjay,

If you wish, send me your file.


norman_jones@NOSPAMbtconnectDOTcom

Delete'NOSPAM' and replace 'DOT' with a period (full stop).


---
Regards,
Norman



"Oldjay" wrote in message
...
I had to reopen the file to reset the used range. it still always opens
the
MsgBox

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Did you see Debra's note #4:

Save the file. Note: In older versions of Excel, you may have to Save,
then
close and re-open the file before the used range is reset.


---
Regards,
Norman

checking for a blank field
 

Thanks again Every thing is working

oldjay (an 80 year old fart )

"Norman Jones" wrote:

The OP sent me his workbook and two relevant problems were apparent:

(1) The used range on the sheet of interest needed to be rest to exclude
extraneous blank rows, and

(2) The B1 cell was empty, although this may not have been immediately
obvious as A1 contained a title header which flowed through B1. To resolve
this problem, I changed the OP's code condition:

if not rng is nothing then

to:

If rng.count 1 Then

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Oldjay,

If you wish, send me your file.


norman_jones@NOSPAMbtconnectDOTcom

Delete'NOSPAM' and replace 'DOT' with a period (full stop).


---
Regards,
Norman



"Oldjay" wrote in message
...
I had to reopen the file to reset the used range. it still always opens
the
MsgBox

oldjay

"Norman Jones" wrote:

Hi Oldjay,

Did you see Debra's note #4:

Save the file. Note: In older versions of Excel, you may have to Save,
then
close and re-open the file before the used range is reset.


---
Regards,
Norman

checking for a blank field
 

Hi Oldjay,

oldjay (an 80 year old fart )

I sincerly hope that your comment was not prompted by a misinterpetation of
:

The OP sent me his workbook

Here, and conventionally in newsgroup parlance, OP is an abbreviation for
Original poster.

In any event, the design of your workbook would put many younger people to
shame and certainly impressed me..


---
Regards,
Norman


"Oldjay" wrote in message
...
Thanks again Every thing is working

oldjay (an 80 year old fart )

checking for a blank field
 

My comment was only to make an excuse for being slow and not realizing that
checking for blanks in col b meant all rows not just the ones within the list
Sorry for any confusion

oldjay

"Norman Jones" wrote:

Hi Oldjay,

oldjay (an 80 year old fart )

I sincerly hope that your comment was not prompted by a misinterpetation of
:

The OP sent me his workbook

Here, and conventionally in newsgroup parlance, OP is an abbreviation for
Original poster.

In any event, the design of your workbook would put many younger people to
shame and certainly impressed me..


---
Regards,
Norman


"Oldjay" wrote in message
...
Thanks again Every thing is working

oldjay (an 80 year old fart )

checking for a blank field
 

Another problem - when I delete a record the MsgBox opens and says "you have
a blank record"

"Oldjay" wrote:

My comment was only to make an excuse for being slow and not realizing that
checking for blanks in col b meant all rows not just the ones within the list
Sorry for any confusion

oldjay

"Norman Jones" wrote:

Hi Oldjay,

oldjay (an 80 year old fart )

I sincerly hope that your comment was not prompted by a misinterpetation of
:

The OP sent me his workbook

Here, and conventionally in newsgroup parlance, OP is an abbreviation for
Original poster.

In any event, the design of your workbook would put many younger people to
shame and certainly impressed me..


---
Regards,
Norman


"Oldjay" wrote in message
...
Thanks again Every thing is working

oldjay (an 80 year old fart )

checking for a blank field
 

Hi Jim,

In your MainMenu code module's CommandButton1_Click event, change:

On Error Resume Next

Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
' If Not rng Is Nothing Then
If rng.Count 1 Then

to:

On Error Resume Next
Set rng = Range("Database").Columns(2).SpecialCells(xlBlanks )
On Error GoTo 0

If Not rng Is Nothing Then


---
Regards,
Norman



"Oldjay" wrote in message
...
Another problem - when I delete a record the MsgBox opens and says "you
have
a blank record"

"Oldjay" wrote:

My comment was only to make an excuse for being slow and not realizing
that
checking for blanks in col b meant all rows not just the ones within the
list
Sorry for any confusion

oldjay

"Norman Jones" wrote:

Hi Oldjay,

oldjay (an 80 year old fart )

I sincerly hope that your comment was not prompted by a
misinterpetation of
:

The OP sent me his workbook

Here, and conventionally in newsgroup parlance, OP is an abbreviation
for
Original poster.

In any event, the design of your workbook would put many younger people
to
shame and certainly impressed me..


---
Regards,
Norman


"Oldjay" wrote in message
...
Thanks again Every thing is working

oldjay (an 80 year old fart )

checking for a blank field
 

That didn't do it. I added a new record with out a Last name. Hit closed.
then added a last name to the bad record. Hit Enter and then clicked on Main
Memu. Every thing OK to this point. Whent back to the DataForm and deleted
the newly added record I got the MsgBox again. The Used Range is one row
below the last record.

"Norman Jones" wrote:

Hi Jim,

In your MainMenu code module's CommandButton1_Click event, change:

On Error Resume Next

Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
' If Not rng Is Nothing Then
If rng.Count 1 Then

to:

On Error Resume Next
Set rng = Range("Database").Columns(2).SpecialCells(xlBlanks )
On Error GoTo 0

If Not rng Is Nothing Then


---
Regards,
Norman



"Oldjay" wrote in message
...
Another problem - when I delete a record the MsgBox opens and says "you
have
a blank record"

"Oldjay" wrote:

My comment was only to make an excuse for being slow and not realizing
that
checking for blanks in col b meant all rows not just the ones within the
list
Sorry for any confusion

oldjay

"Norman Jones" wrote:

Hi Oldjay,

oldjay (an 80 year old fart )

I sincerly hope that your comment was not prompted by a
misinterpetation of
:

The OP sent me his workbook

Here, and conventionally in newsgroup parlance, OP is an abbreviation
for
Original poster.

In any event, the design of your workbook would put many younger people
to
shame and certainly impressed me..


---
Regards,
Norman


"Oldjay" wrote in message
...
Thanks again Every thing is working

oldjay (an 80 year old fart )

checking for a blank field
 

Hi Jim,

I amended your userform code as suggested in my previous post.

I then invoked the dataform (using your userform menu) and added a new
record which consisted (solely) of a forename "JOE". As expected (and
intended), I received a msgbox notification of a missing surname. I then
re-invoked the dataform, located the defective record and appended the
missing surname BLOGGS. To be consistent with your situation, I then
reopened the dataform and deleted the JOE BLOGGS record. The record was
deleted without problem and without any specious alert.

Incidentally, precisely because deleting records via the dataform does not
reset the used range, I changed your (implicit) used range condition to use
the database range instead. Consequently, for these purposes, the extent of
the used range has no relevance.

I have, therefore, sent you the amended file and I invite you to retest.


---
Regards,
Norman



"Oldjay" wrote in message
...
That didn't do it. I added a new record with out a Last name. Hit closed.
then added a last name to the bad record. Hit Enter and then clicked on
Main
Memu. Every thing OK to this point. Whent back to the DataForm and deleted
the newly added record I got the MsgBox again. The Used Range is one row
below the last record.

"Norman Jones" wrote:

Hi Jim,

In your MainMenu code module's CommandButton1_Click event, change:

On Error Resume Next

Set rng = Columns(2).SpecialCells(xlBlanks)
On Error GoTo 0
' If Not rng Is Nothing Then
If rng.Count 1 Then

to:

On Error Resume Next
Set rng = Range("Database").Columns(2).SpecialCells(xlBlanks )
On Error GoTo 0

If Not rng Is Nothing Then


---
Regards,
Norman



"Oldjay" wrote in message
...
Another problem - when I delete a record the MsgBox opens and says "you
have
a blank record"

"Oldjay" wrote:

My comment was only to make an excuse for being slow and not realizing
that
checking for blanks in col b meant all rows not just the ones within
the
list
Sorry for any confusion

oldjay

"Norman Jones" wrote:

Hi Oldjay,

oldjay (an 80 year old fart )

I sincerly hope that your comment was not prompted by a
misinterpetation of
:

The OP sent me his workbook

Here, and conventionally in newsgroup parlance, OP is an
abbreviation
for
Original poster.

In any event, the design of your workbook would put many younger
people
to
shame and certainly impressed me..


---
Regards,
Norman


"Oldjay" wrote in message
...
Thanks again Every thing is working

oldjay (an 80 year old fart )

checking for a blank field
 

OldJay responded in a private email:

'=================
Norman

Every thing seems to be OK. I really appricate your efforts

How do you change (implicit) used range condition to use

the database range?

oldjay
'<<=================

Hi OldJay,

Your original condition looked for blank cells in column B using the
SpecialCells method. The SpecialCells method, unless explicitly further
restricted, operates on the used range.

As this caused problems relating to the deletion of database records, I
changed the control area to the dynamic Database range which expands and
contracts as records are added or deleted via the menu driven dataform.
Hence my suggestion to change:

Set rng = Columns(2).SpecialCells(xlBlanks)

to

Set rng = Range("Database").Columns(2).SpecialCells(xlBlanks )


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Jim,

I amended your userform code as suggested in my previous post.

I then invoked the dataform (using your userform menu) and added a new
record which consisted (solely) of a forename "JOE". As expected (and
intended), I received a msgbox notification of a missing surname. I then
re-invoked the dataform, located the defective record and appended the
missing surname BLOGGS. To be consistent with your situation, I then
reopened the dataform and deleted the JOE BLOGGS record. The record was
deleted without problem and without any specious alert.

Incidentally, precisely because deleting records via the dataform does not
reset the used range, I changed your (implicit) used range condition to
use the database range instead. Consequently, for these purposes, the
extent of the used range has no relevance.

I have, therefore, sent you the amended file and I invite you to retest.


---
Regards,
Norman