Enable commandbutton based on time

M

macea1987

New Member
Joined
Mar 6, 2015
Messages
12
Hi Guys,

I have a cell on sheet 1 which updates every second with the current time. the format of this cell is hh:mm:ss. I then have a sheet with cells for different premises again with the format hh:mm:ss. I have a command button for each premises on a page. However, i dont want anyone to be able to click on the commandbutton for each premises unless the time now is + 20 min either side of the times on the sheet with times listed.

So if the time on the "Schedule" sheet is 09:00, i only want the button the be active if the time is within 08:40 or 09:20. The Schedule page has the different premises listed in Column A and then the scheduled times in columns B to M. Each site having 12 times against it.

Really hoping someone can help.

Thanks,

Alan
 
Sort by date Sort by votes
Ahhh confusing. :) I have pasted the entire Sheet 1 code so you can see what i have going on. When a time reaches a time on the "Schedule" page. It changes the button color and pops a message up to inform the person they have to do something. They click the button, connect to the site and commence a tour. this is a 2 min minimum connection time, so there is a 2 min timer. They can however start 20 min before the scheduled time, so they can click the button it will change colour. So the code for the reminder and things only pops up if they havent clicked on the button already. see what you think.
Code: 
Private Sub CommandButton23_Click()
'Make Changes Button
Module3.EndTimer
Dim wsheet As Worksheet
For Each wsheet In Worksheets
wsheet.Unprotect Password:="311572141980"
Next wsheet
Dim wslog As Worksheet 'Abbreviation of Worksheet is wslog
Dim ws As Worksheet 'Abbreviation of worksheet is ws
Dim lrow As Long 'Abbreviation of Long is lrow
Set ws = ThisWorkbook.Sheets("Main") ' Definition of ws
Set wslog = ThisWorkbook.Sheets("Log") ' Definition of wslog
lrow = wslog.Cells(Rows.count, "A").End(xlUp).Row + 1 ' Moves the Row Down on New Addition
wslog.Range("A" & lrow) = Now  'Date and Time Now
wslog.Range("B" & lrow) = "Make Changes Allowed" ' ' Event Name
wslog.Range("C" & lrow) = ws.Range("E1")  'Agent ID
wslog.Range("D" & lrow) = Environ("Username") 'Stamps Windows Login
End Sub

Private Sub CommandButton24_Click()
'Logoff Button
Dim answer As Integer
answer = MsgBox("Are you sure you want to logoff?", vbYesNo + vbQuestion, "Logoff") 'Prompt ok for logoff
If answer = vbYes Then
Range("AA1").ClearContents
CommandButton21.BackColor = 15592941
CommandButton22.BackColor = 15592941
CommandButton25.BackColor = 15592941
CommandButton26.BackColor = 15592941
CommandButton27.BackColor = 15592941
CommandButton28.BackColor = 15592941
CommandButton29.BackColor = 15592941
CommandButton210.BackColor = 15592941
CommandButton211.BackColor = 15592941
CommandButton212.BackColor = 15592941
CommandButton213.BackColor = 15592941
CommandButton214.BackColor = 15592941
CommandButton215.BackColor = 15592941
Dim wslog As Worksheet 'Abbreviation of Worksheet is wslog
Dim ws As Worksheet 'Abbreviation of worksheet is ws
Dim lrow As Long 'Abbreviation of Long is lrow
Set ws = ThisWorkbook.Sheets("Main") ' Definition of ws
Set wslog = ThisWorkbook.Sheets("Log") ' Definition of wslog
lrow = wslog.Cells(Rows.count, "A").End(xlUp).Row + 1 ' Moves the Row Down on New Addition
wslog.Range("A" & lrow) = Now  'Date and Time Now
wslog.Range("B" & lrow) = "Logoff" ' ' Event Name
wslog.Range("C" & lrow) = ws.Range("E1")  'Agent ID
wslog.Range("D" & lrow) = Environ("Username") 'Stamps Windows Login
Range("E1").ClearContents
ws.Select
ThisWorkbook.Save 'Saves the File
ThisWorkbook.Close 'Closes the File
Else
     'do nothing ' When they click No, nothing will happen
     End If
