yasanthax
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To address the other part of the question do
To only run if the selected cell is in Range("A1:D7")
If Not Intersect(Target, Range("A1:D7")) Is Nothing Then
...
End If
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly answered question as stated and in addition added error handling
2. What else do uyou recommend?
Can I see your workbook?