What am I doing wrong here?

[duskwood]

So im creating a Form control spin button that will increment one specific cell in a range based on what day of the week it is and then also based on what hour of the day it is. This is the code Im using?

'

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

Please help

[georgiboy]

Does Weekday need a value to look at?

Weekday(Value,vbMonday)

[duskwood]

So how would i declare LWeekday so that it assigns it based on what day it is?

[SamT]

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

LWeekday = Weekday(Date) 'presents Sunday as day #1

[duskwood]

So Monday is day 1 and Friday is day 5 but will it assign it 1 as a variable or will it assign whatever number the current day happens to be?

[SamT]

assign whatever number the current day happens to be?

Isn't that what you want? It makes no sense to assign Same Number to all days

X = WeekDay(DateToCheck, DayToCountAsFirstDayOfWeek)

X = WeekDay(FridayDate, CountTuesdayAsFirstDayOfWeek)
X = WeekDay("01/26/2018", vbTuesday)
X = 4 because Tuesday is presented as first day of week

[duskwood]

No thats exactly what I want. Now for the trickier part. The code as far I know is correct. However when I assign the macro to a spin control button nothing happens. Any ideas why that might be? Or even if i assign it to a button for that matter.

Fairly certain I got it working, i have to wait an hour to test it again. At least with a button, eventually id like to get to work with a spin control button that would allow it to increment AND decrease.

[SamT]

Post your latest code.

Also let us know what the ultimate goal is.

[duskwood]

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

This code currently works with a button to increment a cell by 1 based on what day it is and what time of the day it is. However instead of a button I would like to add a spin control so they can add 1 to a cell or subtract 1 to a cell if need be. That is the ultimate goal.

[duskwood]

I hope that makes sense anyways.

[SamT]

I don't know which SpinButtun you're using, but you need to use both the Change Event to note if the change is up or down and possibly the Exit Event to allow the User to change their mind before actually changing a cell. The reason is that if you use only the change event and the user make a mistake, the cell could have 1 added to it then 1 subtracted from it simply because the User first changed the button up, then changed it down.

'Module Level Code
Dim UpDown As Long

Spin Button rules. You need to write this code.
If change up then UpDown = + 1
Else if change down then UpDown = - 1

Sub SpinButton_Exit( ByVal Cancel As MSForms.ReturnBoolean)
If Weekday(Date, vbMonday) <> 1 then 
MsgBox " THis only works on Mondays"
Exit Sub 'Prevent any changes if not Monday
End if

OneClick 'Call sub
End Sub

Sub OneClick() 
Dim Cel As Range
Dim strTime as Long
    strTime = Hour(Now()) 'grabs the current hour on a 24 hour rotation

'Redundent 
If Weekday(Date, vbMonday) <> 1 then 
MsgBox " THis only works on Mondays"
Exit Sub 'Prevent any changes if not Monday
End if   
     
 Select Case strTime   
    Case 9 
        Set Cel = Range("C3")
    Case 10   
         Set Cel  = Range("C4")
    Case 11     
        Set Cel  = Range("C5")
     Case 12    
        Set Cel = Range("C6")
    Case 13, 14     
        Set Cel = Range("C7")
    Case 15     
       Set Cel  = Range("C8")
     Case 16    
      Set Cel = Range("C9")
     Case 17   
        Set Cel  = Range("C10")
      Case 18
        Set Cel  = Range("C11")
     Case Else
       UpDown = 0 'Clean up
       Exit Sub 'Prevent changes when not between 9am and 6 pm
End Select

Cel = Cel + UpDown
UpDown = 0 'Clean up
End Sub

Obviously, this is a rough draft, not tested in any way. Also, you may have to add sheet references depending of your actual setup.

[SamT]

Also, if the User does this right at the changing of the hour, you might want to set strTime in the SpinButton code. This depends on your requirements and your Business Rules.