End Sub

Private Sub CommandButton_Click()
Dim wslog As Worksheet 'Abbreviation of Worksheet is wslog
Dim ws As Worksheet 'Abbreviation of worksheet is ws
Dim lrow As Long 'Abbreviation of Long is lrow
Dim connect As Integer
Set ws = ThisWorkbook.Sheets("Main") ' Definition of ws
Set wslog = ThisWorkbook.Sheets("Log") ' Definition of wslog
Set wslogr = ThisWorkbook.Sheets("Results") ' Definition of wslogr
lrow = wslogr.Cells(Rows.count, "A").End(xlUp).Row + 1 ' Moves the Row Down on New Addition
If wslogr.Buttons(Application.Caller).BackColor = 15592941 Then
    With Application.WorksheetFunction
         If .Lookup(wslog.Range("Y42").Value + TimeValue("00:20:00"),  .Index(ws.Range("B2:H200"),  .Match(wslogr.Buttons(Application.Caller).Caption, ws.Range("A2:A200"),  False), 0)) <= wslog.Range("Y42").Value + TimeValue("00:20:00") Then
            MsgBox "error, not in permitted time frame"
            Exit Sub
        End If
    End With
End If
wslogr.Range("B" & lrow) = Now  'Date and Time Now
wslogr.Range("A" & lrow) = wslogr.Buttons(Application.Caller).Caption  'Site Name
wslogr.Range("D" & lrow) = ws.Range("E1")  'Agent ID
wslogr.Range("E" & lrow) = Environ("Username") 'Stamps Windows Login
connect = MsgBox("Are you able to Connect to the Site?", vbYesNo + vbQuestion, "Site Status")
If connect = vbNo Then
wslogr.Range("C" & lrow) = "Cannot Connect"
wslogr.Buttons(Application.Caller).BackColor = vbRed
ThisWorkbook.Save
Else
wslogr.Buttons(Application.Caller).BackColor = vbGreen
StartGT
wslogr.Buttons(Application.Caller).BackColor = vbYellow
ThisWorkbook.Save
End If
End Sub


End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set ws = ThisWorkbook.Sheets("Schedule")
Set wslog = ThisWorkbook.Sheets("Main")
Dim areyouready As Integer
Dim thisrow As Long
    Dim lr As Long


