Link to home
Start Free TrialLog in
Avatar of yasanthax
yasanthaxFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Worksheet Selection Change - however slowing down spreadsheet

I currently have a excel form which users input date
1.  Users have to fill in mamdatory data in first sheet.
2.  At the abocve sheet users choose "systems", and this will open unhide extra sheets to be filled in.
3.  When choosing system (more than 1) some combinations are immendiatelty defaulted if 2 systems go together,
2&3 use Worksheet Selection Change  macro,  This runs the macro everytime you move cells anywhere in the sheet.    This is slowing down the sopreadsheet, and using a curser to move down can be very slow.

1.  is there a way of slectio change cabn apply to only cells moved within a region.
2.  What else do uyou recommend?

Start of my code eg


If Range("J20").Value = "Summit XXX" Then
Sheets("Summit 3.83").Visible = False
Sheets("Summit 3.75").Visible = True
Sheets("Murex").Visible = False
Sheets("Martini").Visible = False
Sheets("TOMS").Visible = True
'Sheets("Paris (Hierarchy)").Visible = True
Range("F20").Value = "X"
Range("F34").Value = "X"
Range("J34").Value = "Yes"

ElseIf Range("J20").Value = "Summit XXY" Then
Sheets("Summit 3.83").Visible = False
Sheets("Summit 3.75").Visible = True
Sheets("Murex").Visible = False
Sheets("Martini").Visible = False

etc....... many lines.


Thanks
SOLUTION
Avatar of Tracy
Tracy
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
1.  is there a way of slectio change cabn apply to only cells moved within a region.
2.  What else do uyou recommend?

Can I see your workbook?
To address the other part of the question do
If Not Intersect(Target, Range("A1:D7")) Is Nothing Then
...
End If

Open in new window


To only run if the selected cell is in Range("A1:D7")
So something like this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:D7")) Is Nothing Then
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  'All the code goes here
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of yasanthax

ASKER

Exactly answered question as stated and in addition added error handling