+ Reply to Thread
Results 1 to 5 of 5

Assigning button to a Private sub module

  1. #1
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Lightbulb Assigning button to a Private sub module

    Hi,
    Can you please help me to create a desgnated button for Private sub Module?

    I am not able to find the macro which I want to assign in the list when I try that in the normal way. is there any other option?

    The code is below.
    PHP Code: 
    Option Explicit

    Private Sub cmdUpdate_Click()
        
    On Error GoTo ErrHandler
        
        Dim aError
    aFilesAs Integer
        
        With Application
            
    .ScreenUpdating False
            
    .EnableEvents False
            
    .DisplayAlerts False
        End With
        
        aFiles 
    = Array("R.0007418_Liq Fil_MSR.xlsx""R.0007420_Singapore_MSR.xlsx""R.0007440_Houston_MSR.xlsx"_
                       
    "R.0007441_CPGF-AR_LR_MSR.xlsx""R.0007442_Telecom_MSR.xlsx"_
                       
    "R.0007443_CPGK-HR_MSR.xlsx""R.0007444_CPGK-LR_MSR.xlsx""R.0007814_CTT_MSR.xlsx"_
                       
    "R.0008500_QUIMPER_MSR.xlsx""R.0007272_EBU_MSR.xlsx""R.0008490_CGT_MSR.xlsx"_
                       
    "R.0007399_CES-US_MSR.xlsx""R.0007400_CRTI_MSR.xlsx""R.0007415_Config_MSR.xlsx"_
                       
    "R.0007416_PDCA_MSR.xlsx""R.0007417_PPSC_MSR.xlsx""R.0007418_Air Fil_MSR.xlsx")
                       
        
    strError vbNullString
        
        
    'check for files:
        For i = LBound(aFiles) To UBound(aFiles)
            If Dir(ThisWorkbook.Path & "\" & aFiles(i)) = vbNullString Then
                strError = strError & vbLf & "File not found: '" & aFiles(i) & "'."
            End If
        Next i
        
        If strError <> vbNullString Then GoTo ErrHandler
        
        With ThisWorkbook
            GetFromWorkbook .Worksheets("Liq Fil"), "R.0007418_Liq Fil_MSR.xlsx"
            GetFromWorkbook .Worksheets("Singapore"), "R.0007420_Singapore_MSR.xlsx"
            GetFromWorkbook .Worksheets("Houston"), "R.0007440_Houston_MSR.xlsx"
            GetFromWorkbook .Worksheets("CPGF AR-LR"), "R.0007441_CPGF-AR_LR_MSR.xlsx"

            GetFromWorkbook .Worksheets("Telecom"), "R.0007442_Telecom_MSR.xlsx"
            GetFromWorkbook .Worksheets("CPGK HR"), "R.0007443_CPGK-HR_MSR.xlsx"
            GetFromWorkbook .Worksheets("CPGK LR"), "R.0007444_CPGK-LR_MSR.xlsx"
            GetFromWorkbook .Worksheets("CTT"), "R.0007814_CTT_MSR.xlsx"
            GetFromWorkbook .Worksheets("Quimper"), "R.0008500_QUIMPER_MSR.xlsx"
            GetFromWorkbook .Worksheets("EBU"), "R.0007272_EBU_MSR.xlsx"
            GetFromWorkbook .Worksheets("CGT"), "R.0008490_CGT_MSR.xlsx"
            GetFromWorkbook .Worksheets("CES-US"), "R.0007399_CES-US_MSR.xlsx"
            GetFromWorkbook .Worksheets("CRTI"), "R.0007400_CRTI_MSR.xlsx"
            GetFromWorkbook .Worksheets("Config"), "R.0007415_Config_MSR.xlsx"
            GetFromWorkbook .Worksheets("PDCA"), "R.0007416_PDCA_MSR.xlsx"
            GetFromWorkbook .Worksheets("PPSC"), "R.0007417_PPSC_MSR.xlsx"
            GetFromWorkbook .Worksheets("Air Fil"), "R.0007418_Air Fil_MSR.xlsx"
            
            .Worksheets("DU Dashboard").Activate
            Sheet23.CreatePowerPoint
        End With
        
    ErrHandler:
        If strError <> vbNullString Then
            frmError.lstError.Clear
            aError = Split(strError, vbLf)
            For i = LBound(aError) + 1 To UBound(aError)
                frmError.lstError.AddItem aError(i)
            Next i
            frmError.Show
        End If

        With Application
            .DisplayAlerts = True
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        
        If Err.Number <> 0 Then
            MsgBox Err.Number & ": " & Err.Description
        End If
    End Sub 
    Attaching the workbook also for your easy reference...

    New Consolidated-Test.zip

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Assigning button to a Private sub module

    It needs to be public, not private

  3. #3
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Re: Assigning button to a Private sub module

    WOW... thats all?
    Checked and working!!!
    Thanks a lot...

    One doubt... Then why we write private sub? I mean on what occasion we write private sub?

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Assigning button to a Private sub module

    Generally, scope should be kept as small as possible, so only code that needs to use it does so. If you code is never going to be called from outside the module/class/object in which it resides, then naming it private is fine. If you want to call it from somewhere else, then it needs to be public

  5. #5
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    489

    Thumbs up Re: Assigning button to a Private sub module

    Oh... Ok....
    Got it now.. Thanks for the explanation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1