Problem with Hide / Unhide sheets

[RAJESH1960]

Hello guys
I have this code in which, when there is data in the MasterData Sheet, it shows the perfect result. But the hidden sheets are unhidden, when there is no data entered, after the msgbox that displays Data is not entered. I am unaware why it is opening the hidden sheet. Please corect me, if the placement of hidesheets and unhidesheets is right and if ok then how do I keep the sheet hidden, even when there is no data entered.

Option Explicit

Sub GenerateMasterXML()
    Application.ScreenUpdating = False                                                  ' Turn ScreenUpdating off
    Dim ImportMastersFormulaStartRow                As Long
    Dim cell                                        As Range
    Dim ImportMastersFormulaStartAddress            As String, MasterDataLedgersStartAddress    As String
    Dim ImportMastersFormulaStartColumn             As String
    Dim MastersDataParticularsColumnLetter          As String
    Dim XML_FileName                                As String
    Dim wsImportMasters                             As Worksheet, wsListOfLedgers               As Worksheet
    Dim wsMasterData                                As Worksheet, wsOriginal                    As Worksheet
'
UnHideSheets

    Set wsImportMasters = Sheets("ImportMasters")
    Set wsMasterData = Sheets("MasterData")
'
    ImportMastersFormulaStartAddress = "A2"                                             ' <--- Adjust this if needed
    ImportMastersFormulaStartColumn = "A"                                               ' <--- Adjust this if needed
    ImportMastersFormulaStartRow = 2                                                    ' <--- Adjust this if needed
    MasterDataLedgersStartAddress = "B2"                                                ' <--- Adjust this if needed
    MastersDataParticularsColumnLetter = "B"                                            ' <--- Adjust this if needed
    XML_FileName = "C:\Users\" & Environ("username") & "\Desktop\Master.xml"            ' <--- Adjust this if needed
    
        If wsMasterData.Range(MasterDataLedgersStartAddress) = "" Then                          '   If all ledgers match then ...
            MsgBox "Party Ledger Name Not Entered.."                                                     '       Display message to user
            Exit Sub                                                                            '       Exit sub
        End If


    Dim LastColumnNumberInRow               As Long
    Dim LastRowInSheetImportMasters         As Long
    Dim LedgerCount                         As Long
    Dim xmlFile                             As Object
    Dim LastColumnLetterSheetImportMasters  As String
    Dim strData                             As String
    Dim strTempFile                         As String
'

    With wsImportMasters
        LastColumnLetterSheetImportMasters = Split(Cells(1, (.Cells.Find("*", , xlFormulas, _
                , xlByColumns, xlPrevious).Column)).Address, "$")(1)                            '   Get last column letter used in ImportMasters sheet
        LastRowInSheetImportMasters = .Cells.Find("*", , xlFormulas, , xlByRows, _
                xlPrevious).Row                                                                 '   Get last row used in ImportMasters sheet
'
        .Range(ImportMastersFormulaStartColumn & ImportMastersFormulaStartRow + 1 & ":" & _
                LastColumnLetterSheetImportMasters & LastRowInSheetImportMasters + 1).ClearContents '   Erase all but one row of formulas on ImportMasters sheet
'
        LedgerCount = wsMasterData.Range(MasterDataLedgersStartAddress & ":" & _
                MastersDataParticularsColumnLetter & wsMasterData.Range(MastersDataParticularsColumnLetter & _
                Rows.Count).End(xlUp).Row).Rows.Count                                           '   Get count of ledgers
'
        If LedgerCount > 1 Then .Range(ImportMastersFormulaStartAddress & ":" & _
                LastColumnLetterSheetImportMasters & LedgerCount + 1).FillDown                  '   If LedgerCount > 1 Then Create range of formulas
'
        LastColumnNumberInRow = .Cells(ImportMastersFormulaStartRow, _
                .Columns.Count).End(xlToLeft).Column                                            '  Get last column # used in row ImportMastersFormulaStartRow
'
        .Range(ImportMastersFormulaStartAddress).Resize(LedgerCount, LastColumnNumberInRow).Copy    '   Copy ImportMasters Formula results
    End With
'
'---------------------------------------------------------------------------------------
'

    strData = CreateObject("htmlfile").ParentWindow.ClipboardData.GetData("Text")               ' Save contents into strData
    strTempFile = XML_FileName                                                                  ' Set File name to copy to
    CreateObject("Scripting.FileSystemObject").CreateTextFile(strTempFile, True).Write strData  ' Write the data to file
'
HideSheets

    MsgBox ("File saved on Desktop as Master.XML.")                                             ' Display message to user
'
    Application.ScreenUpdating = True                                                           ' Turn ScreenUpdating back on
End Sub

 

[RAJESH1960]

Option Explicit

Sub HideSheets()
    Dim ws As Worksheet
    ThisWorkbook.Unprotect Password:=123
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "MasterData" Then
        ws.Visible = xlSheetVeryHidden
        End If
    Next ws
    
End Sub

Sub UnHideSheets()
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "MasterData" Then
            ws.Visible = xlSheetVisible
            
        End If
    Next ws
    
End Sub

This is the Hide ' Unhide sheets code entered.

 

[johnnyL]

Your 'UnHideSheets' is executing every time 'GenerateMasterXML' is executed.

From your question, it sounds like you may want to relocate the 'UnHideSheets' below the following portion of code:

        If wsMasterData.Range(MasterDataLedgersStartAddress) = "" Then                          '   If all ledgers match then ...
            MsgBox "Party Ledger Name Not Entered.."                                                     '       Display message to user
            Exit Sub                                                                            '       Exit sub
        End If

 

[RAJESH1960]

Your 'UnHideSheets' is executing every time 'GenerateMasterXML' is executed.

From your question, it sounds like you may want to relocate the 'UnHideSheets' below the following portion of code:

        If wsMasterData.Range(MasterDataLedgersStartAddress) = "" Then                          '   If all ledgers match then ...
            MsgBox "Party Ledger Name Not Entered.."                                                     '       Display message to user
            Exit Sub                                                                            '       Exit sub
        End If

Thanks JohnnyL.?