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

I have a series of interacting VBA codes which are not completeing and I would like your…

  1. 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").Activate
    

    End 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

  2. 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 Me
    

    End 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 CopyMatchingContinue
    

    End 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.

  3. 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

  4. 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 Sub
    

    By 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.

  5. User

    error in the new line say Method or data member not found

  6. 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 Sub
    

    And 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 Sub
    

    By declaring the mIsSubmitted variable as Private in the form module and accessing it directly from CopyMatchingValues, 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.

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