Where to put the Else MsgBox in my code?

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
73
I have written some code, which uses bookmarks to transfer data from an excel database to a template i have on Word.

It all works well, it takes a value from the cell "B3" which is a works order number. It then searches for this number in the database and if found it transfers that line of the database to the word template.

HOWEVER, if the works order number entered in cell B3 is not found, it runs through and does nothing (which is what i want) but still displays the messagebox at the end "Inspection Sheet Created". However i would like to return a messagebox saying "Number not found"

However I am new to coding, and I am not sure where to put the else in the IF statement to display this messagebox.

VBA Code:
Dim wd As Object 'Word Application
Dim wdDOC As Object 'word document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer  to where database is stored
Dim myValue As Variant
Dim WorksOrder As String


'Start word as new document

Set wd = CreateObject("Word.Application")


'Get user entered WorksOrder Number

WorksOrder = ThisWorkbook.Sheets("PrintDocuments").Range("B3").Value


'Set worksheet

Set sh = ThisWorkbook.Sheets("Database")

iRow = 2 'row in which data starts from in database

Do While sh.Range("A" & iRow).Value <> ""  'loop through until no data is found (last row of database)
 
If WorksOrder = sh.Range("D" & iRow).Value Then


'opening word template

Set wdDOC = wd.Documents.Add("T:\mageeg\TEST DATA  INSPECTION SCHEDULE Issue 3.docx")

wd.Visible = False

'code to insert values from database to bookmarks in word

