Where to put the Else MsgBox in my code?

M

mageeg

Board Regular
Joined
Jun 6, 2021
Messages
81
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
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....
 
Upvote 0
@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
 
Upvote 0
Solution
Thank you both for your help - worked a treat :)

Seems really easy now i look at it, was just getting myself confused!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Run Macro for only selected row
Replies
2
Views
487
Suavesav
S
M
  • Solved
Excel not responding when running this piece of code? What am i missing?
Replies
7
Views
918
mageeg
M
B
  • Question Question
Automatically filling a label template in Excel
Replies
0
Views
2K
Ben171
B
M
  • Question Question
Specifying Printer in this scenario
Replies
0
Views
1K
mageeg
M
F
  • Question Question
Excel Data to Word Template
2
Replies
10
Views
2K
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

Share this page

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
Back
Top