Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

Is it possible to change this VBA code to have a three option Pop Up Message. Yes to…

  1. User

    Is it possible to change this VBA code to have a three option Pop Up Message. Yes to call the module "ServiceProvidersHistory" No to Do nothing Open to open Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx")

    If Target.Address = "$C$2" Then
        Dim response As Integer
        response = MsgBox("This process will copy all existing completed Tasks to the next Uncompleted Task" & vbCrLf & _
                          "and paste the copy into workbook ‘Service Providers History’." & vbCrLf & _
                          "After the paste, all copied Tasks will be deleted." & vbCrLf & vbCrLf & _
                          "Would you like to continue?" & vbCrLf & vbCrLf & _
                          "This process takes about 60 seconds", vbQuestion + vbYesNo, "Confirmation")
        If response = vbYes Then
            ' Call the module "ServiceProvidersHistory"
            Call Module22.ServiceProvidersHistory
        End If
    End If
    
  2. Assistant

    Yes, it is possible to change the VBA code to have a three-option Pop Up message. Here is an updated version of the code:

    If Target.Address = "$C$2" Then
        Dim response As Integer
        response = MsgBox("This process will copy all existing completed Tasks to the next Uncompleted Task" & vbCrLf & _
                          "and paste the copy into workbook 'Service Providers History'." & vbCrLf & _
                          "After the paste, all copied Tasks will be deleted." & vbCrLf & vbCrLf & _
                          "Please choose an option:" & vbCrLf & vbCrLf & _
                          "Yes - Call the module 'ServiceProvidersHistory'" & vbCrLf & _
                          "No - Do nothing" & vbCrLf & _
                          "Open - Open 'Service Providers History.xlsx'", vbQuestion + vbYesNoCancel, "Confirmation")
        If response = vbYes Then
            ' Call the module "ServiceProvidersHistory"
            Call Module22.ServiceProvidersHistory
        ElseIf response = vbOpen Then
            Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx")
        End If
    End If
    

    In this updated code, the three options are "Yes", "No", and "Open". The user can choose to call the module "ServiceProvidersHistory" by clicking "Yes", or choose to do nothing by clicking "No". If the user clicks "Open", the "Service Providers History.xlsx" workbook will be opened.

  3. User

    the variable vbOpen is not defined

  4. Assistant

    Apologies for the confusion. The variable vbOpen doesn't exist in VBA. However, you can use a custom value to represent the "Open" option. Here's the updated code:

    If Target.Address = "$C$2" Then
        Dim response As Integer
        response = MsgBox("This process will copy all existing completed Tasks to the next Uncompleted Task" & vbCrLf & _
                          "and paste the copy into workbook 'Service Providers History'." & vbCrLf & _
                          "After the paste, all copied Tasks will be deleted." & vbCrLf & vbCrLf & _
                          "Please choose an option:" & vbCrLf & vbCrLf & _
                          "Yes - Call the module 'ServiceProvidersHistory'" & vbCrLf & _
                          "No - Do nothing" & vbCrLf & _
                          "Open - Open 'Service Providers History.xlsx'", vbQuestion + vbYesNoCancel, "Confirmation")
        If response = vbYes Then
            ' Call the module "ServiceProvidersHistory"
            Call Module22.ServiceProvidersHistory
        ElseIf response = vbRetry Then
            Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx")
        End If
    End If
    

    In this updated code, the custom value vbRetry is used for the "Open" option. If the user clicks "Open", the "Service Providers History.xlsx" workbook will be opened using Workbooks.Open method.

  5. User

    The pop up has three options Yes, No and Cancel. Yes runs the module, No and Cancel do nothing. I want No to open the workbook and Cancel to Exit the sub

  6. Assistant

    Apologies for the confusion. Here's the updated code with the desired functionality:

    If Target.Address = "$C$2" Then
        Dim response As Integer
        response = MsgBox("This process will copy all existing completed Tasks to the next Uncompleted Task" & vbCrLf & _
                          "and paste the copy into workbook 'Service Providers History'." & vbCrLf & _
                          "After the paste, all copied Tasks will be deleted." & vbCrLf & vbCrLf & _
                          "Please choose an option:" & vbCrLf & vbCrLf & _
                          "Yes - Call the module 'ServiceProvidersHistory'" & vbCrLf & _
                          "No - Open 'Service Providers History.xlsx'" & vbCrLf & _
                          "Cancel - Exit the sub", vbQuestion + vbYesNoCancel, "Confirmation")
        If response = vbYes Then
            ' Call the module "ServiceProvidersHistory"
            Call Module22.ServiceProvidersHistory
        ElseIf response = vbNo Then
            Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx")
        ElseIf response = vbCancel Then
            Exit Sub
        End If
    End If
    

    In this updated code, the "No" option opens the "Service Providers History.xlsx" workbook using Workbooks.Open method. The "Cancel" option will exit the sub.

  7. User

    If Target.Address = "CC2" Then Dim response As Integer response = MsgBox("This process will copy all existing completed Tasks to the next Uncompleted Task" & vbCrLf & _ "and paste the copy into workbook 'Service Providers History'." & vbCrLf & _ "After the paste, all copied Tasks will be deleted." & vbCrLf & vbCrLf & _ "Please choose an option:" & vbCrLf & vbCrLf & _ "Yes - Save Completed Tasks to History'" & vbCrLf & _ "No - View Saved Task History" & vbCrLf & _ "Cancel - Cancel and do Nothing", vbQuestion + vbYesNoCancel, "Confirmation") If response = vbYes Then ' Call the module "ServiceProvidersHistory" Call Module22.ServiceProvidersHistory

    ElseIf response = vbNo Then Dim sourceWB As Workbook Dim sourceWS As Worksheet Dim historyWB As Workbook Dim historyWS As Worksheet ' Set source workbook and worksheet Set sourceWB = ActiveWorkbook Set sourceWS = sourceWB.ActiveSheet

    Workbooks.Open ("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx")
    ' Activate the history workbook, select the history sheet
    Application.Wait (Now + TimeValue("0:00:5"))
    ' Select the sheet with the same name as the active sheet in the source workbook
    On Error Resume Next
    Set historyWS = historyWB.Sheets(sourceWS.Name)
    On Error GoTo 0
    
    ' If the sheet doesn't exist, create a new sheet with the same name
    If historyWS Is Nothing Then
        sourceWS.Copy After:=historyWB.Sheets(historyWB.Sheets.count)
        Set historyWS = historyWB.ActiveSheet
        historyWS.Name = sourceWS.Name
    historyWB.Activate
    historyWB.Sheets(sourceWS.Name).Select
    

    ElseIf response = vbCancel Then Exit Sub End If End If End If

    When I run the above code, the workbook "Service Providers History.xlsx" opens But I also get a vba error that says Object variable or With block variable not set on the line sourceWS.Copy After:=historyWB.Sheets(historyWB.Sheets.count)

  8. Assistant

    Apologies for the oversight. The error occurs because the "historyWB" object is not set to the opened workbook "Service Providers History.xlsx" before performing operations on it. Here's the updated code to address this issue:

    If Target.Address = "$C$2" Then
        Dim response As Integer
        response = MsgBox("This process will copy all existing completed Tasks to the next Uncompleted Task" & vbCrLf & _
                          "and paste the copy into workbook 'Service Providers History'." & vbCrLf & _
                          "After the paste, all copied Tasks will be deleted." & vbCrLf & vbCrLf & _
                          "Please choose an option:" & vbCrLf & vbCrLf & _
                          "Yes - Save Completed Tasks to History'" & vbCrLf & _
                          "No - View Saved Task History" & vbCrLf & _
                          "Cancel - Cancel and do Nothing", vbQuestion + vbYesNoCancel, "Confirmation")
        If response = vbYes Then
            ' Call the module "ServiceProvidersHistory"
            Call Module22.ServiceProvidersHistory
        ElseIf response = vbNo Then
            Dim sourceWB As Workbook
            Dim sourceWS As Worksheet
            Dim historyWB As Workbook
            Dim historyWS As Worksheet
            ' Set source workbook and worksheet
            Set sourceWB = ActiveWorkbook
            Set sourceWS = sourceWB.ActiveSheet
            
            ' Open "Service Providers History.xlsx" workbook and set the history workbook object
            Set historyWB = Workbooks.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx")
            
            ' Activate the history workbook, select the history sheet
            historyWB.Activate
            Set historyWS = historyWB.Sheets(sourceWS.Name)
            
            ' If the sheet doesn't exist, create a new sheet with the same name
            If historyWS Is Nothing Then
                sourceWS.Copy After:=historyWB.Sheets(historyWB.Sheets.Count)
                Set historyWS = historyWB.ActiveSheet
                historyWS.Name = sourceWS.Name
            End If
            
            ' Select the sheet with the same name as the active sheet in the source workbook
            historyWB.Sheets(sourceWS.Name).Select
        ElseIf response = vbCancel Then
            Exit Sub
        End If
    End If
    

    In this updated code, the "historyWB" object is set after opening the "Service Providers History.xlsx" workbook using Workbooks.Open method. This should resolve the "Object variable or With block variable not set" error.

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