CODEWITHSUNDEEP
excelvba
Gehn47
Gehn47

Reputation: 93

Increment specific cell based on time and day

So im creating a spin button that connects to a macro. The button would increment a cell by 1 based on the hour of the day and what day of the week it is.

The code im using is: UPDATE

Sub OneClick() 

strTime = Hour(Now()) 
Dim LWeekday As Integer 




LWeekday = Weekday(vbMonday) 


If strTime = 10 And LWeekday = 5 Then 
    Range("K4").Value = Range("K4") + 1 


ElseIf strTime = 11 And LWeekday = 5 Then 
    Range("K5").Value = Range("K5") + 1 




End If 


 'Select Case OneClickTest




 '
 '   Case strTime = 8 And LWeekday = 5
 '       Range("K2").Value = Range("K2") + 1


 '    Case strTime = 9 And LWeekday = 5
 '       Range("K3").Value = Range("K3") + 1




 'End Select

 End Sub

However im having two issues. The first is i dont think ive coded it right to get the hour of the day and the second is it only allows the spin control to increment, would i have to create a 2nd button to decrease the number by one?

Any help is appreciated.

Upvotes: 0

Views: 158

Answers (2)

Gehn47
Gehn47

Reputation: 93

Here is the final working code.

Sub OneClick()
strTime = Hour(Now()) 'grabs the current hour on a 24 hour rotation
Dim LWeekday As Integer

LWeekday = Weekday(Date, vbMonday) 'Set Monday as day 1



If strTime = 9 And LWeekday = 1 Then
 Range("C3").Value = Range("C3") + 1

ElseIf strTime = 10 And LWeekday = 1 Then
 Range("C4").Value = Range("C4") + 1

ElseIf strTime = 11 And LWeekday = 1 Then
 Range("C5").Value = Range("C5") + 1

ElseIf strTime = 12 And LWeekday = 1 Then
 Range("C6").Value = Range("C6") + 1

ElseIf strTime = 13 And LWeekday = 1 Then
 Range("C7").Value = Range("C7") + 1

ElseIf strTime = 14 And LWeekday = 1 Then
 Range("C7").Value = Range("C7") + 1

ElseIf strTime = 15 And LWeekday = 1 Then
 Range("C8").Value = Range("C8") + 1

ElseIf strTime = 16 And LWeekday = 1 Then
 Range("C9").Value = Range("C9") + 1

ElseIf strTime = 17 And LWeekday = 1 Then
 Range("C10").Value = Range("C10") + 1

ElseIf strTime = 18 And LWeekday = 1 Then
 Range("C11").Value = Range("C11") + 1




End If

End Sub

Upvotes: 0

teylyn
teylyn

Reputation: 35935

If your button is an ActiveX spin button, use the events related to the button, like SpinButton1_SpinDown() and SpinButton1_SpinUp().

If the button is a forms control, then you will have a cell linked to the button where the click results in a change of value. Use that cell value in your code.

Upvotes: 1

Related Questions