Advice for code to run when i see MsgBox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,910
Office Version
  1. 2007
Platform
  1. Windows
Morning.

The code in use is shown below "partial snippet"
The piece of code that i am interested in is shown in RED

I am watching the screen as i type values in the userform & pressing yes / no to MsgBoxes etc etc.

I see a MsgBox "HAS THE SECURITY MARK BEEN APPLIED" & when i press either YES or NO i see the worksheet in the background update.
It is NOW i wish the code in REd below to also run.

Ive placed the code in various places but i only see it run when i leave the userform.


Rich (BB code):
On Error Resume Next
         
With ThisWorkbook.Worksheets("POSTAGE").Cells(lastrow + 1, 4).Comment
        .shape.Autoshapetype = msoShapeRoundedRectangle
        .shape.TextFrame.Characters.Font.Name = "Times Roman" ' FONT FAMILY STYLE
        .shape.TextFrame.Characters.Font.Size = 12 ' TEXT SIZE
        .shape.TextFrame.Characters.Font.ColorIndex = 5 ' TEXT COLOR
        .shape.LINE.ForeColor.RGB = RGB(0, 0, 0) ' ARROW & LINE COLOR
        .shape.Fill.Visible = msoTrue
        .shape.Fill.ForeColor.RGB = RGB(255, 255, 255) ' FILL COLOR
        .shape.TextFrame.AutoSize = True
        
    End With
On Error GoTo 0
       
         

Dim colorHTML As String, r As String, g As String, b As String

        If MsgBox("HAS THE SECURITY MARK BEEN APPLIED ?", vbYesNo + vbExclamation, "PINK SECURITY MARK MESSAGE") = vbYes Then
        TextBox1.Value = ""
        TextBox2.Value = ""
        TextBox3.Value = ""
        TextBox4.Value = ""
        TextBox6.Value = ""
        TextBox9.Value = ""
        TextBox10.Value = ""
        .Cells(lastrow + 1, 11).Value = "YES"
        Application.ScreenUpdating = True
Else
        .Cells(lastrow + 1, 11).Value = "NO"
        Application.ScreenUpdating = True
        
        On Error GoTo 10
        For Each Cell In Range("D:D").SpecialCells(xlCellTypeComments)
        Cell.Value = "COMMENT MSG"
        Next Cell
        Exit Sub
10:
MsgBox "NO COMMENTS MSG WERE FOUND IN COLUMN D", vbCritical, "COMMENTS MSG ERROR MESSAGE"

        Application.Goto Sheets("POSTAGE").Range("B" & Rows.Count).End(xlUp), True
        
End If

err:
 Const FILE_PATH As String = "C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\"
    If ActiveCell.Column = Columns("B").Column Then
 
    If Len(Dir(FILE_PATH & ActiveCell.Value & ".jpg")) Then
        ActiveCell.Hyperlinks.Add Anchor:=ActiveCell, Address:=FILE_PATH & ActiveCell.Value & ".jpg"
        MsgBox "CUSTOMER PHOTO HYPERLINK WAS SUCCESSFUL.", vbInformation, "POSTAGE SHEET HYPERLINK MESSAGE"

        
        End If
        
        Else
        MsgBox "PLEASE SELECT A CUSTOMER FIRST TO HYPERLINK THE PHOTO.", vbCritical, "POSTAGE SHEET HYPERLINK MESSAGE"
        Exit Sub
        End If
        
        If Dir(FILE_PATH & ActiveCell.Value & ".jpg") = "" Then
        If MsgBox("****** POSTAGE SHEET HAS NOW BEEN UPDATED ******" & vbCrLf & vbCrLf & "    NO PHOTO TO HYPERLINK FOR THIS CUSTOMER" & vbCrLf & vbCrLf & _
        "     WOULD YOU LIKE TO OPEN THE PHOTO FOLDER ?" & vbCrLf & vbCrLf & _
        "                 YES = OPEN THE PHOTO FOLDER" & vbCrLf & vbCrLf & _
        "               NO = HYPERLINK IS NOT REQUIRED", vbYesNo + vbCritical, "HYPERLINK CUSTOMER MISSING PHOTO MESSAGE.") = vbYes Then
        
        CreateObject("Shell.Application").Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EBAY CUSTOMERS PHOTOS\")
        MsgBox "CONTINUE TO NOW HYPERLINK CUSTOMER & PHOTO ?", vbInformation, "HYPERLINK PHOTO MESSAGE"
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
As you have it now, the red code runs when you reply NO to the Msgbox. Is that what you want?

You didn't qualify the worksheet for column D
VBA Code:
For Each Cell In [B]Range("D:D")[/B].SpecialCells(xlCellTypeComments)
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,910
Office Version
  1. 2007
Platform
  1. Windows
Hi
It’s definitely column D that it needs to be applied to.
This was being step 1 in respect to just get it to work.

Step 2 is going to be for it to be applied only to the row of values just sent from user form.
Otherwisecode runs on ALL of column D

when watching the code work / run etc selecting Yes or No I see the code then enter the worksheet cells. Makes sense to have this also do the same.
I started a post for step 2 but mentioned it here should it help.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,742
Messages
5,626,618
Members
416,195
Latest member
tonmcg

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