Can't Figure out the error

anand3dinesh

Board Regular
Joined
Dec 19, 2019
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
Hello my dear friends,

i have this Worksheet_Change event, i do not know what is exact error but it keeps saying there is an error.
what i am trying to do is, if anyone deletes OvarAll shape then macro will throw an error right so uppon error macro should go and insert Ovel shape and rename it as OverAll.

if i take out on error goto ErrHangle line, macro works fine but i can't make macro to insert Ovel shape as i explained above.
my error hangling msg box simply shows blank message
below is my code


Private Sub Worksheet_Change(ByVal Target As Range)

Set Target = Range("G21")
On Error GoTo ErrHandle

If Target.Value = 1 Then
Sheets("Summary").Shapes("OverAll").Fill.ForeColor.RGB = RGB(255, 0, 0)
ElseIf Target.Value = 2 Then
Sheets("Summary").Shapes("OverAll").Fill.ForeColor.RGB = RGB(255, 192, 0)
ElseIf Target.Value = 3 Then
Sheets("Summary").Shapes("OverAll").Fill.ForeColor.RGB = RGB(0, 176, 80)
Else
Sheets("Summary").Shapes("OverAll").Fill.ForeColor.RGB = RGB(255, 255, 255)
End If

ErrHandle:
MsgBox Err.Description

End Sub
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,042
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Palce an Exit Sub above your error handling label

Rich (BB code):
Exit Sub
ErrHandle:
MsgBox Err.Description

If your code errors, it will be directed to the label & msgbox reports the error otherwise, code will exit at Exit Sub line.

Dave
 

anand3dinesh

Board Regular
Joined
Dec 19, 2019
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
H
Hi,
Palce an Exit Sub above your error handling label

Rich (BB code):
Exit Sub
ErrHandle:
MsgBox Err.Description

If your code errors, it will be directed to the label & msgbox reports the error otherwise, code will exit at Exit Sub line.

Dave
Hi Dave thanks for your solution, but i want to know what is the error instead of exiting sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,042
Office Version
  1. 2019
Platform
  1. Windows
H

Hi Dave thanks for your solution, but i want to know what is the error instead of exiting sub
If your message box is blank there is no error
unless you exit before the error label the msgbox will always be displayed

An alternative approach would be to include a test for an error

VBA Code:
ErrHandle:
If Err <> 0 Then MsgBox Err.Description

Dave
 
Solution

anand3dinesh

Board Regular
Joined
Dec 19, 2019
Messages
88
Office Version
  1. 2016
Platform
  1. Windows
If your message box is blank there is no error
unless you exit before the error label the msgbox will always be displayed

An alternative approach would be to include a test for an error

VBA Code:
ErrHandle:
If Err <> 0 Then MsgBox Err.Description

Dave
Thank you very much. that helped me a lot
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,604
Messages
5,765,383
Members
425,281
Latest member
tmoreira001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top