Right-Click events

Hi Kevin

The default property for a Range Object is Value. Target is a Range Object. So the line:

If IsNumeric(Target) Then

Is really saying:

If IsNumeric(Target.Value) Then

Now any truly empty cell has a Value of zero, so the Target.Value IsNumeric!

You have got the right idea by using the IsEmpty Function. But one thing (again while your still fresh) avoid using the GoTo Statement in your code the force it to jump to a particular line in your Procedure. The reason is that once you start writting lengthy code and it contains even a few GoTo Statements, you end up with what is known a spaghetti code. This is very hard to read when you come back to it later on.

The use of "GoTo" (IMO) should only be used in two cases.

1. As the very first line in your Procedure to GoTo an Error Handler.

2. To repeat a SMALL block of code. Let's say you collect some data via an InputBox and you only want a Number greater the zero, you could then use the GoTo like this:

Reply2Start:
Reply2 = 0
Reply2 = InputBox("Lowest number ?" _
& Chr(13) & Chr(13) & "Number must be greater than 0 and entered without spaces or commas" & Chr(13) _
, "OzGrid Random Number Generator", 1)
If Reply2 = 0 Then Exit Sub

If Reply2 < 1 Then
MsgBox "Number must be greater than 0", vbCritical, "OzGrid Business Applications"
GoTo Reply2Start
End If

In Excel 97 the default for the Worksheet Modules was:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

...and for the Workbook Modules was:

Private Sub Workbook_Open()

End Sub

This is not the case in Excel 2000. However there still no need to type anthing! While in the Private Module (Workbook or Worsheet) click on the "Object" box (it will have "(General)" in it) and select "Workbook" or "Worksheet" and the default will appear. All other Events for the Object will appear in the "Procedure" box to the right.

Hope your cold gets better :o)

Dave

OzGrid Business Applications

Hi Dave:

Regarding the statement:
If IsEmpty(Target) Then GoTo Out

Mr. Walkenbach had the same caution about using "Goto." Problem is, I couldn't see any other choice. I tried:

If IsEmpty(Target) Then Exit Sub

but that gave me a syntax error. What would you recommend?

Also, having "dabbled" in coding (sporatically), I am aware of the dangers of spegetti code. Please feel free to critique my efforts as much as you want. I truly do respect that you are much more advanced than myself.

Thanks,
Kevin

Hi Kevin

RE: Please feel free to critique my efforts as much as you want.

I'm glad you understand, sometimes I write something then when I read it back it just doesn't like I intended. One of the draw back with WWW I guess. I also tend to always look at all code (including mine) with a critical eye.

The code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If IsEmpty(Target) Then Exit Sub
MsgBox "I'm not empty"
Cancel = True
End Sub

Works as expected! Are you sure the "Target" Object has been declared? Copy the above code directly in and try it. Make sure it goes in the Worksheet Module and NOT the Workbook module though.

Dave

OzGrid Business Applications

My computer burped

Hi Dave,

You know, either I mistyped the original and didn't catch the typo or my computer belched. This time I typed it in and it worked.

Private Sub WorkSheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If IsEmpty(Target) Then Exit Sub
If IsNumeric(Target) Then
Application.Dialogs(xlDialogFormatNumber).Show
Cancel = True
End If
End Sub

Maybe I need to take more breaks between my reading sessions.

Kevin