If  CommandButton21.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B2") Or wslog.Range("Y42").Value = ws.Range("C2") Or  wslog.Range("Y42").Value = ws.Range("D2") Or wslog.Range("Y42").Value =  ws.Range("E2") Or wslog.Range("Y42").Value = ws.Range("F2") Or  wslog.Range("Y42").Value = ws.Range("G2") Or wslog.Range("Y42").Value =  ws.Range("H2") Then
CommandButton21.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton21.BackColor = 15592941 And wslog.Range("G1") = "Saturday"  And wslog.Range("Y42").Value = ws.Range("I2") Or  wslog.Range("Y42").Value = ws.Range("J2") Or wslog.Range("Y42").Value =  ws.Range("K2") Or wslog.Range("Y42").Value = ws.Range("L2") Or  wslog.Range("Y42").Value = ws.Range("M2") Then
CommandButton21.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton21.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I2") Or  wslog.Range("Y42").Value = ws.Range("J2") Or wslog.Range("Y42").Value =  ws.Range("K2") Or wslog.Range("Y42").Value = ws.Range("L2") Or  wslog.Range("Y42").Value = ws.Range("M2") Then
CommandButton21.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton22.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B3") Or wslog.Range("Y42").Value = ws.Range("C3") Or  wslog.Range("Y42").Value = ws.Range("D3") Or wslog.Range("Y42").Value =  ws.Range("E3") Or wslog.Range("Y42").Value = ws.Range("F3") Or  wslog.Range("Y42").Value = ws.Range("G3") Or wslog.Range("Y42").Value =  ws.Range("H3") Then
CommandButton22.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton22.BackColor = 15592941 And wslog.Range("G1") = "Saturday"  And wslog.Range("Y42").Value = ws.Range("I3") Or  wslog.Range("Y42").Value = ws.Range("J3") Or wslog.Range("Y42").Value =  ws.Range("K3") Or wslog.Range("Y42").Value = ws.Range("L3") Or  wslog.Range("Y42").Value = ws.Range("M3") Then
CommandButton22.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton22.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I3") Or  wslog.Range("Y42").Value = ws.Range("J3") Or wslog.Range("Y42").Value =  ws.Range("K3") Or wslog.Range("Y42").Value = ws.Range("L3") Or  wslog.Range("Y42").Value = ws.Range("M3") Then
CommandButton22.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton25.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B4") Or wslog.Range("Y42").Value = ws.Range("C4") Or  wslog.Range("Y42").Value = ws.Range("D4") Or wslog.Range("Y42").Value =  ws.Range("E4") Or wslog.Range("Y42").Value = ws.Range("F4") Or  wslog.Range("Y42").Value = ws.Range("G4") Or wslog.Range("Y42").Value =  ws.Range("H4") Then
CommandButton25.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton25.BackColor = 15592941 And wslog.Range("G1") = "Saturday"  And wslog.Range("Y42").Value = ws.Range("I4") Or  wslog.Range("Y42").Value = ws.Range("J4") Or wslog.Range("Y42").Value =  ws.Range("K4") Or wslog.Range("Y42").Value = ws.Range("L4") Or  wslog.Range("Y42").Value = ws.Range("M4") Then
CommandButton25.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton25.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I4") Or  wslog.Range("Y42").Value = ws.Range("J4") Or wslog.Range("Y42").Value =  ws.Range("K4") Or wslog.Range("Y42").Value = ws.Range("L4") Or  wslog.Range("Y42").Value = ws.Range("M4") Then
CommandButton25.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton26.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B5") Or wslog.Range("Y42").Value = ws.Range("C5") Or  wslog.Range("Y42").Value = ws.Range("D5") Or wslog.Range("Y42").Value =  ws.Range("E5") Or wslog.Range("Y42").Value = ws.Range("F5") Or  wslog.Range("Y42").Value = ws.Range("G5") Or wslog.Range("Y42").Value =  ws.Range("H5") Then
CommandButton26.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton26.BackColor = 15592941 And wslog.Range("G1") = "Saturday"  And wslog.Range("Y42").Value = ws.Range("I5") Or  wslog.Range("Y42").Value = ws.Range("J5") Or wslog.Range("Y42").Value =  ws.Range("K5") Or wslog.Range("Y42").Value = ws.Range("L5") Or  wslog.Range("Y42").Value = ws.Range("M5") Then
CommandButton26.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton26.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I5") Or  wslog.Range("Y42").Value = ws.Range("J5") Or wslog.Range("Y42").Value =  ws.Range("K5") Or wslog.Range("Y42").Value = ws.Range("L5") Or  wslog.Range("Y42").Value = ws.Range("M5") Then
CommandButton26.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton27.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B6") Or wslog.Range("Y42").Value = ws.Range("C6") Or  wslog.Range("Y42").Value = ws.Range("D6") Or wslog.Range("Y42").Value =  ws.Range("E6") Or wslog.Range("Y42").Value = ws.Range("F6") Or  wslog.Range("Y42").Value = ws.Range("G6") Or wslog.Range("Y42").Value =  ws.Range("H6") Then
CommandButton27.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton27.BackColor = 15592941 And wslog.Range("G1") = "Saturday"  And wslog.Range("Y42").Value = ws.Range("I6") Or  wslog.Range("Y42").Value = ws.Range("J6") Or wslog.Range("Y42").Value =  ws.Range("K6") Or wslog.Range("Y42").Value = ws.Range("L6") Or  wslog.Range("Y42").Value = ws.Range("M6") Then
CommandButton27.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton27.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I6") Or  wslog.Range("Y42").Value = ws.Range("J6") Or wslog.Range("Y42").Value =  ws.Range("K6") Or wslog.Range("Y42").Value = ws.Range("L6") Or  wslog.Range("Y42").Value = ws.Range("M6") Then
CommandButton27.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton28.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B7") Or wslog.Range("Y42").Value = ws.Range("C7") Or  wslog.Range("Y42").Value = ws.Range("D7") Or wslog.Range("Y42").Value =  ws.Range("E7") Or wslog.Range("Y42").Value = ws.Range("F7") Or  wslog.Range("Y42").Value = ws.Range("G7") Or wslog.Range("Y42").Value =  ws.Range("H7") Then
CommandButton28.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton28.BackColor = 15592941 And wslog.Range("G1") = "Saturday"  And wslog.Range("Y42").Value = ws.Range("I7") Or  wslog.Range("Y42").Value = ws.Range("J7") Or wslog.Range("Y42").Value =  ws.Range("K7") Or wslog.Range("Y42").Value = ws.Range("L7") Or  wslog.Range("Y42").Value = ws.Range("M7") Then
CommandButton28.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton28.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I7") Or  wslog.Range("Y42").Value = ws.Range("J7") Or wslog.Range("Y42").Value =  ws.Range("K7") Or wslog.Range("Y42").Value = ws.Range("L7") Or  wslog.Range("Y42").Value = ws.Range("M7") Then
CommandButton28.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton29.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B8") Or wslog.Range("Y42").Value = ws.Range("C8") Or  wslog.Range("Y42").Value = ws.Range("D8") Or wslog.Range("Y42").Value =  ws.Range("E8") Or wslog.Range("Y42").Value = ws.Range("F8") Or  wslog.Range("Y42").Value = ws.Range("G8") Or wslog.Range("Y42").Value =  ws.Range("H8") Then
CommandButton29.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton29.BackColor = 15592941 And wslog.Range("G1") = "Saturday"  And wslog.Range("Y42").Value = ws.Range("I8") Or  wslog.Range("Y42").Value = ws.Range("J8") Or wslog.Range("Y42").Value =  ws.Range("K8") Or wslog.Range("Y42").Value = ws.Range("L8") Or  wslog.Range("Y42").Value = ws.Range("M8") Then
CommandButton29.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton29.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I8") Or  wslog.Range("Y42").Value = ws.Range("J8") Or wslog.Range("Y42").Value =  ws.Range("K8") Or wslog.Range("Y42").Value = ws.Range("L8") Or  wslog.Range("Y42").Value = ws.Range("M8") Then
CommandButton29.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton210.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B9") Or wslog.Range("Y42").Value = ws.Range("C9") Or  wslog.Range("Y42").Value = ws.Range("D9") Or wslog.Range("Y42").Value =  ws.Range("E9") Or wslog.Range("Y42").Value = ws.Range("F9") Or  wslog.Range("Y42").Value = ws.Range("G9") Or wslog.Range("Y42").Value =  ws.Range("H9") Then
CommandButton210.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton210.BackColor = 15592941 And wslog.Range("G1") =  "Saturday" And wslog.Range("Y42").Value = ws.Range("I9") Or  wslog.Range("Y42").Value = ws.Range("J9") Or wslog.Range("Y42").Value =  ws.Range("K9") Or wslog.Range("Y42").Value = ws.Range("L9") Or  wslog.Range("Y42").Value = ws.Range("M9") Then
CommandButton210.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton210.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I9") Or  wslog.Range("Y42").Value = ws.Range("J9") Or wslog.Range("Y42").Value =  ws.Range("K9") Or wslog.Range("Y42").Value = ws.Range("L9") Or  wslog.Range("Y42").Value = ws.Range("M9") Then
CommandButton210.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton211.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B10") Or wslog.Range("Y42").Value = ws.Range("C10") Or  wslog.Range("Y42").Value = ws.Range("D10") Or wslog.Range("Y42").Value =  ws.Range("E10") Or wslog.Range("Y42").Value = ws.Range("F10") Or  wslog.Range("Y42").Value = ws.Range("G10") Or wslog.Range("Y42").Value =  ws.Range("H10") Then
CommandButton211.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton211.BackColor = 15592941 And wslog.Range("G1") =  "Saturday" And wslog.Range("Y42").Value = ws.Range("I10") Or  wslog.Range("Y42").Value = ws.Range("J10") Or wslog.Range("Y42").Value =  ws.Range("K10") Or wslog.Range("Y42").Value = ws.Range("L10") Or  wslog.Range("Y42").Value = ws.Range("M10") Then
CommandButton211.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton211.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I10") Or  wslog.Range("Y42").Value = ws.Range("J10") Or wslog.Range("Y42").Value =  ws.Range("K10") Or wslog.Range("Y42").Value = ws.Range("L10") Or  wslog.Range("Y42").Value = ws.Range("M10") Then
CommandButton211.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton212.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B11") Or wslog.Range("Y42").Value = ws.Range("C11") Or  wslog.Range("Y42").Value = ws.Range("D11") Or wslog.Range("Y42").Value =  ws.Range("E11") Or wslog.Range("Y42").Value = ws.Range("F11") Or  wslog.Range("Y42").Value = ws.Range("G11") Or wslog.Range("Y42").Value =  ws.Range("H11") Then
CommandButton212.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton212.BackColor = 15592941 And wslog.Range("G1") =  "Saturday" And wslog.Range("Y42").Value = ws.Range("I11") Or  wslog.Range("Y42").Value = ws.Range("J11") Or wslog.Range("Y42").Value =  ws.Range("K11") Or wslog.Range("Y42").Value = ws.Range("L11") Or  wslog.Range("Y42").Value = ws.Range("M11") Then
CommandButton212.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton212.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I11") Or  wslog.Range("Y42").Value = ws.Range("J11") Or wslog.Range("Y42").Value =  ws.Range("K11") Or wslog.Range("Y42").Value = ws.Range("L11") Or  wslog.Range("Y42").Value = ws.Range("M11") Then
CommandButton212.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton213.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B12") Or wslog.Range("Y42").Value = ws.Range("C12") Or  wslog.Range("Y42").Value = ws.Range("D12") Or wslog.Range("Y42").Value =  ws.Range("E12") Or wslog.Range("Y42").Value = ws.Range("F12") Or  wslog.Range("Y42").Value = ws.Range("G12") Or wslog.Range("Y42").Value =  ws.Range("H12") Then
CommandButton213.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton213.BackColor = 15592941 And wslog.Range("G1") =  "Saturday" And wslog.Range("Y42").Value = ws.Range("I12") Or  wslog.Range("Y42").Value = ws.Range("J12") Or wslog.Range("Y42").Value =  ws.Range("K12") Or wslog.Range("Y42").Value = ws.Range("L12") Or  wslog.Range("Y42").Value = ws.Range("M12") Then
CommandButton213.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton213.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I12") Or  wslog.Range("Y42").Value = ws.Range("J12") Or wslog.Range("Y42").Value =  ws.Range("K12") Or wslog.Range("Y42").Value = ws.Range("L12") Or  wslog.Range("Y42").Value = ws.Range("M12") Then
CommandButton213.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If


