Hi All,

I've got a little dilemma that has been driving me nuts.
I have two SpinButtons on my Sheet1, what I want to do is make each button
active or enabled when I click on a specific cell.
e.g. only Enable SpinButton1 when I click on E9 and only enable SpinButton2
when I click on E12.

I have this working quite well except for one condition. This condition is
when the value of spin1 and spin2 are the same, then for some reason they
both go active regardless whether I click E9 or E12.

I have been call the spin enable sub from the following: Public Sub
Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Excel.Range)

I've put in everything to make sure that buttons are only enabled when I'm
in E9 or E12 and it works under every other condition but matched values?

Any guidance would be greatly appreciated.
If more details are required please let me know.

Best regards,
Pete

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
With ActiveSheet
Select Case Target.Address
Case "$E$9"
.OLEObjects("SpinButton1").Enabled = True
.OLEObjects("SpinButton2").Enabled = False
Case "$E$12"
.OLEObjects("SpinButton1").Enabled = False
.OLEObjects("SpinButton2").Enabled = True
End Select
End With

End Sub

Regards,
Greg

PS:
The code should be placed in the Sheet(Sheet1) code
module, not the Thisworkbook code module.

Regards,
Greg

Thanks Greg this works like a charm!
Much more efficient all around than my clumsy attempt.

Happy New Year!

Pete

"Greg Wilson" wrote in message
...
PS:
The code should be placed in the Sheet(Sheet1) code
module, not the Thisworkbook code module.

Regards,
Greg

I thought you wanted them both disabled if any other cells was selected.
Apparently not.

--
Regards,
Tom Ogilvy

"pcsis" wrote in message
news:7%rIb.49462$6b2.15674@edtnps84...
Thanks Greg this works like a charm!
Much more efficient all around than my clumsy attempt.

Happy New Year!

Pete

"Greg Wilson" wrote in message
...
PS:
The code should be placed in the Sheet(Sheet1) code
module, not the Thisworkbook code module.

Regards,
Greg

Thanks for check Tom,

I actually did / do want the buttons disabled when click elsewhere.
When I used Greg's code with a sub that I already had in place, I got the
desired action.
If you have a suggestion for incorporating that feature in to what Greg has
done, I would be most appreciative.

Best regards,
Pete

"Tom Ogilvy" wrote in message
...
I thought you wanted them both disabled if any other cells was selected.
Apparently not.

--
Regards,
Tom Ogilvy

"pcsis" wrote in message
news:7%rIb.49462$6b2.15674@edtnps84...
Thanks Greg this works like a charm!
Much more efficient all around than my clumsy attempt.

Happy New Year!

Pete

"Greg Wilson" wrote in message
...
PS:
The code should be placed in the Sheet(Sheet1) code
module, not the Thisworkbook code module.

Regards,
Greg