vbYesNo to populate cell

Phoenix333

New Member
Joined
Sep 28, 2010
Messages
26
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

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,797
vbYes is a constant that is equal to 6. Try this
Code:
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

New Member
Joined
Sep 28, 2010
Messages
26
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!
 

Forum statistics

Threads
1,082,151
Messages
5,363,437
Members
400,737
Latest member
vipamuk

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top