Thread: 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

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

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