Forum Discussion

Greg Bonaparte's avatar
Greg Bonaparte
Iron Contributor
Nov 09, 2018

yes no Selection button on a msgbox

I have the following code: 

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Range(" U30:U400"), Target) Is Nothing) Then
MsgBox "TURN OFF AUTO CALCULATE AFTER ALL 'ADDITIONAL SHARES' ARE ENTERED", vbInformation, " U R G E N T A L E R T "
End If
End Sub

 

Rather than generate an "ok" box I would rather have yes or no. 

 

example: msgbox Are you finished entering additional shares? 

if "no" then msgbox continues to pop up. If "yes" then the macro below will run:

 

Sub ManualCalculate()
'
' ManualCalculate Macro
'

'
Application.Calculation = xlManual
End Sub

 

Thank you in advance for you help with this.

 

 

 

 

Macros and VBA
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Nov 10, 2018

    Hi Greg Bonaparte

     

    Try this...

    Private Sub Worksheet_Change(ByVal Target As Range)



If Not (Application.Intersect(Range(" U30:U400"), Target) Is Nothing) Then

    
    
  If MsgBox("HAVE YOU ENTERED ALL 'ADDITIONAL SHARES' ?", vbQuestion + vbYesNo, " U R G E N T A L E R T ") = vbYes Then
  
     Call ManualCalculate
  
  End If
  

End If

End Sub

Resources