If  CommandButton214.BackColor = 15592941 And wslog.Range("Y42").Value =  ws.Range("B13") Or wslog.Range("Y42").Value = ws.Range("C13") Or  wslog.Range("Y42").Value = ws.Range("D13") Or wslog.Range("Y42").Value =  ws.Range("E13") Or wslog.Range("Y42").Value = ws.Range("F13") Or  wslog.Range("Y42").Value = ws.Range("G13") Or wslog.Range("Y42").Value =  ws.Range("H13") Then
CommandButton214.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton214.BackColor = 15592941 And wslog.Range("G1") =  "Saturday" And wslog.Range("Y42").Value = ws.Range("I13") Or  wslog.Range("Y42").Value = ws.Range("J13") Or wslog.Range("Y42").Value =  ws.Range("K13") Or wslog.Range("Y42").Value = ws.Range("L13") Or  wslog.Range("Y42").Value = ws.Range("M13") Then
CommandButton214.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If
If  CommandButton214.BackColor = 15592941 And wslog.Range("G1") = "Sunday"  And wslog.Range("Y42").Value = ws.Range("I13") Or  wslog.Range("Y42").Value = ws.Range("J13") Or wslog.Range("Y42").Value =  ws.Range("K13") Or wslog.Range("Y42").Value = ws.Range("L13") Or  wslog.Range("Y42").Value = ws.Range("M13") Then
CommandButton214.BackColor = vbBlue
areyouready = MsgBox("Tour Due", vbOKOnly + vbQuestion, "Site Name (Site Ref)")
End If




