Optional MsgBox to appear

[RAJESH1960]

Hello Experts

I am trying to fit 2 MsgBox in one code, but it should display one only depending on the conditions in the code.
Right now the code runs and displays MsgBox ("File saved on Desktop as Master.XML.") even if there is no data.
I want to add just one line of code in the beginning.
If, in sheet MasterData cell B2 is Blank and when I press the button macro, the MsgBox should display “All Ledgers Available.” and stop from running the rest of the code.

Else, the code should continue to run and on completion, the MsgBox should display “File saved on Desktop as Master.XML.”.

Option Explicit
Sub GenerateMasterXML()
'solved by JohnnyL

'
    Dim LastColumnNumberInRow               As Long
    Dim x                                   As Long
    Dim xmlFile                             As Object
    Dim LastColumnLetterSheetImportMasters  As String
    Dim strData                             As String
    Dim strTempFile                         As String
'
    x = Sheets("MasterData").Range("B2:B" & Sheets("MasterData").Range("B" & Rows.Count).End(xlUp).Row).Rows.Count  ' Get count of rows to write to file
'
    LastColumnNumberInRow = Sheets("ImportMasters").Cells(2, Sheets("ImportMasters").Columns.Count).End(xlToLeft).Column  ' Get last column number in row
'
    LastColumnLetterSheetImportMasters = Split(Cells(1, (Sheets("ImportMasters").Cells.Find("*", , xlFormulas, _
            , xlByColumns, xlPrevious).Column)).Address, "$")(1)                                            ' Get last column letter used in Sheets("ImportMasters")
'
    Sheets("ImportMasters").Range("A2:" & LastColumnLetterSheetImportMasters & x + 1).FillDown                  ' Create range needed to copy
'
    Sheets("ImportMasters").Range("A2").Resize(x, LastColumnNumberInRow).Copy
'
    strData = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("Text")                               ' Save contents into strData
'
    strTempFile = "C:\Users\" & Environ("username") & "\Desktop\Master.xml"
    CreateObject("Scripting.FileSystemObject").CreateTextFile(strTempFile, True).Write strData                  ' Write the data to file
'
    Application.CutCopyMode = False

    
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
    MsgBox ("File saved on Desktop as Master.XML.")
End Sub

 

[Micron]

Test for a value and exit if none:

If Sheets("MasterData").Range("B2")= "" Then
  MsgBox “All Ledgers Available"
 Exit Sub
End If

I usually put all my Dim statements together at the top, but in this case would probably have that code first. Not much point in declaring anything if you might exit right away.

 

[johnnyL]

Try putting the following after the Dim lines of code:

    If Sheets("MasterData").Range("B2") = vbNullString Then                                                         ' If B2 in MasterData is blank then ...
        MsgBox "All Ledgers Available."                                                                             '   Display message to user
        Exit Sub                                                                                                    '   Exit the code
    End If

 

[RAJESH1960]

JohnnyL. In the code please clear the data in ImportPurchase sheet from cell A3:FT3 to end. When I run a new file the old data is not deleted.

 

[johnnyL]

???

 

[RAJESH1960]

Sorry. Got carried away. That is for a different post. I have a list of 3 issues to be solved. Will send individually. Thanks JohnnyL

 

[Micron]

I guess you need to be the second person to post the same answer in order for it to be deemed the solution?

 

[RAJESH1960]

I guess you need to be the second person to post the same answer in order for it to be deemed the solution?

Sorru Micron, I tried your code and it gave me an error

 

[RAJESH1960]

Test for a value and exit if none:

If Sheets("MasterData").Range("B2")= "" Then
  MsgBox “All Ledgers Available"
 Exit Sub
End If

I usually put all my Dim statements together at the top, but in this case would probably have that code first. Not much point in declaring anything if you might exit right away.

Compile error, Syntax error

 

[johnnyL]

The error is the left quotation mark in front of the message to be displayed.

@Micron Don't sweat it, it all pays the same.