Problem in customizing ribbon

Hello

I have a commandbutton on my sheet11.I have have created an icon on my custom ribbon and want the code of that commandbutton run if i click on the icon on my new ribbon tab. How can I?

Best Regards
Imran Bhatti

the code resides in the click event of the commandbutton1 and this button is placed on sheet.

Like

Private sub CommandButton1_Click()
Code here
End sub

YOur mentioned dropdown shows only macros which are in a modual and not the click events of forms controls

by creating a duplicate an error occurs
" Object Required"

and this line of code gets highlighted

If Cells(i, 5) = legname And Cells(i, 1) >= DTPicker1.Value And Cells(i, 1) <= DTPicker2.Value Then

Lemme do it

Sorry for providing less information. Here are the complete info

the code in the click event of commandbutton1 (which is on sheet and not in a userform) and this code goes to a journal determines the rows whether the column of the journal contains the dates equal to or in between the dates of dtpicker1 and dtpicker2 then it determines wheter the column 5 of journal contains a account head that is in L2 cell after determining these 3 criterias this code copies all the rows(meeting criteria) from the journal and pastes to another location as a ledger.
I want to keep both this command button and a button on the ribbon .Both should perform the above mentioned task.

Hope now it is all conveyed

Best Regards
Imran Bhatti

You will need to post the code for the click event, I feel.

Alternatively, you might create a macro in a normal module thus

Sub DoButtonClick()
Application.Run "Sheet1.CommandButton1_Click"
End Sub

and assign this macro to your ribbon button. Sheet1 needs to be replaced with the code name- not the tab name- of the sheet with the button.

