Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Run Macro needed (2000 sp3)

    Greetings!
    I am in need of some assistance regarding a macro to do a couple of tasks.
    1) It needs to auto run. 2) It needs to create a unique button
    3) it needs to assign an existing macro to the button
    4) on closure of the file it needs to delete the newly created button.

    Any help is greatly appreciated!

    thanks,
    Brad

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

    Re: Auto Run Macro needed (2000 sp3)

    Please provide some more information, for example: where should this "unique button" be created?
    - On a worksheet (which one? where?)
    - On a toolbar (which one? where?)
    - On a userform (which one? where?)

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Run Macro needed (2000 sp3)

    Hans,

    - On a worksheet (which one? where?) not on a specific worksheet.
    - On a toolbar (which one? where?) added to the standard toolbar If possible as a standalone toolbar

    Thanks,
    Brad

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

    Re: Auto Run Macro needed (2000 sp3)

    You need code in the ThisWorkbook module for this (not in a standard module). ThisWorkbook stores workbook-level event procedures. For example, to create a toolbar named MyToolbar with a button named My Button that runs the macro MyMacro:

    ' Show toolbar when workbook is active
    Private Sub Workbook_Activate()
    On Error Resume Next
    Application.CommandBars("MyToolbar").Visible = True
    End Sub

    ' Delete toolbar when workbook is closed
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("MyToolbar").Delete
    End Sub

    ' Hide toolbar when workbook is not active
    Private Sub Workbook_Deactivate()
    On Error Resume Next
    Application.CommandBars("MyToolbar").Visible = False
    End Sub

    ' Create toolbar when workbook is opened
    Private Sub Workbook_Open()
    Dim cbr As CommandBar
    Dim cbb As CommandBarButton
    Set cbr = Application.CommandBars.Add("MyToolbar")
    Set cbb = cbr.Controls.Add(msoControlButton)
    With cbb
    .Caption = "My Button"
    .OnAction = "MyMacro"
    .Style = msoButtonIcon
    .FaceId = 123
    End With
    Set cbb = Nothing
    Set cbr = Nothing
    End Sub

    A demo workbook is attached.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Run Macro needed (2000 sp3)

    Hans,
    Thanks for the information. I will add the macro and try it out. Hopefully all will go well. I will reply either way.

    Thanks again,
    Brad

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Run Macro needed (2000 sp3)

    Hans!

    Thanks so much! I added the information and it works great. I have one additional question. Is it possible to have the newly created button always appear in the same spot?

    if not, no problem.


    Thanks,
    Brad

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

    Re: Auto Run Macro needed (2000 sp3)

    Below the line that creates cbr, you can add

    cbr.Position=msoBarTop

    if you want the toolbar to be at the top, or (for example)

    cbr.Top = 72
    cbr.Left = 144

    to position the toolbar 72 points (1 inch) from the top of the screen, and 144 points (2 inches) from the left side of the screen.

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Run Macro needed (2000 sp3)

    Hans,
    Here is what I did: but it did not work. I think I placed the info incorrectly.

    Dim cbr As CommandBar
    Dim cbb As CommandBarButton
    cbr.Position = msoBarTop
    cbr.Top = 72
    cbr.Left = 144
    Set cbr = Application.CommandBars.Add("ARA Metrics")
    Set cbb = cbr.Controls.Add(msoControlButton)
    With cbb
    .Caption = "ARA Metrics"
    .OnAction = "Select_Form"
    .Style = msoButtonIcon
    .FaceId = 2950
    End With
    Set cbb = Nothing
    Set cbr = Nothing
    End Sub

    Thanks,
    Brad

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

    Re: Auto Run Macro needed (2000 sp3)

    That is because you placed the extra instructions before the line where cbr is created instead of after it. Moreover, you must either use

    cbr.Position = msoBarTop

    or

    cbr.Top = 72
    cbr.Left = 144

    but not both. For example:

    ' Create toolbar
    Set cbr = Application.CommandBars.Add("ARA Metrics")
    ' Set its position AFTER it has been created
    cbr.Position = msoBarTop

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Run Macro needed (2000 sp3)

    Hans!
    Thanks for all of your help. This is working well enough to support my needs.

    Thanks again,
    Brad

Posting Permissions

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