Yes / No button in VBA

[Mehmud Khan]

Hi friends,

I need your help in VBA project.

I want to run the below VBA script but before it will get run, I want the excel should pop-up for Yes or No.

If I choose Yes, then the below script should run ;

Sub ExtractDataStaffWise()

Application.ScreenUpdating = False

Sheets("Staff Wise").Select
Range("A5:K10002").Select
Selection.ClearContents

Dim fPath As String, fName As String

fPath = Worksheets("MasterData").Range("D4").Text
If Right(fPath, 1) <> "\" Then fPath = fPath + "\"
fName = Worksheets("MasterData").Range("G3").Text

Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=fPath + fName, ReadOnly:=True)

Worksheets(1).Activate
ActiveSheet.Range("A2:K1000").Select
Selection.Copy
Windows("Consolidated File.xlsm").Activate
Worksheets("Staff Wise").Activate
Range("A5").Select
ActiveSheet.Paste
Range("A1").Select
Selection.Copy
Range("A1").Select

Worksheets("Staff Wise").Columns("A:K").AutoFit
Range("A4:K2000").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter

Range("A4").Select

fName = Worksheets("MasterData").Range("G3").Text

Set wb = Workbooks.Open(Filename:=fPath + fName, ReadOnly:=True)
ActiveWorkbook.Close

End With
End Sub


If I choose No, then nothing should be happan.

Thansk in advance guyzzzz...

 

[vletm]

Mehmud Khan

https://chandoo.org/forum/threads/posting-a-sample-workbook.451/

 

[Mehmud Khan]

Hi vletm,

thnks for providing the above link.
But I have so many confusion with that.

I have attached the "Test" file.
There is a button "Clear".

If I click on that button,
then before its VBA, a yes / Cancel pop-up should prompt like below;

thanks in advance sir....

 

[Belleke]

Like this?

Sub CleatData()
smessage = "Clear the cells, are you sure" + "?"
If MsgBox(smessage, vbQuestion + vbYesNo, "Confirm delete") = vbNo Then GoTo oops
Range("A2:D6").Clear
oops:
End Sub

No need to activate Sheet, because your button is on the active sheet.
Try to avoid activate and select,it is rarely needed and it slows down your code.