views:

348

answers:

3

Hi, I am a beginning in visual basic. What I am trying to do is whenever a box is clicked, highlight a specific range. Then, if another box is clicked after that, the previous range will unhighlight, and another range will highlight. Here is my code but it doesn't work right now.

Dim FSelect As Boolean
Dim myRange As Range

Sub Rectangle18_Click()

   If FSelect Then
        UnhighlightBox (myRange) <---error - runtime error "424" object required
   End If

   Range("C9:D9").Select
   HighlightBox

   FSelect = True

   Set myRange = Range("C9:D9")
   End Sub

Sub Rectangle19_Click()

   If FSelect Then
        UnhighlightBox (myRange)
   End If

   Range("C11:D11").Select
   HighlightBox

   FSelect = True

   Set myRange = Range("C11:D11")

End Sub    

Sub HighlightBox()
   Selection.Interior.ColorIndex = 36
End Sub

Sub UnhighlightBox(cellRng As Range)
   cellRng.Interior.ColorIndex = 2
End Sub
+1  A: 

When I throw this code into excel it complains about Select. I don't think you can use Select as a variable...

EDIT: Select is a reserved keyword in VB/A, It begins the Select Case block.

Jason Punyon
i changed the variable but it still doesn't work
+1  A: 

Putting the parentheses around the argument when calling the UnhighlightBox procedure is incorrect.

Two possible correct forms:

UnhighlightBox myRange

Call UnhighlightBox(myRange)

I find the first form (without the Call keyword) to be preferable

For the Excel 2003 help:

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist.

Please note that this does not apply to a function which returns a value. A function needs to be called as part of an assignment (e.g. a = f(x)) and the arguments must be enclosed in parentheses

Your use of the FSelect boolean (which initialises to false) should be preventing the problem with calling UnhighlightBox before myRange is ever set.

barrowc
thxs a lot!!! it works now xD
A: 

When using the UnhighlightBox routine you either need to put the Call statement before hand or remove the parenthesis.

For example: Call UnhighlightBox (myRange)

or UnhighlightBox myRange

For more info on why to use Call, etc see the either of the following:

what-does-the-call-keyword-do-in-vb6

MSDN

Jon Fournier