Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defind Dynamic named Range up to first error | Excel Worksheet Functions | |||
#value! error trying to create a simple dynamic named range | Excel Discussion (Misc queries) | |||
getting the absolute range address from a dynamic named range | Excel Programming | |||
Trapping error with dynamic range | Excel Programming | |||
Named Range - Empty columns | Excel Programming |