• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Yes / No button in VBA

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

Attachments

Belleke

Active Member
Like this?
Code:
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.
 
Top