Search 2 criteria "Reservation panel"

[hpernaf]

Hi everyone!

I am creating a spreadsheet template where I need to schedule some machines by date as shown in the example below.
1.PNG

In a form, I need to select the date and check which machines are available and which are reserved for the selected date.

That is, I will select a date and cad Textbox should show the status "Reserved" or "Available".

2.PNG

I tried to do this using a For Next Loop, but I was only able to do it for the first Textbox.

Could someone help me in this case?

Thanks in advance.

[Marc L]

According to your attachment a VBA demonstration as a beginner starter :

Private Sub CommandButton2_Click() 
        Dim R&, Rf As Range, M%(1 To 4) 
    If Not txtDate Like "*/*/*" Then 
        For R = 1 To 4:  Controls("txtM" & R) = "":  Next 
        If txtDate > "" Then Beep 
    Else 
        With Planilha1.[A1].CurrentRegion.Columns(1) 
               Set Rf = .Find(CDate(txtDate), , , 1) 
            If Not Rf Is Nothing Then 
                       R = Rf.Row 
                Do 
                   M(Split(Rf(1, 2).Text)(1)) = 1 
                       Set Rf = .FindNext(Rf) 
                Loop Until Rf.Row = R 
                       Set Rf = Nothing 
            End If 
        End With 
            For R = 1 To 4:  Controls("txtM" & R) = Array("Available", "Reserved")(M(R)):  Next 
    End If 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Marc L]

And button #2 is usesles just with this :

Private Sub CommandButton1_Click() 
    txtDate = GetCalendário 
    txtDate_AfterUpdate 
End Sub 
 
Private Sub txtDate_AfterUpdate() 
        Dim R&, Rf As Range, M%(1 To 4) 
    If Not txtDate Like "*/*/*" Then 
        For R = 1 To 4:  Controls("txtM" & R) = "":  Next 
        If txtDate > "" Then Beep 
    Else 
        With Planilha1.[A1].CurrentRegion.Columns(1) 
               Set Rf = .Find(CDate(txtDate), , , 1) 
            If Not Rf Is Nothing Then 
                       R = Rf.Row 
                Do 
                   M(Split(Rf(1, 2).Text)(1)) = 1 
                       Set Rf = .FindNext(Rf) 
                Loop Until Rf.Row = R 
                       Set Rf = Nothing 
            End If 
        End With 
            For R = 1 To 4:  Controls("txtM" & R) = Array("Available", "Reserved")(M(R)):  Next 
    End If 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[hpernaf]

That was really perfect Marc
This code contains some things I've never seen with VBA.
Thanks for your help

[Vraag en antwoord]

Or Slightly easier and faster.

Please Login or Register  to view this content.

[Marc L]

Hi Vraag !

I need some light about your logic : If ar(j, 2) = "Machine " & jj Or Controls("txtM" & jj) = "Reserverd" Then Controls("txtM" & jj) = "Reserverd"

It seems the Or part is useless …

[Vraag en antwoord]

@Marc L, that's right. The code used to be different and this part has remained.

[hpernaf]

Marc,
I don't want to abuse your courtesy, but another question has arisen.
If I have an extra time field as shown below.

1.PNG
2.PNG

What would this code look like with this additional criteria?

[Marc L]

So with the time as criteria :

Private Sub CommandButton2_Click() 
      Const C = "txtM" 
        Dim D As Date, Rf As Range, R&, M%(1 To 4) 
    If IsDate(txtDate) And IsDate(txtTime) Then 
        With Planilha1.[A1].CurrentRegion.Columns(1) 
                D = CDate(txtDate) 
                If Len(txtDate) < 10 Then txtDate = D 
                Set Rf = .Find(D, , , 1) 
                D = CDate(txtTime) 
                If Len(txtTime) < 5 Then txtTime = Left(D, 5) 
            If Not Rf Is Nothing Then 
                   R = Rf.Row 
            Do 
                    If Rf(1, 2).Value = D Then M(Split(Rf(1, 3).Text)(1)) = 1 
                   Set Rf = .FindNext(Rf) 
            Loop Until Rf.Row = R 
                   Set Rf = Nothing 
            End If 
        End With 
            For R = 1 To 4:  Controls(C & R) = Array("Available", "Reserved")(M(R)):  Next 
    Else 
        For R = 1 To 4:  Controls(C & R) = "":  Next 
        If txtDate > "" Or txtTime > "" Then Beep 
    End If 
End Sub  

[Vraag en antwoord]

You will also need to specify an end time or minimum duration of the reservation. Otherwise, machine 1 will be available again on 15-10-2021 at 07:01.

[hpernaf]

Thank you very much!