PDA

View Full Version : What am I doing wrong here?



duskwood
01-26-2018, 11:17 AM
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
01-26-2018, 11:59 AM
Does Weekday need a value to look at?

Weekday(Value,vbMonday)

duskwood
01-26-2018, 12:52 PM
So how would i declare LWeekday so that it assigns it based on what day it is?

SamT
01-26-2018, 01:09 PM
LWeekday = Weekday(Date, vbMonday) 'presents Monday as day #1

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

duskwood
01-26-2018, 01:43 PM
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
01-26-2018, 01:48 PM
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
01-26-2018, 02:09 PM
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
01-27-2018, 09:56 AM
Post your latest code.

Also let us know what the ultimate goal is.

duskwood
01-29-2018, 12:03 PM
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
01-29-2018, 12:09 PM
I hope that makes sense anyways.

SamT
01-30-2018, 09:46 AM
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
01-30-2018, 09:49 AM
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.