Is it possible to change this VBA code to have a three option Pop Up Message. Yes to…
- 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 - 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 IfIn 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.
- User
the variable vbOpen is not defined
- Assistant
Apologies for the confusion. The variable
vbOpendoesn'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 IfIn this updated code, the custom value
vbRetryis used for the "Open" option. If the user clicks "Open", the "Service Providers History.xlsx" workbook will be opened usingWorkbooks.Openmethod. - 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
- 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 IfIn this updated code, the "No" option opens the "Service Providers History.xlsx" workbook using
Workbooks.Openmethod. The "Cancel" option will exit the sub. - User
If Target.Address = "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
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).SelectElseIf 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)
- 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 IfIn this updated code, the "historyWB" object is set after opening the "Service Providers History.xlsx" workbook using
Workbooks.Openmethod. This should resolve the "Object variable or With block variable not set" error.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.