Solved: Self referencing cells?

[cubbycub]

Hi all,

This is hard to explain but here goes...

The attached file is just a test bed in order for me to test the items I need, to create a time sheet.

I would like to select the interval period using the top spin button and then use this value to increment my start and stop time using the bottom 2 spin buttons. The fly in the proverbial ointment though is that I would like the spin button reference cell to reset once the end of the day has been reached. (see the macro below this is run as soon as the spin button is pressed).

There must be a neater method than this to reset the count. This would require a macro for each spin button.

Any help would be greatly appreciated.

[vba]Sub Macro1()
'
' Macro1 Macro
'
'
If Cells(5, 3).Value > 1 Then Cells(5, 2).Value = 1


End Sub
[/vba]

[Bob Phillips]

You could use event code

[vba]

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "B5:B10" '<== change to suit
Dim Target As Range

On Error GoTo ws_exit
Application.EnableEvents = False

For Each Target In Me.Range(WS_RANGE)
With Target

If .Offset(0, 1) >= 1 Then .Value = 1
End With
Next Target

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[cubbycub]

Thank you XLD!

That has opened a whole new range of thought for me! Thanks again

I'm going to play with that and let you know how that went later.

Im positive that this is solved now. heh heh