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