Here is the code

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Sheets("Journal").Activate
    Range("L10").Value = "Opening Balance"
    Range("R10").Formula = "=SUMIFS(G8:G1048576,E8:E1048576,N3,A8:A1048576,""<""&M1) + VLOOKUP(N3,Chart_of_Accounts!P3:T314,3,0)"
    Range("S10").Formula = "=SUMIFS(H8:H1048576,E8:E1048576,N3,A8:A1048576,""<""&M1)+VLOOKUP(N3,Chart_of_Accounts!P3:T314,4,0)"
    Range("T10").Formula = "=R10" & "-" & "S10"
    Range("L9").Value = "Voucher Date"
    Range("M9").Value = "Voucher #"
    Range("N9").Value = "Cheque #"
    Range("O9").Value = "Main Acc"
    Range("P9").Value = "Sub-Acc"
    Range("Q9").Value = "Description"
    Range("R9").Value = "Debit"
    Range("S9").Value = "Credit"
    Range("T9").Value = "Balance"
    Call LedgerHeading
    Dim acc As String
    Dim jawab As String
    acc = Mid(Sheets("Journal").Range("L2").Value, 7, 100)
    jawab = "Ledger:" & " " & acc
    Range("L7").Value = jawab

    Dim legname As String
    Dim lastrow As Double
    Dim i As Double
    Dim sDate As Date
    Dim lDate As Date
    Application.ScreenUpdating = False
    Sheets("Journal").Range("L11:t1048576").Clear
    Sheets("Journal").Range("L11:t1048576").Borders.LineStyle = xlNone
    legname = Sheets("Journal").Range("L2").Value
    lastrow = Sheets("Journal").Range("a1048576").End(xlUp).Row

    For i = 1 To lastrow
        If Cells(i, 5) = legname And Cells(i, 1) >= DTPicker1.Value And Cells(i, 1) <= DTPicker2.Value Then

            Range(Cells(i, 1), Cells(i, 8)).Copy
            Range("L1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
        End If
    Next i

    Range("L1048576").End(xlUp).Offset(1, 0).Select
    Selection.Resize(, 9).Select
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection.Font
        .Name = "Arial Black"
        .Bold = True
        .Size = 11
        .Color = vbBlue
    End With
    Range("Q7").Value = "From  " & DTPicker1.Value & "  to   " & DTPicker2.Value
    Dim Lrow As Long
    Lrow = Range("L1048576").End(xlUp).Offset(1, 0).Row - 1
    Range("L1048576").End(xlUp).Offset(1, 0).Select
    ActiveCell.Offset(, 6).Formula = "=sum(R10:" & "R" & Lrow & ")"
    ActiveCell.Offset(, 7).Formula = "=sum(S10:" & "S" & Lrow & ")"
    ActiveCell.Offset(, 7).NumberFormat = Range("S10").NumberFormat
    ActiveCell.Value = "TOTAL"

    Dim irow As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    irow = Range("L1048576").End(xlUp).Row
    Range("T11:T" & irow).Formula = "=T10" & "+" & "R11" & "-" & "S11"
    Range("T" & irow).Formula = "= R" & irow & "-" & "s" & irow
    Range("T11:T" & irow).NumberFormat = Range("T10").NumberFormat
    Range("L7:T" & irow).Copy
    Range("L7:T" & irow).PasteSpecial xlPasteValues
    With Range("L11:M" & irow).Select
        Selection.HorizontalAlignment = xlLeft
    End With
    With Range("R9:T" & irow).Select
        Selection.HorizontalAlignment = xlLeft
    End With
    Dim brow As Long
    brow = Sheets("journal").Range("L1048576").End(xlUp).Offset(-1, 0).Row
    With Range("L11:T" & brow).Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlHairline
    End With
    Range("L11").Select

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub

Which sheet is that code in?

commandbutton is placed in sheet11 . the journal and ledger are also on the same sheets

Try this code in a normal module and call this routine from the button click and your menu item

Sub updateJournal()
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    End With
    With Sheets("Journal")
        .Range("L10").Value = "Opening Balance"
        .Range("R10").Formula = "=SUMIFS(G8:G1048576,E8:E1048576,N3,A8:A1048576,""<""&M1) + VLOOKUP(N3,Chart_of_Accounts!P3:T314,3,0)"
        .Range("S10").Formula = "=SUMIFS(H8:H1048576,E8:E1048576,N3,A8:A1048576,""<""&M1)+VLOOKUP(N3,Chart_of_Accounts!P3:T314,4,0)"
        .Range("T10").Formula = "=R10" & "-" & "S10"
        .Range("L9:T9").Value = Array("Voucher Date", "Voucher #", "Cheque #", "Main Acc", _
                                      "Sub-Acc", "Description", "Debit", "Credit", "Balance")
        .Activate
        Call LedgerHeading
        Dim acc               As String
        Dim jawab             As String
        acc = Mid(.Range("L2").Value, 7, 100)
        jawab = "Ledger:" & " " & acc
        .Range("L7").Value = jawab

        Dim legname           As String
        Dim lastrow           As Double
        Dim i                 As Double
        Dim sDate             As Date
        Dim lDate             As Date
        Application.ScreenUpdating = False
        .Range("L11:t1048576").Clear
        .Range("L11:t1048576").Borders.LineStyle = xlNone
        legname = .Range("L2").Value
        lastrow = .Range("a1048576").End(xlUp).Row

        For i = 1 To lastrow
            If .Cells(i, 5) = legname And .Cells(i, 1) >= .DTPicker1.Value And .Cells(i, 1) <= .DTPicker2.Value Then

                .Range(.Cells(i, 1), .Cells(i, 8)).Copy
                .Range("L1048576").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
            End If
        Next i

        With .Range("L1048576").End(xlUp).Offset(1, 0).Resize(, 9)
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThin
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlDouble
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThick
            End With
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            With .Font
                .Name = "Arial Black"
                .Bold = True
                .Size = 11
                .Color = vbBlue
            End With
        End With
        .Range("Q7").Value = "From  " & .DTPicker1.Value & "  to   " & .DTPicker2.Value
        Dim Lrow              As Long
        Lrow = .Range("L1048576").End(xlUp).Offset(1, 0).Row - 1
        With .Range("L1048576").End(xlUp).Offset(1, 0)
            .Offset(, 6).Formula = "=sum(R10:" & "R" & Lrow & ")"
            .Offset(, 7).Formula = "=sum(S10:" & "S" & Lrow & ")"
            .Offset(, 7).NumberFormat = Range("S10").NumberFormat
            .Value = "TOTAL"
        End With

        Dim irow              As Long
        irow = .Range("L1048576").End(xlUp).Row
        .Range("T11:T" & irow).Formula = "=T10" & "+" & "R11" & "-" & "S11"
        .Range("T" & irow).Formula = "= R" & irow & "-" & "s" & irow
        .Range("T11:T" & irow).NumberFormat = .Range("T10").NumberFormat
        .Range("L7:T" & irow).Copy
        .Range("L7:T" & irow).PasteSpecial xlPasteValues
        .Range("L11:M" & irow).HorizontalAlignment = xlLeft
        .Range("R9:T" & irow).HorizontalAlignment = xlLeft
        Dim brow              As Long
        brow = .Range("L1048576").End(xlUp).Offset(-1, 0).Row
        With .Range("L11:T" & brow).Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlHairline
        End With
    End With
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
End Sub

Hi xlnitwit it worked perfectly.but I found another problem with custom ribbon. But I am gona mark this thread and please and post a new thread for the next problem in line with this problem."After changing the location of the file or renaming it the Custom ribbon buttons don't work at all".