MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Right-Click events


Posted by Kevin James on May 26, 2001 2:57 PM

Howdy all:

Well, I'm now on page 165 of the "Excel2000 Programming for Dummies" book. The following code is presented, *minus* the commented lines:

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

The problem is, when I right-click on an empty cell, I still get the number format pane. Hence, after fooling with the syntax, trying IsNull and others, I made a intellectual guess, adding the two (currently disabled) commented lines, which makes the code work correctly--that is, not presenting the format pane on empty cells.

Being new, I wondering if this is just a fluke of Excel2000 or if any one of you also experience the same problem.

The author (famed John Walkenbach) also had something else in the SUB statement that doesn't automatically appear when I choose the BeforeRightClick event. His book had:
.... (ByVal Target As Excel.Range ....)

My prepopulated syntax excludes the word "Excel" in the above statement. I tried it both ways and it seems to work either way. Another carryover from an older version???

Kevin
PS: Hope you all are having a wonderful weekend. I'm cuddled up with allergy pills and Kleenex Cold Care. My wife is doing a TupperWare party.


Posted by Dave Hawley on May 26, 2001 9:01 PM

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

Posted by Kevin James on May 27, 2001 12:52 AM

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

Posted by Dave Hawley on May 27, 2001 2:31 AM


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

Posted by Kevin James on May 27, 2001 12:16 PM

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