Hi. I have a simple macro that copies a small range and pastes to a different workbook. Before pasting to the destination, I want it to ask the User if they have populated something in the Source workbook. If they have, progress to pasting, if not, go back to Source and fill in required data. How do I write that VBA code? Any help would be really great. Thank you.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Ask user a Question before going to next step in macro
- Thread starter dk
- Start date
Hi DK,
try the msgbox control..
Sub YourRoutine()
'do the codes to copy the range
Dim bFlag As Boolean
bFlag = MsgBox("Have you filled the mandatory info at source?", vbQuestion + vbYesNo, "Confirm")
If bFlag = vbYes Then
'code to paste your data
Else
' code to activate your source screen for entering the data.
'note: you may need to either above function as loop for checking again
End If
'continue with code of what you want further here
End Sub
Hope this gives you some idea.
Regards,
Prasad
try the msgbox control..
Sub YourRoutine()
'do the codes to copy the range
Dim bFlag As Boolean
bFlag = MsgBox("Have you filled the mandatory info at source?", vbQuestion + vbYesNo, "Confirm")
If bFlag = vbYes Then
'code to paste your data
Else
' code to activate your source screen for entering the data.
'note: you may need to either above function as loop for checking again
End If
'continue with code of what you want further here
End Sub
Hope this gives you some idea.
Regards,
Prasad
Shadow Jam
New Member
Hi dk,
Do you want to tell them they need to fill it in, or ask them a question? You say "required" so its probably better to give a simple message box rather than a yes no choice maybe.
Do you want to tell them they need to fill it in, or ask them a question? You say "required" so its probably better to give a simple message box rather than a yes no choice maybe.
Thank you very much, that helps a lot!! It's not completely working just yet however. Whether VBAnswer is "Yes" or "No", it goes to the source data and stops. I'm not quite sure what part of the loop I am missing? Here's my code:
'do the codes to copy the range
Range("C1:C42").Select
Selection.Copy
Dim bFlag As Boolean
bFlag = MsgBox("Have you filled in the CAP-X in the Buyer's?", vbQuestion + vbYesNo, "Confirm")
If bFlag = vbYes Then
'code to paste your data
Workbooks.Open Filename:= _
"DestinationFile.xlsx"
Range("A1").Select
Else
'code to activate your source screen for entering the data.
'note: you may need to either above function as loop for checking again
Sheets("Source").Select
Range("L313").Select
End If
'continue with code of what you want further here
End Sub
'Thanks
'do the codes to copy the range
Range("C1:C42").Select
Selection.Copy
Dim bFlag As Boolean
bFlag = MsgBox("Have you filled in the CAP-X in the Buyer's?", vbQuestion + vbYesNo, "Confirm")
If bFlag = vbYes Then
'code to paste your data
Workbooks.Open Filename:= _
"DestinationFile.xlsx"
Range("A1").Select
Else
'code to activate your source screen for entering the data.
'note: you may need to either above function as loop for checking again
Sheets("Source").Select
Range("L313").Select
End If
'continue with code of what you want further here
End Sub
'Thanks
Change Dim line to:
[pre]
[/pre]
Asa
[pre]
Code:
Dim bFlag As Long
should resolve it, at first look.
Alternatively, you could do without the flag variable entirely if you want, by combining the MsgBox call and the If command (although after the End If you won't be able to refer to the user's choice again):
If MsgBox("Have you filled in the CAP-X in the Buyer's?", vbQuestion + vbYesNo, "Confirm") = vbYes ThenAsa