Need Solution for Macro Error

[madhuchelliah]

Hello Guys, I am using the below mentioned macro in one of the sheet of my workbook. While running the macros in modules an error is thrown by this macro. I don't know what is the reason. If anyone know the solution or any altenate way to use this code please help.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Range(Cells(ActiveCell.Row, 3), Cells(ActiveCell.Row, 24)).Select
End Sub

 

[Norie]

What's the error message and what is the code meant to do?

 

[madhuchelliah]

It is the regular error message with options End and Debug. BTW the code is to select the range of cells in a row if i click any cell in that row.

 

[Rick Rothstein]

It is the regular error message with options End and Debug. BTW the code is to select the range of cells in a row if i click any cell in that row.

When Norie said "What's the error message?" he did not mean for you to describe what the MessageBox looked like... what he wanted you to tell us is what was the error number and error description in that MessageBox.

As for selecting the entire row when any cell in the row is clicked, you can use this worksheet event code as a starting point (restricting the range for the permitted selection and extent of the selection as needed)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Target.EntireRow.Select
End Sub

 

[madhuchelliah]

Thanks Rick. Need one more clarification. Is there any macros to activate or deactivate woksheet event codes whenever i required

 

[Rick Rothstein]

Thanks Rick. Need one more clarification. Is there any macros to activate or deactivate woksheet event codes whenever i required

Can you describe your needs in a little more detail? For example, are you talking about stopping all event codes from running or only the one I posted? Or are you talking about restricting the range of cells over which the event code I posted should be active? Or are you talking about which cells should be highlighted rather than the whole range? Or are you talking about something else entirely? The more detail you give us, the better able we are to respond.

 

[madhuchelliah]

I appreciate your reply. I have a bunch of codes in my process. The worksheet event which i mentioned is to view my final result. With this code i can easily read the data of the row. After adding this event whenever i run my macros an error message popup. If i remove that code my macros running smoothly. That is why i asked you for activate and deactivate options. If these options available i will activate at the end of my process. I want to select from c to y column if i click any cell in a row. Hope you can understand. Thanks again.

 

[Rick Rothstein]

After adding this event whenever i run my macros an error message popup.

That is probably because your macros make use of selecting cells or ranges before doing something with them. That is a coding technique you should avoid or, better still, eliminate completely from your coding repertoire. For example, if you find yourself coding something like this...

Range(..).Select
Selection.SomeProperty = ...

you can replace it with something like this...

Range(..).SomeProperty = ...

This makes sense if you think about it. What is Selection? It is the range you selected... they are the same thing. So, if you can call a property or method of the Selection, then you can call that same property or method from the original object used to create the Selection, namely, the original range that was selected. If you do it that way, no selection event procedure code will interfere with your macro code.

That is why i asked you for activate and deactivate options. If these options available i will activate at the end of my process. I want to select from c to y column if i click any cell in a row. Hope you can understand. Thanks again.

Since I don't expect you to go back and rewrite all of your macros to do what I have posted above, here is new event procedures to replace the one I gave you earlier that will enable/disable the event procedure as needed. Note that I also restrict the selection to Columns C through Y. Replace the code I gave you earlier with this one...

Dim FullRowSelection As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  FullRowSelection = Not FullRowSelection
  Cancel = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If FullRowSelection Then Intersect(Target.EntireRow, Columns("C:Y")).Select
End Sub

Note that the isolated Dim statement must always appear at the top of the module above any other code that is in that module. To use this code, simply double-click any cell on the worksheet whose code module you install it in... doing that will toggle the selection to the opposite state it is currently in.

 

[madhuchelliah]

Hello Rick, the code is working cool !!. I tried to automate the double click with Application.DoubleClick its not working. Do you have any idea on this?

 

[Rick Rothstein]

Hello Rick, the code is working cool !!. I tried to automate the double click with Application.DoubleClick its not working. Do you have any idea on this?

DoubleClick is an event, not a property or method... the only way to implement a reaction to double clicking is the way I outlined in my posted code.