vbYesNo to populate cell

[Phoenix333]

HI! I have a weird thing happening and i'm hoping someone can help fix it.

When I select a specific worksheet, I want the macro to look at B8 and if it's empty, ask me the msg box.

The code I have does this, but when I select yes, it populates the cell with a 6, not Yes and when I select No, it populates the cell with a 7, instead of No.

Here's the code I'm using

Sub Worksheet_Activate()
    Dim Adhoc As String
        If Range("B8").Value > 0 Then Exit Sub Else
        
        Adhoc = MsgBox("Is this an Adhoc project?", vbYesNo, Adhoc)
        Range("B8").Value = Adhoc

End Sub

The only things I'm finding when I search this online are vbYesNo to call macro, end macro, pop a message box, but nothing that populates a cell with the Yes or No answer.

I have tried adding code that says
If Adhoc = Yes then Range("B8").value = Yes...etc etc, but that doesn't work either.

Any ideas where i'm messing this up?

Thanks!
T-

 

[mikerickson]

vbYes is a constant that is equal to 6. Try this

Sub Worksheet_Activate()
    Dim Adhoc As Long
        If Range("B8").Value > 0 Then Exit Sub Else
        
        Adhoc = MsgBox("Is this an Adhoc project?", vbYesNo, vbNullString)
        Range("B8").Value = "No"
        If Adhoc = vbYes Then
            Range("B8").Value = "Yes"
        End If

End Sub

 

[Phoenix333]

YES!!! I'm so excited! Awesome! Thank you so much! Fabulous!

This was perfect, it does exactly what I need. Thank you thank you thank you!