asked on

Access 2000 - Assign code in Modules to button

Dear Experts,

I have some VBA code lines in Modules section, those I would like to assign to button, that pressing it would be executed so run.

But if I put the module name as button property as Hyperlink SubAddress, the Access just open the module in design view and not run. Similiar the case if I put the module to a macro as OpenModule action, it opens the code in design but does not start the run of it.

Could you please advise what is the method of it?

Basically if I would like to open a query by a button, I used to apply those two ways above

thanks,

SOLUTION

Barry Cunney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Thanks I was able to do it with copying the code lines after the Command1_Click(),

Could be also maybe call somehow the Module macro?

As I remember in Excel VBA it is with Call command, but this does not work in Access as I can see

Private Sub Command1_Click()
Call FR_RM_export
End Sub

Rey Obrero (Capricorn1)

what is the name of the function?
post the first line of the function..

public functionName(?

csehz

ASKER

Basically I would like to start his exporting macro in the Code with the button

Sub ExportAndFormat()
Dim xlApp As Object 
Dim xlWb As Object 
Dim xlWs As Object 
Dim xlData As Object 
Dim xlTotals As Object 
Dim strPath As String
Dim intNoCols As String
Dim intNoRows As Long

    strPath = CurrentProject.Path & "\AccessExport.xls"

    If Dir(strPath) <> "" Then Kill strPath

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", strPath, True

    'open the excel file and format

    Set xlApp = CreateObject("Excel.Application")

    Set xlWb = xlApp.Workbooks.Open(strPath)

    Set xlWs = xlWb.Worksheets(1)

    Set xlData = xlWs.UsedRange

    intNoCols = xlData.Columns.Count

    intNoRows = xlData.Rows.Count

    With xlData.Rows(1)
        With .Borders
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With
    End With

    Set xlTotals = xlData.Cells(intNoRows + 1, 2).Resize(, intNoCols - 1)

    With xlTotals
        With .Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
            .ColorIndex = xlAutomatic
        End With
        .Formula = "=SUM(" & xlWs.Cells(2, 2).Resize(intNoRows - 1).Address(0, 0) & ")"
        .Interior.ColorIndex = 36
        .Interior.Pattern = xlSolid
    End With
    
    xlWs.UsedRange.Font.Name = "Verdana"

    xlWs.UsedRange.EntireColumn.AutoFit
    
    xlWb.Save

    xlApp.Quit

    Set xlApp = Nothing

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

That is perfect like that, thanks very much the solutions