'Make Changes Button Enabled
If  Range("AA1").Value = "user.name1" Or Range("AA1").Value = "user.name2"  Then 'If ID is Person 1 or Person 2 then it will allow Make Changes  Button
           CommandButton23.Enabled = True
                                 Else
           CommandButton23.Enabled = False
                      End If
                                           End Sub
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Double confusing lol.

Might be best to ignore the code I posted earlier.

Rich (BB code): 
    With Application.WorksheetFunction
        If .Lookup(wslog.Range("Y42").Value + TimeValue("00:20:00"), .Index(ws.Range("table_of_times"), .Match(site_name, ws.Range("site_list"), False), 0)) <= wslog.Range("Y42").Value + TimeValue("00:20:00") Then
            Exit Sub
        End If
    End With

The line above is the part that I think will help you, where I've used descriptions as placeholders in the code, you will need to assign the relevant ranges in your schedule sheet and the name of the site to compare.

At the moment, looking at your code, my head feels like it's about to implode, I'll start digging through it later to see if I can reduce the number of repeated similar lines, for example by dumping a list of button names to an array, then looping through that instead of listing each one individually.
 
Upvote 0
Sorry to be dumb. where abouts would i put the above code? and do i need to remove anything from my existing code? I really do appreciate you looking for me.
 
