Trapping error from Empty Dynamic Named Range
 

Hi,
I have a UserForm with 8 command buttons which set the RowSource fo
the listBox on the UserForm. The 8 RowSources are all Dynamic Name
Ranges on a Worksheet.
The problem is trapping the error that occurs when one of these Dynami
Named Ranges is empty of data. I have tried IsEmpty, IsNull, IsError
IsMissing but I can't get any of these to work. Can someone give m
some direction on this one?

Here is my current code:
This code throws a run-time error 1004 App or Obj defined error. Debu
highlights the Set Rng8......Statement.

Private Sub CommandButton8_Click()
Dim Msg As Integer
Dim Rng8 As Variant
Set Rng8 = Worksheets("DataBase").Range("FutureCatIIDB")
If IsMissing(Rng8) Then
Msg = MsgBox("The database you are trying to access is empty" _
& (Chr(13)) & "Return to the DataBase Worksheet enter items." _
& (Chr(13)) & "Each database must have at least one item.", _
vbOK + vbInformation, "Database Empty")
Else
ListBox1.RowSource = "FutureCatIIDB"
OptionButton1.Value = False
OptionButton2.Value = False
ListBox1.SetFocus
End If
End Su

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=55120

Trapping error from Empty Dynamic Named Range
 

Maybe...

Private Sub CommandButton8_Click()
Dim Msg As Integer
Dim Rng8 As Range 'changed
set Rng8 = nothing
on error resume next
Set Rng8 = Worksheets("DataBase").Range("FutureCatIIDB")
on error goto 0

if rng8 is nothing then
msgbox "No range named Futurecatiidb on the database worksheet"
exit sub
end if

if application.counta(rng8) = 0 then
Msg = MsgBox("The database you are trying to access is empty" _
& (Chr(13)) & "Return to the DataBase Worksheet enter items." _
& (Chr(13)) & "Each database must have at least one item.", _
vbOK + vbInformation, "Database Empty")
Else
ListBox1.RowSource = "FutureCatIIDB"
OptionButton1.Value = False
OptionButton2.Value = False
ListBox1.SetFocus
End If
End Sub

Casey wrote:

Hi,
I have a UserForm with 8 command buttons which set the RowSource for
the listBox on the UserForm. The 8 RowSources are all Dynamic Named
Ranges on a Worksheet.
The problem is trapping the error that occurs when one of these Dynamic
Named Ranges is empty of data. I have tried IsEmpty, IsNull, IsError,
IsMissing but I can't get any of these to work. Can someone give me
some direction on this one?

Here is my current code:
This code throws a run-time error 1004 App or Obj defined error. Debug
highlights the Set Rng8......Statement.

Private Sub CommandButton8_Click()
Dim Msg As Integer
Dim Rng8 As Variant
Set Rng8 = Worksheets("DataBase").Range("FutureCatIIDB")
If IsMissing(Rng8) Then
Msg = MsgBox("The database you are trying to access is empty" _
& (Chr(13)) & "Return to the DataBase Worksheet enter items." _
& (Chr(13)) & "Each database must have at least one item.", _
vbOK + vbInformation, "Database Empty")
Else
ListBox1.RowSource = "FutureCatIIDB"
OptionButton1.Value = False
OptionButton2.Value = False
ListBox1.SetFocus
End If
End Sub

--
Casey

------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=551205

--

Dave Peterson

Trapping error from Empty Dynamic Named Range
 

Dave,
Just what I needed, works exactly as I need it too when the Named rang
is empty and just as I would expect it to when the Named range has data
however I'm confused why there appears to be 2 checks on Rng8 to g
through. One if Rng8 is nothing and then one if CountA (Rng8) = 0.
Are they both necessary or were you just modeling my code so as not t
confuse my tired brain?
If they are necessary, could I ask you what part each test plays i
trapping potential errors

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=55120

Trapping error from Empty Dynamic Named Range
 

The first check
if rng8 is nothing then
Checks to see if there was an actual range named FutureCatIIDB on that
worksheet. If that's something that could never happen, you can remove that
check.

The second check
if application.counta(rng8) = 0 then
checks to see if there is at least one cell in that range that isn't empty.

==
You may want to keep the first check--just in case someone deletes the name or
the range that the name refers to.

Casey wrote:

Dave,
Just what I needed, works exactly as I need it too when the Named range
is empty and just as I would expect it to when the Named range has data,
however I'm confused why there appears to be 2 checks on Rng8 to go
through. One if Rng8 is nothing and then one if CountA (Rng8) = 0.
Are they both necessary or were you just modeling my code so as not to
confuse my tired brain?
If they are necessary, could I ask you what part each test plays in
trapping potential errors.

--
Casey

------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=551205

--

Dave Peterson

Trapping error from Empty Dynamic Named Range
 

Dave,
Thank you very much for the help and the tutorial. I really appreciat
the time. I will keep both checks even though the "if rng8 is nothin
then" is, as you suspected, a very remote possibility. Thanks again

--
Case

-----------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...nfo&userid=454
View this thread: http://www.excelforum.com/showthread.php?threadid=55120

Trapping error from Empty Dynamic Named Range
 

If you can think of other things to check, you may want to add them to your
code, too.

The stuff that is only a remote possibility will happen the hour before your
vacation starts!

Casey wrote:

Dave,
Thank you very much for the help and the tutorial. I really appreciate
the time. I will keep both checks even though the "if rng8 is nothing
then" is, as you suspected, a very remote possibility. Thanks again.

--
Casey

------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=551205

--

Dave Peterson