checking for a blank field

[Oldjay]

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

[Tom Ogilvy]

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" <jauld1@hotmail.com> wrote in message
news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> 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
>
>

[Oldjay]

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" <jauld1@hotmail.com> wrote in message
> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> > 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
> >
> >
>
>
>

[Norman Jones]

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" <Oldjay@discussions.microsoft.com> wrote in message
news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
> 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" <jauld1@hotmail.com> wrote in message
>> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
>> > 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
>> >
>> >
>>
>>
>>

[Oldjay]

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" <Oldjay@discussions.microsoft.com> wrote in message
> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
> > 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" <jauld1@hotmail.com> wrote in message
> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> >> > 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
> >> >
> >> >
> >>
> >>
> >>
>
>
>

[Norman Jones]

Hi Oldjay,

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

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


---
Regards,
Norman


"Oldjay" <Oldjay@discussions.microsoft.com> wrote in message
news:1EC075AE-B63D-45B6-951E-AF081C9D7C40@microsoft.com...
> 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" <Oldjay@discussions.microsoft.com> wrote in message
>> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
>> > 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" <jauld1@hotmail.com> wrote in message
>> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[Oldjay]

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" <Oldjay@discussions.microsoft.com> wrote in message
> news:1EC075AE-B63D-45B6-951E-AF081C9D7C40@microsoft.com...
> > 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" <Oldjay@discussions.microsoft.com> wrote in message
> >> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
> >> > 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" <jauld1@hotmail.com> wrote in message
> >> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> >> >> > 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Oldjay]

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" <Oldjay@discussions.microsoft.com> wrote in message
> > news:1EC075AE-B63D-45B6-951E-AF081C9D7C40@microsoft.com...
> > > 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" <Oldjay@discussions.microsoft.com> wrote in message
> > >> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
> > >> > 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" <jauld1@hotmail.com> wrote in message
> > >> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> > >> >> > 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
> > >> >> >
> > >> >> >
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >

[Norman Jones]

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" <Oldjay@discussions.microsoft.com> wrote in message
news:40E024B1-D4BF-4056-B30E-5CFAB59947FB@microsoft.com...
> 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" <Oldjay@discussions.microsoft.com> wrote in message
>> > news:1EC075AE-B63D-45B6-951E-AF081C9D7C40@microsoft.com...
>> > > 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" <Oldjay@discussions.microsoft.com> wrote in message
>> > >> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
>> > >> > 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" <jauld1@hotmail.com> wrote in message
>> > >> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
>> > >> >> > 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
>> > >> >> >
>> > >> >> >
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >

[Oldjay]

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" <Oldjay@discussions.microsoft.com> wrote in message
> news:40E024B1-D4BF-4056-B30E-5CFAB59947FB@microsoft.com...
> > 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" <Oldjay@discussions.microsoft.com> wrote in message
> >> > news:1EC075AE-B63D-45B6-951E-AF081C9D7C40@microsoft.com...
> >> > > 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" <Oldjay@discussions.microsoft.com> wrote in message
> >> > >> news:D1244FF0-FEDA-4D24-BD43-2F77B6EF8A2C@microsoft.com...
> >> > >> > 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" <jauld1@hotmail.com> wrote in message
> >> > >> >> news:%236p0d7G$FHA.2156@TK2MSFTNGP11.phx.gbl...
> >> > >> >> > 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
> >> > >> >> >
> >> > >> >> >
> >> > >> >>
> >> > >> >>
> >> > >> >>
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
>
>
>

[Norman Jones]

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" <Oldjay@discussions.microsoft.com> wrote in message
news:F9EE7BFD-6349-48AD-BD91-8BBAD808A63B@microsoft.com...
>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

[Norman Jones]

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" <normanjones@whereforartthou.com> wrote in message
news:uOva8EP$FHA.160@TK2MSFTNGP12.phx.gbl...
> 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" <Oldjay@discussions.microsoft.com> wrote in message
> news:F9EE7BFD-6349-48AD-BD91-8BBAD808A63B@microsoft.com...
>>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]

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" <normanjones@whereforartthou.com> wrote in message
> news:uOva8EP$FHA.160@TK2MSFTNGP12.phx.gbl...
> > 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" <Oldjay@discussions.microsoft.com> wrote in message
> > news:F9EE7BFD-6349-48AD-BD91-8BBAD808A63B@microsoft.com...
> >>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
> >
> >
>
>
>

[Norman Jones]

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" <Oldjay@discussions.microsoft.com> wrote in message
news:F67BBC23-4D55-4788-BFEA-5484D27E3A20@microsoft.com...
> Thanks again Every thing is working
>
> oldjay (an 80 year old fart )
>

[Oldjay]

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" <Oldjay@discussions.microsoft.com> wrote in message
> news:F67BBC23-4D55-4788-BFEA-5484D27E3A20@microsoft.com...
> > Thanks again Every thing is working
> >
> > oldjay (an 80 year old fart )
> >
>
>
>

[Oldjay]

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" <Oldjay@discussions.microsoft.com> wrote in message
> > news:F67BBC23-4D55-4788-BFEA-5484D27E3A20@microsoft.com...
> > > Thanks again Every thing is working
> > >
> > > oldjay (an 80 year old fart )
> > >
> >
> >
> >

[Norman Jones]

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" <Oldjay@discussions.microsoft.com> wrote in message
news:FA852A47-4F4B-4093-B169-758E52329015@microsoft.com...
> 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" <Oldjay@discussions.microsoft.com> wrote in message
>> > news:F67BBC23-4D55-4788-BFEA-5484D27E3A20@microsoft.com...
>> > > Thanks again Every thing is working
>> > >
>> > > oldjay (an 80 year old fart )
>> > >
>> >
>> >
>> >

[Oldjay]

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" <Oldjay@discussions.microsoft.com> wrote in message
> news:FA852A47-4F4B-4093-B169-758E52329015@microsoft.com...
> > 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" <Oldjay@discussions.microsoft.com> wrote in message
> >> > news:F67BBC23-4D55-4788-BFEA-5484D27E3A20@microsoft.com...
> >> > > Thanks again Every thing is working
> >> > >
> >> > > oldjay (an 80 year old fart )
> >> > >
> >> >
> >> >
> >> >
>
>
>

[Norman Jones]

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" <Oldjay@discussions.microsoft.com> wrote in message
news:BCEA45AD-5C5F-4E73-9FB5-5BFF64A000E4@microsoft.com...
> 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" <Oldjay@discussions.microsoft.com> wrote in message
>> news:FA852A47-4F4B-4093-B169-758E52329015@microsoft.com...
>> > 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" <Oldjay@discussions.microsoft.com> wrote in message
>> >> > news:F67BBC23-4D55-4788-BFEA-5484D27E3A20@microsoft.com...
>> >> > > Thanks again Every thing is working
>> >> > >
>> >> > > oldjay (an 80 year old fart )
>> >> > >
>> >> >
>> >> >
>> >> >
>>
>>
>>

[Norman Jones]

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" <normanjones@whereforartthou.com> wrote in message
news:eEATFpc$FHA.264@tk2msftngp13.phx.gbl...
> 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