MsgBox vbYesNo Does Nothing

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
209
Can someone please tell me what I have wrong? I get the message box to pop up but nothing happens if I click on yes.
It is supposed to put a "Yes" in column AL if I click on yes but it skip over that in the code.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myLabel

i = ActiveCell.Row

If Range("AK" & i) = 1 Then

myLabel = MsgBox("Do you need a BARCODE LABEL", vbYesNo Or vbQuestion, "Labels")
    If myLabel = vbNo Then Exit Sub

End If
    If myLabel = ybYes Then
    Range("AL" & i) = "yes"
    Unload Me
End If
End Sub
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
Hi PCTech.

Think this is in the wrong forum - looks like an Excel problem.

Does this solve the problem though:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    'Is 1 entered in column AK?
    If Target.Column = 37 And _
       Target.Value = 1 Then
       
        If MsgBox("Do you need a BARCODE LABEL", vbYesNo + vbQuestion, "Labels") = _
            vbYes Then
            Target.Worksheet.Range("AL" & Target.Row) = "Yes"
        End If
    
    End If

End Sub

The problem in your code is probably:
Code:
    If myLabel = ybYes Then 
    Range("AL" & i) = "yes" 
    Unload Me
Note: ybYes instead of vbYes
 

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
209
Sorry, I didn't realize where I posted. I will post in the proper area.
For some reason, your code does not bring up the message box.
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,295
Office Version
  1. 365
Platform
  1. Windows
It should only bring up the message box if 1 is entered in column AK (i.e. column 37) as indicated by Target.
Any other value entered in that column or any other column will ignore the message box.

Note the edit in my previous post - will probably solve your problem to.

Off home now, so can't reply for another 17 hours. Sorry :cry:
 

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
209

ADVERTISEMENT

Your code works great, if I put a 1 in column AK. The fight now is the 1 comes from a coutif formula. In otherwords, if it finds a match, it puts a 1 in the cell. It is recognizing the fomula, not the 1. (I think) So I go to AM and put =Value($A$K) and change the code to look at that and it still will not trigger teh message box.

Have a good evening.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is this homework?

I answered this same exact question by another person yesterday.
 

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
209
I told a co-worker to go to MrExcel and post this question. He told me that nobody replyed so I posted this morning. Thanks for the replies.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,849
Messages
5,544,647
Members
410,627
Latest member
georgealice
Top