SpinButton Problem
 

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

SpinButton Problem
 

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

SpinButton Problem
 

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

Regards,
Greg

SpinButton Problem
 

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

SpinButton Problem
 

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

SpinButton Problem
 

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

SpinButton Problem
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if target.count 1 then exit sub
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
Case Else
.OLEObjects("SpinButton1").Enabled = False
.OLEObjects("SpinButton2").Enabled = False
End Select
End With
End Sub

--
Regards,
Tom Ogilvy


Pete Csiszar wrote in message
news:2mFIb.884694$6C4.234972@pd7tw1no...
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

SpinButton Problem
 

Thanks Tom,
With your assistance I have taken another step in refining my spreadsheet
program.

Happy New Year!

Best regards,
Pete


"Tom Ogilvy" wrote in message
...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if target.count 1 then exit sub
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
Case Else
.OLEObjects("SpinButton1").Enabled = False
.OLEObjects("SpinButton2").Enabled = False
End Select
End With
End Sub

--
Regards,
Tom Ogilvy


Pete Csiszar wrote in message
news:2mFIb.884694$6C4.234972@pd7tw1no...
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