wd.Selection.GoTo what:=wdGoToBookmark, Name:="PartNo"
wd.Selection.TypeText Text:=sh.Range("C" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="Serial"
wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo"
wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="WorksOrderNo"
wd.Selection.TypeText Text:=sh.Range("D" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="MaterialNo"
wd.Selection.TypeText Text:=sh.Range("F" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNo"
wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo2"
wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="Type"
wd.Selection.TypeText Text:=sh.Range("H" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="Size"
wd.Selection.TypeText Text:=sh.Range("I" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="WKPRESS"
wd.Selection.TypeText Text:=sh.Range("J" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNumber"
wd.Selection.TypeText Text:=sh.Range("G" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="CertDate"
wd.Selection.TypeText Text:=sh.Range("K" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="BatchNo"
wd.Selection.TypeText Text:=sh.Range("L" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="JobNo"
wd.Selection.TypeText Text:=sh.Range("M" & iRow).Value

wd.Selection.GoTo what:=wdGoToBookmark, Name:="DateOfManufacture"
wd.Selection.TypeText Text:=Format(sh.Range("N" & iRow).Value, "mm/yy")


'code to delete the existing bookmarks from wordfile

On Error Resume Next

wdDOC.Bookmarks("PartNo").Delete
wdDOC.Bookmarks("SerialNo").Delete
wdDOC.Bookmarks("ModelNo").Delete
wdDOC.Bookmarks("WorksOrderNo").Delete
wdDOC.Bookmarks("MaterialNo").Delete
wdDOC.Bookmarks("Serial").Delete
wdDOC.Bookmarks("ModelNo2").Delete
wdDOC.Bookmarks("Type").Delete
wdDOC.Bookmarks("Size").Delete
wdDOC.Bookmarks("WKPRESS").Delete
wdDOC.Bookmarks("SerialNumber").Delete
wdDOC.Bookmarks("CertDate").Delete
wdDOC.Bookmarks("BatchNo").Delete
wdDOC.Bookmarks("JobNo").Delete
wdDOC.Bookmarks("DateOfManufacture").Delete

'save file with new name

wdDOC.SaveAs2 (ThisWorkbook.Path & "\" & sh.Range("D" & iRow).Value & ".docx")

'Print document

wdDOC.PrintOut

'close the word file

wdDOC.Close

'release memory of word doc

Set wdDOC = Nothing


Exit Do

End If

iRow = iRow + 1

Loop

wd.Quit 'close MS Word

Set wd = Nothing 'Release memory allocated to WD

MsgBox ("Inspection Test Sheet Created")

End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
85
You put it right here...
VBA Code:
Dim wd As Object 'Word Application
Dim wdDOC As Object 'word document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer  to where database is stored
Dim myValue As Variant
Dim WorksOrder As String


'Start word as new document

Set wd = CreateObject("Word.Application")


'Get user entered WorksOrder Number

WorksOrder = ThisWorkbook.Sheets("PrintDocuments").Range("B3").Value


If Trim(WorksOrder) = "" Then          'Checks if the data taken from WorksOrder is blank of not.
        MsgBox "No Number Found"
        Exit Sub       'Change "Exit Sub" to a "Exit Function" if this is a function routine.
End If

'Rest of your code follows....
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,292
Office Version
  1. 2013
Platform
  1. Windows
@mageeg Maybe like below '*******

VBA Code:
Dim wd As Object 'Word Application
Dim wdDOC As Object 'word document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer  to where database is stored
Dim myValue As Variant
Dim WorksOrder As String
Dim Found As Boolean  '******** Indicator for find

'Start word as new document

Set wd = CreateObject("Word.Application")


'Get user entered WorksOrder Number

WorksOrder = ThisWorkbook.Sheets("PrintDocuments").Range("B3").Value


'Set worksheet

Set sh = ThisWorkbook.Sheets("Database")

iRow = 2 'row in which data starts from in database

Found = False  '*************

    Do While sh.Range("A" & iRow).Value <> ""  'loop through until no data is found (last row of database)
     
        If WorksOrder = sh.Range("D" & iRow).Value Then
        '**********
        Found = True
        '**********
            'opening word template
            
            Set wdDOC = wd.Documents.Add("T:\mageeg\TEST DATA  INSPECTION SCHEDULE Issue 3.docx")
            
            wd.Visible = False
            
            'code to insert values from database to bookmarks in word
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="PartNo"
            wd.Selection.TypeText Text:=sh.Range("C" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="Serial"
            wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="ModelNo"
            wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="WorksOrderNo"
            wd.Selection.TypeText Text:=sh.Range("D" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="MaterialNo"
            wd.Selection.TypeText Text:=sh.Range("F" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="SerialNo"
            wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="ModelNo2"
            wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="Type"
            wd.Selection.TypeText Text:=sh.Range("H" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="Size"
            wd.Selection.TypeText Text:=sh.Range("I" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="WKPRESS"
            wd.Selection.TypeText Text:=sh.Range("J" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="SerialNumber"
            wd.Selection.TypeText Text:=sh.Range("G" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="CertDate"
            wd.Selection.TypeText Text:=sh.Range("K" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="BatchNo"
            wd.Selection.TypeText Text:=sh.Range("L" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="JobNo"
            wd.Selection.TypeText Text:=sh.Range("M" & iRow).Value
            
            wd.Selection.Goto what:=wdGoToBookmark, Name:="DateOfManufacture"
            wd.Selection.TypeText Text:=Format(sh.Range("N" & iRow).Value, "mm/yy")
            
            
            'code to delete the existing bookmarks from wordfile
            
            On Error Resume Next
            
            wdDOC.Bookmarks("PartNo").Delete
            wdDOC.Bookmarks("SerialNo").Delete
            wdDOC.Bookmarks("ModelNo").Delete
            wdDOC.Bookmarks("WorksOrderNo").Delete
            wdDOC.Bookmarks("MaterialNo").Delete
            wdDOC.Bookmarks("Serial").Delete
            wdDOC.Bookmarks("ModelNo2").Delete
            wdDOC.Bookmarks("Type").Delete
            wdDOC.Bookmarks("Size").Delete
            wdDOC.Bookmarks("WKPRESS").Delete
            wdDOC.Bookmarks("SerialNumber").Delete
            wdDOC.Bookmarks("CertDate").Delete
            wdDOC.Bookmarks("BatchNo").Delete
            wdDOC.Bookmarks("JobNo").Delete
            wdDOC.Bookmarks("DateOfManufacture").Delete
            
            'save file with new name
            
            wdDOC.SaveAs2 (ThisWorkbook.Path & "\" & sh.Range("D" & iRow).Value & ".docx")
            
            'Print document
            
            wdDOC.PrintOut
            
            'close the word file
            
            wdDOC.Close
            
            'release memory of word doc
            
            Set wdDOC = Nothing
            
            
            Exit Do
        
        End If
    
    iRow = iRow + 1
    
    Loop

wd.Quit 'close MS Word

Set wd = Nothing 'Release memory allocated to WD

'*****************
    If Found = True Then
        MsgBox ("Inspection Test Sheet Created")
        Else
        MsgBox ("Number not found")
    End If
 '*****************
End Sub
 
Solution

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
73
Thank you both for your help - worked a treat :)

Seems really easy now i look at it, was just getting myself confused!!!
 

Forum statistics

Threads
1,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

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