MsgBox vbYesNo Does Nothing

PCTech

Board Regular
Joined
Mar 24, 2005
Messages
210
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
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
210
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,296
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
210

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
210
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.
 

Forum statistics

Threads
1,140,941
Messages
5,703,292
Members
421,290
Latest member
java

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top