Upvote 0
Let me change the code slightly

Code: 
    With Application.WorksheetFunction
        If .Lookup(wslog.Range("Y42").Value + TimeValue("00:20:00"), .Index(ws.Range("table_of_times"), .Match(site_name, ws.Range("site_list"), False), 0)) <= wslog.Range("Y42").Value + TimeValue("00:20:00") Then
        Else
            Exit Sub
        End If
    End With

Make the relevant changes based on the red placeholders in my previous post.

Ideally it would go close to the beginning of the relevant code so that when the button is clicked, it would check this before doing anything else, if the time frame doesn't match then the code will end.

Note that any variables used in this line, i.e. the worksheet defined as ws, will need to be declared and defined first to prevent it erroring.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
VBA Run-time Errors
Replies
3
Views
513
StephenCrump
S
S
  • Solved
VBA Add Formula to varying cell reference
Replies
6
Views
495
SpicyItalian
S
Z
  • Question
Center Across Selection Issue in Web App.
Replies
0
Views
106
Z01NK5
Z
J
  • Solved
VBA to calculate and conditional format cell
Replies
6
Views
193
rollis13
rollis13
B
  • Question
Looking for a way to automate scheduling duos into different squads
Replies
0
Views
144
bsweet0us
B

Forum statistics

Threads
1,214,834
Messages
6,121,873
Members
449,056
Latest member
ruhulaminappu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top