Tab Selection from Userform based on Date range

[campbell1093]

Hello all, I am working on a monthly tracker for work where I have a userform that allows the user to enter data. My last goal with this is to select a month tab based on entered data in the userform.

For context, right now I have to manually select the month I want to enter the data to, then activate my userform, and allow it to enter that way. I am trying to make VBA select the correct monthly tab based on the entered data and input the information there.

I can't attach a sample due to my admin restrictions, but I can try to attach my code. I'm guessing the relevant code would go somewhere with the "AddEntry" function of my code.

I am also open to suggestions and input on how to better this worksheet.

Private Sub AddEntry_Click()
Dim msg As String




If CaseBox.Value = "" Then
    CaseBox.SetFocus
    CaseBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If ComplaintBox.Value = "" Then
    ComplaintBox.SetFocus
    ComplaintBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If OffenseBox.Value = "" Then
    OffenseBox.SetFocus
    OffenseBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If LevelSelect.Value = "" Then
    LevelSelect.SetFocus
    LevelSelect.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If ReportedBox.Value = "" Then
    ReportedBox.SetFocus
    ReportedBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If SuspectBox.Value = "" Then
    SuspectBox.SetFocus
    SuspectBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If DispositionBox.Value = "" Then
    DispositionBox.SetFocus
    DispositionBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If DDateBox.Value = "" Then
    DDateBox.SetFocus
    DDateBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


If ActionBox.Value = "" Then
    ActionBox.SetFocus
    ActionBox.BackColor = RGB(255, 125, 125)
    Exit Sub
    Else
End If


msg = MsgBox("Are You Sure?", vbYesNo + vbQuestion, "Add Entry")
 
    If msg = vbNo Then
        Exit Sub
    Else
        
End If
    
    ActiveSheet.Select
    
    Range("A1").End(xlDown).Offset(1, 0).Select
      
    ActiveCell.Value = CaseBox.Value
    ActiveCell.Offset(0, 1).Value = ComplaintBox.Value
    ActiveCell.Offset(0, 2).Value = OffenseBox.Value
    ActiveCell.Offset(0, 3).Value = LevelSelect.Value
    ActiveCell.Offset(0, 4).Value = ReportedBox.Value
    ActiveCell.Offset(0, 5).Value = SuspectBox.Value
    ActiveCell.Offset(0, 6).Value = DispositionBox.Value
    ActiveCell.Offset(0, 7).Value = DDateBox.Value
    ActiveCell.Offset(0, 8).Value = ActionBox.Value
    ActiveCell.Offset(0, 9).Value = AsOfBox.Value
    
    
    
    CaseBox.Value = ""
    ComplaintBox.Value = ""
    OffenseBox.Value = ""
    LevelSelect.Value = ""
    ReportedBox.Value = ""
    SuspectBox.Value = ""
    DispositionBox.Value = ""
    DDateBox.Value = ""
    ActionBox.Value = ""
    AsOfBox.Value = Date
    
End Sub

 

[dmt32]

Hi,
try replacing this part of your code

ActiveSheet.Select
    
    Range("A1").End(xlDown).Offset(1, 0).Select
      
    ActiveCell.Value = CaseBox.Value
    ActiveCell.Offset(0, 1).Value = ComplaintBox.Value
    ActiveCell.Offset(0, 2).Value = OffenseBox.Value
    ActiveCell.Offset(0, 3).Value = LevelSelect.Value
    ActiveCell.Offset(0, 4).Value = ReportedBox.Value
    ActiveCell.Offset(0, 5).Value = SuspectBox.Value
    ActiveCell.Offset(0, 6).Value = DispositionBox.Value
    ActiveCell.Offset(0, 7).Value = DDateBox.Value
    ActiveCell.Offset(0, 8).Value = ActionBox.Value
    ActiveCell.Offset(0, 9).Value = AsOfBox.Value

with this

Dim NextRow     As Long
    
    With ThisWorkbook.Worksheets(MonthName(Month(Date), False))
        
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        
        With .Cells(NextRow, 1)
            .Value = CaseBox.Value
            .Offset(0, 1).Value = ComplaintBox.Value
            .Offset(0, 2).Value = OffenseBox.Value
            .Offset(0, 3).Value = LevelSelect.Value
            .Offset(0, 4).Value = ReportedBox.Value
            .Offset(0, 5).Value = SuspectBox.Value
            .Offset(0, 6).Value = DispositionBox.Value
            .Offset(0, 7).Value = DDateBox.Value
            .Offset(0, 8).Value = ActionBox.Value
            .Offset(0, 9).Value = AsOfBox.Value
        End With
    End With

I have assumed that your tab names are January, February etc but if they are abbreviated Jan, Feb then change False shown in BOLD to True.

I am also open to suggestions and input on how to better this worksheet.

You may notice that I have not used Select in updated code as You almost never have to use select when using VBA


Hope helpful

Dave

 

[campbell1093]

Works perfectly, thank you!

 

[dmt32]

Works perfectly, thank you!

most welcome & very much appreciate your feedback

Good luck with your project

Dave

 

[campbell1093]

Spoke too soon...

So for clarification, my entries are as titled:
Case Number
Complainant
Offense
Level
Date (Of Offense)
Suspect
Disposition
Disposition Date
Action
"As Of" Date


It seems the new code is selecting based on the "As Of" Date when I would like it to select the tab based on the Date (Of Offense) value. The date entered would be MM/DD/YYYY value

 

[campbell1093]

Answered my own question with a minor tweak:


In case anyone else searches the same or similar question, I have attached the tweak made, where instead of (Month(Date), False) it now selects based on the desired entry from ReportedDate

Dim NextRow As Long
    
    With ThisWorkbook.Worksheets(MonthName(Month([B]ReportedBox.Value[/B]), False))
        
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        
        With .Cells(NextRow, 1)
            .Value = CaseBox.Value
            .Offset(0, 1).Value = ComplaintBox.Value
            .Offset(0, 2).Value = OffenseBox.Value
            .Offset(0, 3).Value = LevelSelect.Value
            .Offset(0, 4).Value = ReportedBox.Value
            .Offset(0, 5).Value = SuspectBox.Value
            .Offset(0, 6).Value = DispositionBox.Value
            .Offset(0, 7).Value = DDateBox.Value
            .Offset(0, 8).Value = ActionBox.Value
            .Offset(0, 9).Value = AsOfBox.Value
        End With
    End With