I have a series of interacting VBA codes which are not completeing and I would like your…
- User
I have a series of interacting VBA codes which are not completeing and I would like your help in solving the issue. In my sheet Overtime, I have a button that calls this code; Sub CopyMatchingValues() Dim mIsSubmitted As Boolean Dim StaffForm As New StaffForm1 ' Show the UserForm as a modal dialog StaffForm.Show vbModal If Not mIsSubmitted Then Exit Sub Call CopyMatchingContinue End Sub
The Form Opens, and this is the code for the Form; Private mIsSubmitted As Boolean Public Property Get IsSubmitted() As Boolean IsSubmitted = mIsSubmitted End Property Private Sub Label1_Click() End Sub Private Sub StaffSubmit_Click() mIsSubmitted = True ' Check if all text boxes are filled If FullName.Text = "" Or JobTitle.Text = "" Or ContType.Text = "" Or WorkHrs.Text = "" Then MsgBox "Please fill in all the fields.", vbExclamation Exit Sub End If ' Copy values from text boxes to 'OTForm' sheet ThisWorkbook.Sheets("OTForm").Range("B4").Value = FullName.Text ThisWorkbook.Sheets("OTForm").Range("B7").Value = JobTitle.Text ThisWorkbook.Sheets("OTForm").Range("E4").Value = ContType.Text ThisWorkbook.Sheets("OTForm").Range("E5").Value = WorkHrs.Text ThisWorkbook.Sheets("OTForm").Calculate ThisWorkbook.Sheets("OTFormX").Calculate ThisWorkbook.Sheets("OTFormX").Range("B7").Calculate ' Close the UserForm Unload Me End Sub Private Sub StaffCancel_Click() mIsSubmitted = False ' Close the UserForm Me.Hide End Sub
When I click the SUBMIT button in the form, it copies the relevant inputs to the sheet 'OTForm'. On SUBMIT, it is also supposed to run the following code in my sheet, which iti is not doing.
Sub CopyMatchingContinue() Dim OvertimeSheet As Worksheet Dim OTFormSheet As Worksheet Dim OvertimeRange As Range Dim OTFormRange As Range Dim OvertimeCell As Range Dim OTFormCell As Range Dim LastRow As Long Dim i As Long Dim matchCount As Long Dim calledCopyMatchingXValues As Boolean ' variable to track if 'CopyMatchingXValues' was called ' Create an instance of the UserForm Set OvertimeSheet = ThisWorkbook.Sheets("Overtime") On Error Resume Next Set OvertimeRange = Application.InputBox("Select range in column C", Type:=8) On Error GoTo 0 If OvertimeRange Is Nothing Or Not OvertimeRange.Columns(1).Column = 3 Then MsgBox "No valid range selected in column C.", vbCritical Exit Sub End If Set OTFormSheet = ThisWorkbook.Sheets("OTForm") Set OTFormRange = OTFormSheet.Range("B4") LastRow = 11 matchCount = 0 calledCopyMatchingXValues = False ' initialize to False
For Each OvertimeCell In OvertimeRange If OvertimeCell.Value = OTFormRange.Value Then matchCount = matchCount + 1 If matchCount > 11 Then calledCopyMatchingXValues = True ' set to True if 'CopyMatchingXValues' is called Exit For End If OTFormSheet.Cells(LastRow, "D").Value = OvertimeCell.Offset(0, 28).Value OTFormSheet.Cells(LastRow, "C").Value = OvertimeCell.Offset(0, 27).Value OTFormSheet.Cells(LastRow, "B").Value = OvertimeCell.Offset(0, 1).Value OTFormSheet.Cells(LastRow, "A").Value = OvertimeCell.Offset(0, -2).Value LastRow = LastRow + 1 End If Next OvertimeCell If calledCopyMatchingXValues Then ' only clear the values if 'CopyMatchingXValues' was called OTFormSheet.Range("D11:D" & LastRow - 1).ClearContents OTFormSheet.Range("C11:C" & LastRow - 1).ClearContents OTFormSheet.Range("B11:B" & LastRow - 1).ClearContents OTFormSheet.Range("A11:A" & LastRow - 1).ClearContents End If If matchCount > 11 Then Call CopyMatchingXValues Exit Sub End If OTFormSheet.Range("I24").Value = Date OTFormSheet.Range("I25").Value = Date Dim message As String message = OTFormSheet.Range("B4").Value & "'s overtime dates have been entered" MsgBox message, vbInformation, "OVERTIME ENTRY" ThisWorkbook.Sheets("OTForm").ActivateEnd Sub
Sub CopyMatchingXValues() MsgBox "Values found exceed space allocated for standard sheet, Extended Overtime Sheet will be used" Dim OvertimeSheet As Worksheet Dim OTFormXSheet As Worksheet Dim OvertimeRange As Range Dim OTFormXRange As Range Dim OvertimeCell As Range Dim OTFormXCell As Range Dim LastRow As Long Dim i As Long Set OvertimeSheet = ThisWorkbook.Sheets("Overtime") On Error Resume Next Set OvertimeRange = Application.InputBox("Select range in column C", Type:=8) On Error GoTo 0 If OvertimeRange Is Nothing Or Not OvertimeRange.Columns(1).Column = 3 Then MsgBox "No valid range selected in column C.", vbCritical Exit Sub End If Set OTFormXSheet = ThisWorkbook.Sheets("OTFormX") Set OTFormXRange = OTFormXSheet.Range("B4") LastRow = 11 For Each OvertimeCell In OvertimeRange If OvertimeCell.Value = OTFormXRange.Value Then OTFormXSheet.Cells(LastRow, "D").Value = OvertimeCell.Offset(0, 28).Value OTFormXSheet.Cells(LastRow, "C").Value = OvertimeCell.Offset(0, 27).Value OTFormXSheet.Cells(LastRow, "B").Value = OvertimeCell.Offset(0, 1).Value OTFormXSheet.Cells(LastRow, "A").Value = OvertimeCell.Offset(0, -2).Value LastRow = LastRow + 1 End If Next OvertimeCell OTFormXSheet.Range("I45").Value = Date OTFormXSheet.Range("I46").Value = Date Dim message As String message = OTFormXSheet.Range("B4").Value & "'s overtime dates have been entered" MsgBox message, vbInformation, "OVERTIME ENTRY" ThisWorkbook.Sheets("OTFormX").Activate End Sub
- Assistant
The first issue I noticed is that you have not assigned the value of the IsSubmitted property in the StaffSubmit_Click event. You need to set the mIsSubmitted variable to True after validating the form fields. Modify the StaffSubmit_Click event as follows:
Private Sub StaffSubmit_Click() ' Check if all text boxes are filled If FullName.Text = "" Or JobTitle.Text = "" Or ContType.Text = "" Or WorkHrs.Text = "" Then MsgBox "Please fill in all the fields.", vbExclamation Exit Sub End If
' Copy values from text boxes to 'OTForm' sheet ThisWorkbook.Sheets("OTForm").Range("B4").Value = FullName.Text ThisWorkbook.Sheets("OTForm").Range("B7").Value = JobTitle.Text ThisWorkbook.Sheets("OTForm").Range("E4").Value = ContType.Text ThisWorkbook.Sheets("OTForm").Range("E5").Value = WorkHrs.Text ThisWorkbook.Sheets("OTForm").Calculate ThisWorkbook.Sheets("OTFormX").Calculate ThisWorkbook.Sheets("OTFormX").Range("B7").Calculate mIsSubmitted = True ' Set IsSubmitted property to True ' Close the UserForm Unload MeEnd Sub
Now, let's move to the CopyMatchingValues subroutine. The issue seems to be with the line "If Not mIsSubmitted Then Exit Sub". Since mIsSubmitted is a Boolean variable, it will always have a value (either True or False). Instead of checking if it is not True, you should check if it is False. Modify the CopyMatchingValues subroutine as follows:
Sub CopyMatchingValues() Dim mIsSubmitted As Boolean Dim StaffForm As New StaffForm1
' Show the UserForm as a modal dialog StaffForm.Show vbModal If Not StaffForm.IsSubmitted Then Exit Sub Call CopyMatchingContinueEnd Sub
Now, when you click the SUBMIT button in the form, it will set the IsSubmitted property to True, and the CopyMatchingContinue subroutine will be called.
Please try these modifications and let me know if it solves the issue.
- User
This line of code 'If Not StaffForm.IsSubmitted Then Exit Sub' in the event 'Sub CopyMatchingValues() Dim mIsSubmitted As Boolean Dim StaffForm As New StaffForm1 ' Show the UserForm as a modal dialog StaffForm.Show vbModal If Not StaffForm.IsSubmitted Then Exit Sub Call CopyMatchingContinue End Sub' is causing a system error
- Assistant
Apologies for the confusion. It seems that the IsSubmitted property is not being recognized correctly in this case. Instead of using the IsSubmitted property, we can modify the code to directly access the mIsSubmitted variable in the form module.
Please modify the CopyMatchingValues subroutine as follows:
Sub CopyMatchingValues() Dim StaffForm As New StaffForm1 ' Show the UserForm as a modal dialog StaffForm.Show vbModal If Not StaffForm.mIsSubmitted Then Exit Sub Call CopyMatchingContinue End SubBy accessing the mIsSubmitted variable directly from the form module, you should avoid the system error.
Please give it a try and let me know if you encounter any further issues.
- User
error in the new line say Method or data member not found
- Assistant
Apologies for the confusion. In order to access the mIsSubmitted variable from the form module, we need to declare it with the "Public" keyword instead of "Private".
Please modify the form module (StaffForm1) as follows:
Private mIsSubmitted As Boolean Public Property Get IsSubmitted() As Boolean IsSubmitted = mIsSubmitted End Property Private Sub StaffSubmit_Click() ' Check if all text boxes are filled If FullName.Text = "" Or JobTitle.Text = "" Or ContType.Text = "" Or WorkHrs.Text = "" Then MsgBox "Please fill in all the fields.", vbExclamation Exit Sub End If ' Copy values from text boxes to 'OTForm' sheet ThisWorkbook.Sheets("OTForm").Range("B4").Value = FullName.Text ThisWorkbook.Sheets("OTForm").Range("B7").Value = JobTitle.Text ThisWorkbook.Sheets("OTForm").Range("E4").Value = ContType.Text ThisWorkbook.Sheets("OTForm").Range("E5").Value = WorkHrs.Text ThisWorkbook.Sheets("OTForm").Calculate ThisWorkbook.Sheets("OTFormX").Calculate ThisWorkbook.Sheets("OTFormX").Range("B7").Calculate mIsSubmitted = True ' Set mIsSubmitted variable to True ' Close the UserForm Unload Me End Sub Private Sub StaffCancel_Click() mIsSubmitted = False ' Close the UserForm Me.Hide End SubAnd then modify the CopyMatchingValues subroutine as follows:
Sub CopyMatchingValues() Dim StaffForm As New StaffForm1 ' Show the UserForm as a modal dialog StaffForm.Show vbModal If Not StaffForm.mIsSubmitted Then Exit Sub Call CopyMatchingContinue End SubBy declaring the mIsSubmitted variable as
Privatein the form module and accessing it directly fromCopyMatchingValues, you should be able to access it without any errors.Please give it a try and let me know if the issue persists or if you have any further questions.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.