Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

I have a form and all the codes below associated with the form work correctly. I would…

  1. User

    I have a form and all the codes below associated with the form work correctly. I would like to add validation controls to the input in the Text Boxes. The Input box 'StartTime' must always be less than Input Box 'EndTime' If there is a value in'StartTime' Then Input Box 'EndTime' must not be blank. If there is a value in'EndTime' Then Input Box 'StartTime' must not be blank. If there is a value in either 'StarTime' or 'EndTime' Then Input Box 'AorH' must be Blank. If the InputBox 'AorH' is not Blank then Values in InputBox 'AorH' can only be either 'a' or 'h' If the InputBox 'AorH' is not Blank then the InputBoxes 'StarTime' or 'EndTime' must be Blank.

    Private newActiveCellRange As Range

    Public Property Let activeCellRange(rng As Range) Set newActiveCellRange = rng End Property

    Public Property Get activeCellRange() As Range Set activeCellRange = newActiveCellRange End Property

    Private Sub UserForm_Initialize() Me.activeCellRange = ActiveCell End Sub

    Private Sub StartEndButton1_Click() newActiveCellRange.Offset(0, 29).Value = StartTime.Value newActiveCellRange.Offset(0, 30).Value = EndTime.Value newActiveCellRange.Offset(0, 32).Value = AorH.Value newActiveCellRange.Offset(0, 2).Value = FullName.Value newActiveCellRange.Offset(0, 31).Calculate newActiveCellRange.Offset(0, 33).Calculate newActiveCellRange.Offset(0, 1).Calculate newActiveCellRange.Offset(0, 3).Select MsgBox "Details recorded, Enter Reason", vbInformation, "OVERTIME HOURS" Me.Hide End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range) Set activeCellRange = Target End Sub

  2. Assistant

    To add validation controls to the input in the Text Boxes, you can use the following code:

    Private Sub StartEndButton1_Click() If StartTime.Value = "" Or EndTime.Value = "" Or AorH.Value = "" Then MsgBox "Please fill in all the required fields", vbExclamation, "Validation Error" Exit Sub End If

    If StartTime.Value >= EndTime.Value Then
        MsgBox "Start Time must be less than End Time", vbExclamation, "Validation Error"
        Exit Sub
    End If
    
    If (StartTime.Value <> "" And EndTime.Value = "") Or (StartTime.Value = "" And EndTime.Value <> "") Then
        MsgBox "Both Start Time and End Time must be filled", vbExclamation, "Validation Error"
        Exit Sub
    End If
    
    If (StartTime.Value <> "" Or EndTime.Value <> "") And AorH.Value <> "" Then
        MsgBox "If Start Time or End Time is filled, AorH must be blank", vbExclamation, "Validation Error"
        Exit Sub
    End If
    
    If AorH.Value <> "" And AorH.Value <> "a" And AorH.Value <> "h" Then
        MsgBox "AorH can only be 'a' or 'h'", vbExclamation, "Validation Error"
        Exit Sub
    End If
    
    newActiveCellRange.Offset(0, 29).Value = StartTime.Value
    newActiveCellRange.Offset(0, 30).Value = EndTime.Value
    newActiveCellRange.Offset(0, 32).Value = AorH.Value
    newActiveCellRange.Offset(0, 2).Value = FullName.Value
    newActiveCellRange.Offset(0, 31).Calculate
    newActiveCellRange.Offset(0, 33).Calculate
    newActiveCellRange.Offset(0, 1).Calculate
    newActiveCellRange.Offset(0, 3).Select
    MsgBox "Details recorded, Enter Reason", vbInformation, "OVERTIME HOURS"
    Me.Hide
    

    End Sub

    This code checks for the following validations:

    1. Checks if all the required fields (StartTime, EndTime, AorH) are filled. If not, displays an error message and exits the sub.
    2. Checks if StartTime is less than EndTime. If not, displays an error message and exits the sub.
    3. Checks if either StartTime or EndTime is filled. If one is filled and the other is blank, displays an error message and exits the sub.
    4. Checks if either StartTime or EndTime is filled, and AorH is not blank. If true, displays an error message and exits the sub.
    5. Checks if AorH is not blank and not equal to 'a' or 'h'. If true, displays an error message and exits the sub. If all validations pass, it assigns the values to the respective cells and performs the necessary calculations.
  3. User

    In your code we a re missing the situation where if StartTime is Blank and EndTime is Blank then AorH should have a value

  4. Assistant

    Apologies for the oversight. Here's an updated version of the code that includes the additional validation:

    Private Sub StartEndButton1_Click() If StartTime.Value = "" And EndTime.Value = "" And AorH.Value = "" Then MsgBox "Please fill in either Start Time and End Time or AorH", vbExclamation, "Validation Error" Exit Sub End If

    If StartTime.Value <> "" And EndTime.Value <> "" Then
        If StartTime.Value >= EndTime.Value Then
            MsgBox "Start Time must be less than End Time", vbExclamation, "Validation Error"
            Exit Sub
        End If
    End If
    
    If (StartTime.Value <> "" And EndTime.Value = "") Or (StartTime.Value = "" And EndTime.Value <> "") Then
        MsgBox "Both Start Time and End Time must be filled", vbExclamation, "Validation Error"
        Exit Sub
    End If
    
    If (StartTime.Value <> "" Or EndTime.Value <> "") And AorH.Value <> "" Then
        MsgBox "If Start Time or End Time is filled, AorH must be blank", vbExclamation, "Validation Error"
        Exit Sub
    End If
    
    If AorH.Value <> "" And AorH.Value <> "a" And AorH.Value <> "h" Then
        MsgBox "AorH can only be 'a' or 'h'", vbExclamation, "Validation Error"
        Exit Sub
    End If
    
    newActiveCellRange.Offset(0, 29).Value = StartTime.Value
    newActiveCellRange.Offset(0, 30).Value = EndTime.Value
    newActiveCellRange.Offset(0, 32).Value = AorH.Value
    newActiveCellRange.Offset(0, 2).Value = FullName.Value
    newActiveCellRange.Offset(0, 31).Calculate
    newActiveCellRange.Offset(0, 33).Calculate
    newActiveCellRange.Offset(0, 1).Calculate
    newActiveCellRange.Offset(0, 3).Select
    MsgBox "Details recorded, Enter Reason", vbInformation, "OVERTIME HOURS"
    Me.Hide
    

    End Sub

    In this updated code, I have added an additional check at the beginning of the sub to ensure that if both StartTime and EndTime are blank, AorH should have a value. If this condition is not met, it displays an error message and exits the sub.

  5. User

    How can I calculate a sheet when I leave it

  6. Assistant

    To calculate a worksheet when you leave it (i.e., when you switch to a different worksheet or close the workbook), you can use the Worksheet_Deactivate event. Here's an example of how you can use this event to calculate the worksheet:

    1. Right-click on the sheet tab at the bottom of the Excel window and select "View Code" to open the sheet's code window.
    2. In the code window, paste the following code:
    Private Sub Worksheet_Deactivate()
        Application.EnableEvents = False
        Me.Calculate
        Application.EnableEvents = True
    End Sub
    

    This code disables events temporarily to avoid triggering the Worksheet_Deactivate event again during the calculation. It then calculates the worksheet using the Calculate method and re-enables events.

    Now, whenever you switch to a different sheet or close the workbook, the worksheet's calculation will be triggered and executed.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.