PDA

View Full Version : Solved: Workbook_SheetSelectionChange Event



Ciorbea
07-25-2007, 08:24 PM
Hello everybody

I've used the Workbook_SheetSelectionChange event to enable/disable a button when selecting cells in a certain column on a spreadsheet. The code works O.K. but I'm not able anymore to Copy and Paste information in the spreadsheet because the SheetSelectionChange event occurs every time I select a range (e.g. a destination cell to Paste), and cancels the Copy mode. Do you know how to solve this issue?

Thank you in advance
Ciorbea

Bob Phillips
07-26-2007, 12:34 AM
Don't use SelectionChange, maybe add a button to do it explicitly instead.

rory
07-26-2007, 01:32 AM
What code do you have in the event?

Ciorbea
07-26-2007, 05:23 AM
The code I used is:


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim mycolumn As Integer
Dim mybtn
mycolumn = ActiveCell.Column
Select Case mycolumn
Case 2
For Each mybtn In ActiveSheet.OLEObjects
mybtn.Enabled = True
Next
Case Else
For Each mybtn In ActiveSheet.OLEObjects
mybtn.Enabled = False
Next
End Select
End Sub

Bob Phillips
07-26-2007, 05:43 AM
I would have tow buttons, one to enable, one to disable, and use a macro like this



Public Sub EnableObjects()
Dim mybtn
For Each mybtn In ActiveSheet.OLEObjects
mybtn.Enabled = True
Next
End Sub

rory
07-26-2007, 06:09 AM
Do you really want this to run for every sheet in the workbook? If not, put it in the Worksheet's SelectionChange routine for the specific worksheet. It might be easier to use a check in the button code - see if activecell.Column = 2, if so run your code; if not, don't.
Incidentally, you could have rewritten your code as:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim myBtn
For Each myBtn In Sh.OLEObjects
myBtn.Enabled = (Target.Column = 2)
Next
End Sub

FWIW
Rory

Ciorbea
07-26-2007, 09:17 AM
I've used your suggestion Rory, to use a check in the button code, simple and good solution, and everything is OK now.

Thank you all,

Ciorbea

rory
07-26-2007, 09:20 AM
Glad to help. I'm a simple guy, so I like to keep things that way so I can understand them! :)
Rory