Hello,
For a macro to work there are files that need to contain the 4 named ranges:
InvCountry
InvDate
TotalDisb
TotalFees
I have a word document with instructions in but then at the end i have a link to a file that has a macro in it that will test the workbook to make sure that the ranges exist. If any are missing then i want a message to the user to let them know which ones are missing.
I'm not too sure about how to see if a range exists or not so the code i have already uses 'goto error' sections in the event that the range doesn't exist which will add that range name to variable 'a' which will then produce the message box in the end.
My code doesn't work. It keeps on coming up with error messages and it seems to ignore my 'On error' lines. Does anyone know why the code isn't working or does anyone know a better way to check if ranges exist?
(I don't have much experience using On Error so this is the best i coud come up with to check the ranges)
here's the code i have at the moment: (It sits in the ThisWorkbook section)
Private Sub Workbook_Open()
ActiveWindow.ActivateNext
Check1:
On Error GoTo Error1
Application.Goto Reference:="InvCountry"
On Error GoTo 0
Check2:
On Error GoTo Error2
Application.Goto Reference:="InvDate"
On Error GoTo 0
Check3:
On Error GoTo Error3
Application.Goto Reference:="TotalDisb"
On Error GoTo 0
Check4:
On Error GoTo Error4
Application.Goto Reference:="TotalFees"
On Error GoTo 0
Error1:
a = "InvCountry" & Chr(13)
GoTo Check2
Error2:
a = a & InvDate & Chr(13)
GoTo Check3
Error3:
a = a & TotalDisb & Chr(13)
GoTo Check4
Error4:
a = a & TotalFees & Chr(13)
If a <> " " Then
MsgBox a, vbInformation, "Check complete"
else
MsgBox "The check is complete. You have added all the correct ranges to the workbook", vbInformation, "Check complete"
End If
End Sub
Thanks for any help in advance.
For a macro to work there are files that need to contain the 4 named ranges:
InvCountry
InvDate
TotalDisb
TotalFees
I have a word document with instructions in but then at the end i have a link to a file that has a macro in it that will test the workbook to make sure that the ranges exist. If any are missing then i want a message to the user to let them know which ones are missing.
I'm not too sure about how to see if a range exists or not so the code i have already uses 'goto error' sections in the event that the range doesn't exist which will add that range name to variable 'a' which will then produce the message box in the end.
My code doesn't work. It keeps on coming up with error messages and it seems to ignore my 'On error' lines. Does anyone know why the code isn't working or does anyone know a better way to check if ranges exist?
(I don't have much experience using On Error so this is the best i coud come up with to check the ranges)
here's the code i have at the moment: (It sits in the ThisWorkbook section)
Private Sub Workbook_Open()
ActiveWindow.ActivateNext
Check1:
On Error GoTo Error1
Application.Goto Reference:="InvCountry"
On Error GoTo 0
Check2:
On Error GoTo Error2
Application.Goto Reference:="InvDate"
On Error GoTo 0
Check3:
On Error GoTo Error3
Application.Goto Reference:="TotalDisb"
On Error GoTo 0
Check4:
On Error GoTo Error4
Application.Goto Reference:="TotalFees"
On Error GoTo 0
Error1:
a = "InvCountry" & Chr(13)
GoTo Check2
Error2:
a = a & InvDate & Chr(13)
GoTo Check3
Error3:
a = a & TotalDisb & Chr(13)
GoTo Check4
Error4:
a = a & TotalFees & Chr(13)
If a <> " " Then
MsgBox a, vbInformation, "Check complete"
else
MsgBox "The check is complete. You have added all the correct ranges to the workbook", vbInformation, "Check complete"
End If
End Sub
Thanks for any help in advance.