I have a form and all the codes below associated with the form work correctly. I would…
- 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
- 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.HideEnd Sub
This code checks for the following validations:
- Checks if all the required fields (StartTime, EndTime, AorH) are filled. If not, displays an error message and exits the sub.
- Checks if StartTime is less than EndTime. If not, displays an error message and exits the sub.
- 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.
- Checks if either StartTime or EndTime is filled, and AorH is not blank. If true, displays an error message and exits the sub.
- 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.
- 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
- 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.HideEnd 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.
- User
How can I calculate a sheet when I leave it
- 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:
- Right-click on the sheet tab at the bottom of the Excel window and select "View Code" to open the sheet's code window.
- In the code window, paste the following code:
Private Sub Worksheet_Deactivate() Application.EnableEvents = False Me.Calculate Application.EnableEvents = True End SubThis code disables events temporarily to avoid triggering the Worksheet_Deactivate event again during the calculation. It then calculates the worksheet using the
Calculatemethod 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.