Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Template Macros (Excel VBA)

    I have a template file named req.xlt. I have a file named req.xls which stores all the macros that req.xlt can use. Right now I have placed command buttons in the req.xlt that call the macros stored in req.xls. I've created the req.xls because there are always changes being requested to the macros in the template file. They also like to open old files that have been created from the template but have latest functionality.

    So, what's the best thing to do when the req.xlt gets opened? What happens if I add another command button the template?

    Can the req.xls be opened in the background (automatically) when the req.xlt opens and populate the workbook with the latest macros and command buttons?

    Should there be a one command button on the req.xlt that causes the population of macros and command buttons from the req.xls file.

    Thanks for any input.

    Deni

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Template Macros (Excel VBA)

    If you have command button in req.xlt that execute macros in req.xls, the latter will be opened automatically the first time the user clicks one of the command buttons in the former. You could also open req.xls in the Workbook_Open event procedure (in ThisWorkbook) for req.xlt, then hide it.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Template Macros (Excel VBA)

    I have created the workbook_open event to open the req.xls. In the req.xls I have created another workbook open the populate the req.xlt with commands buttons.

    Set myDocument = Workbooks("copy of requisition.xlt").Worksheets("Sheet1")
    myDocument.Shapes.AddOLEObject Left:=100, Top:=100, Width:=50, Height:=50, _
    IconLabel:="Hello", ClassType:="Forms.CommandButton.1"

    I can't get the name on the button to change and how do I add the object name?
    Is this shapes button usable as a normal command button created from the toolbar?

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Template Macros (Excel VBA)

    Hans,

    I get a syntax with myDocument.OLEObjects.("cmdTest").Object.Caption = "Click me - please!"

    Thanks for the help,

    Deni

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Template Macros (Excel VBA)

    Edited by HansV to correct error

    You can use

    myDocument.Shapes.AddOLEObject(Left:=100, Top:=100, Width:=50, Height:=50, _
    ClassType:="Forms.CommandButton.1").Name = "cmdTest"
    myDocument.OLEObjects("cmdTest").Object.Caption = "Click me - please!"

    BTW A button created this way is the same as a button created from the Control Toolbox.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Template Macros (Excel VBA)

    I assume you got a syntax error

    I was careless while composing my reply. There shouldn't have been a period after OleObjects, I have corrected my reply.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Template Macros (Excel VBA)

    So, I can open the req.xls file with the workbooks_open event in req.xlt.

    I can add multiple command buttons is req.xlt with the workbooks_open event is req.xls.

    Now, how can I add the click events for each command button. I don't want to have any stored code in the req.xlt in case I make changes down the road.

    Is this possible?

    Thanks,

    Deni

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Template Macros (Excel VBA)

    You must set a reference to Microsoft Visual Basic for Applications Extensibility 5.3 (in Tools | References... in the Visual Basic Editor). This enables you to use code to write code. Here is an example:

    Dim lngLine As Long
    With ActiveWorkbook.VBProject.VBComponents("Blad1").Cod eModule
    ' Creare event procedure for the Click event of cmdTest
    lngLine = .CreateEventProc("Click", "cmdTest")
    ' Insert a tab followed by Call MyMacro
    .InsertLines lngLine + 1, vbTab & "Call MyMacro"
    ' Delete the next line
    .DeleteLines lngLine + 2
    End With

  9. #9
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Template Macros (Excel VBA)

    Hans,

    Thanks for you help

    Dim lngLine As Long
    With ActiveWorkbook.VBProject.VBComponents("ButtonCode" ).CodeModule

    ' Create event procedure for the Click event of cmdTest
    lngLine = .CreateEventProc("Click", "FundType") Dim lngLine As Long
    With ActiveWorkbook.VBProject.VBComponents("ButtonCode" ).CodeModule

    ' Create event procedure for the Click event of cmdTest
    lngLine = .CreateEventProc("Click", "FundType") I get a run time error 57102 here

    ' Insert a tab followed by Call MyMacro
    .InsertLines lngLine + 1, vbTab & "Call FundType"

    ' Delete the next line
    .DeleteLines lngLine + 2

    End With

    ' Insert a tab followed by Call MyMacro
    .InsertLines lngLine + 1, vbTab & "Call FundType"

    ' Delete the next line
    .DeleteLines lngLine + 2

    End With

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Template Macros (Excel VBA)

    Take a good look at your code. You have pasted the code I posted, then pasted it again. This leads to invalid code and duplicate definitions.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Template Macros (Excel VBA)

    Hans,

    I think I hit "control v" twice by accident.

    Here is the code.

    Private Sub Workbooks_Open()

    Set myDocument = Workbooks("copy of requisition.xlt").Worksheets("Sheet1")

    myDocument.Shapes.AddOLEObject(Left:=265, Top:=708, Width:=72, Height:=21, _
    ClassType:="Forms.CommandButton.1").Name = "FundType"
    myDocument.OLEObjects("FundType").Object.Caption = "CDN Funds"

    Dim lngLine As Long
    With ActiveWorkbook.VBProject.VBComponents("ButtonCode" ).CodeModule

    ' Create event procedure for the Click event of FundType
    lngLine = .CreateEventProc("Click", "FundType")

    ' Insert a tab followed by Call FundType
    .InsertLines lngLine + 1, vbTab & "Call FundType"

    ' Delete the next line
    .DeleteLines lngLine + 2

    End With

    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Template Macros (Excel VBA)

    You create a button in Sheet1, so you should add the On Click event procedure to the code module for Sheet1, not to some other code module.

    With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule

Posting Permissions

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