SpinButton Problem

  • Thread starter Thread starter Pete Csiszar
  • Start date Start date
P

Pete Csiszar

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
 
I thought you wanted them both disabled if any other cells was selected.
Apparently not.
 
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
 
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
 
Thanks Tom,
With your assistance I have taken another step in refining my spreadsheet
program.

Happy New Year!

Best regards,
Pete
 
Back
Top