PDA

View Full Version : Solved: helpwith WorkSheet Selection_ change & command button



Rob342
10-23-2010, 10:25 AM
Hi
I have some code in a WorkSheet Selection_Change (ByVal Target as Range)
On the same sheet i have 2 command buttons, 1 changes the cell values and the other to clear the contents of a range of cells.

I have been debugging the code on the command button for clearing the data and after a coulpe of lines of code it goes into the Selection_change event.
Is this supposed to happen or can i stop it from going into the change event everytime i click on the clear data button ?
Here is the code i have got.
Select Change Button

Dim Block As Boolean
Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
If Not Block Then
'// Check whether Q3 has a value 1st if 0 then reset
'Application.ScreenUpdating = False
If Range("Q3").Value = 0 Then Range("H26").Value = 0
If Range("Q3").Value = 0 Then Range("H14") = 0
If Range("Q3").Value = 0 Then Range("H23") = " "
If Range("Q3").Value > 0 And Range("H14").Value >= 0 _
Then Range("H26") = 50
If Range("Q3").Value > 0 And Range("H14").Value <> 0 _
Then Range("H26").Value = Range("H14").Value
'Application.ScreenUpdating = True
End If
End Sub

Clear Data Button

Sub CommandButton1_Click()
'// Delete all data fields except top line leave this as default
Application.ScreenUpdating = False
Range("E5").Select
ActiveCell.FormulaR1C1 = " "
Range("J5").Select
ActiveCell.FormulaR1C1 = " "
Range("K5").Select
ActiveCell.FormulaR1C1 = " "
Range("L5").Select
ActiveCell.FormulaR1C1 = " "
Range("Q5").Select
ActiveCell.FormulaR1C1 = " "
Range("R5").Select
ActiveCell.FormulaR1C1 = " "
Range("B9:G9").Select
'// Send curser back to start position ok
Application.ScreenUpdating = True
End Sub

Any help would be appreciated
Rob342

Bob Phillips
10-23-2010, 10:57 AM
Take out all of the selects



Private Sub CommandButton1_Click()
'// Delete all data fields except top line leave this as default
Application.ScreenUpdating = False
Range("E5").Value = ""
Range("J5").Value = ""
Range("K5").Value = ""
Range("L5").Value = ""
Range("Q5").Value = ""
Range("R5").Value = ""
'// Send curser back to start position ok
Application.ScreenUpdating = True
End Sub

Rob342
10-23-2010, 12:50 PM
Hi
XLD

Many thanks, works at treat now
For future reference, was it the ".select" in contention With Selection_change ?

Thanks for your time
Regards
Rob342

mdmackillop
10-23-2010, 04:10 PM
Changing Selection triggers the SelectionChange macro. XLD.s change removes this trigger. You can also use Application.EnableEvents=False to prevent such looping for SheetChange events and the like.

Rob342
10-24-2010, 03:48 AM
Thanks mdmackillop
I will remeber it for future use
Rob342