Question about msgBox

[mfarres]

I'm trying to create a form using msgBox function with a Yes No option. When user clicks "Yes" in the msgBox I want a certain value appear on an specific cell in the spreadsheet.

So far I got:
Select Case Response
Case Is = vbYes
then something to make the value I want appear on the cell I want
Case Is = vbNo
Nothing happens, the msgBox just closes
End Select

Can you help me to create those two actions?
Thanks

 

[jim may]

Here's one possibility

Sub tester()
Dim myvalue As Double 'big assumption here
myvalue = 999.99
res = MsgBox("Do you want to enter your number?", vbYesNoCancel)
If res = vbYes Then
Range("B6").Value = myvalue
End If
If res = vbNo Or res = vbCancel Then
Exit Sub
End If
End Sub

 

[DonkeyOte]

repeating Jim's code but using your original Select Case approach:

Dim myvalue As Double  'big assumption here
myvalue = 999.99
res = MsgBox("Do you want to enter your number?", vbYesNoCancel)
'(note you could use just vbYesNo as MsgBox type)
Select Case Response
     Case vbYes
          Range("b6").Value = myvalue
     Case Else          
          Exit Sub 'if required
End Select