Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Creating Buttons in VBA

    Hi All,

    I'm having a problem creating buttons in VBA. The code I have posted below creates two buttons in cells J9 and K9 on the "PART NUMBER" sheet successfully however, the text in both buttons is "DELETE"
    I'm trying to show "EDIT" in cell J9 and "DELETE" in K9 and am not sure where I'm going wrong.
    Also, is there a way I can change the button background color to blue for the "EDIT" button and red for the "DELETE" button.
    I only want these buttons created under certain conditions and so far the code is working well for me except for the problems previously mentioned.
    Thanks so much for your help!


    Sub create_buttons()
    Worksheets("PART NUMBER").Range("J9").Select
    ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
    ActiveSheet.Buttons.Font.Bold = True
    ActiveSheet.Buttons.Caption = "EDIT"

    Worksheets("PART NUMBER").Range("K9").Select
    ActiveSheet.Buttons.Add ActiveCell.Left, ActiveCell.Top, ActiveCell.Width, ActiveCell.Height
    ActiveSheet.Buttons.Font.Bold = True
    ActiveSheet.Buttons.Caption = "DELETE"

    End Sub

  2. #2
    New Lounger
    Join Date
    Aug 2010
    Location
    New Zealand
    Posts
    13
    Thanks
    11
    Thanked 1 Time in 1 Post
    Hi, your code doesnt distinguish between each button and the last line tells them the caption is DELETE so that will overide the previous line EDIT What version of excel are you using? If you use buttons from the forms toolbar you can edit their [roperties including background colour etc.
    Last edited by motivated; 2013-10-05 at 22:42. Reason: Add more info

  3. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'm using Excel 2000, I don't want to use the tool bar because these buttons will only appear on the worksheet under certain conditions.
    Can you pinpoint what's wrong with the code to distinguish the button text for each cell?
    Thanks

  4. #4
    New Lounger
    Join Date
    Aug 2010
    Location
    New Zealand
    Posts
    13
    Thanks
    11
    Thanked 1 Time in 1 Post
    Sorry I cant fix your code , we will have to wait for the experts. I use the buttons from the forms toolbar.

  5. #5
    New Lounger
    Join Date
    Aug 2010
    Location
    New Zealand
    Posts
    13
    Thanks
    11
    Thanked 1 Time in 1 Post
    You may have to refer to the command buttons by their individual numbers to change them. You may be able to see the number in the name box when you select the button on the sheet in design mode. HTH

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The problem is that the code changes the names of all buttons, so the first one changes the one button to "EDIT" and the second one renames the first button and names the 2nd button to "DELETE". Try this code. It doesn't require changing the selection or activating either cell.

    Code:
    Option Explicit
    Sub create_buttons()
      With Worksheets("PART NUMBER")
        With .Range("J9")
          With .Parent.Buttons.Add(.Left, .Top, .Width, .Height)
            .Font.Bold = True
            .Caption = "EDIT"
          End With
        End With
        With .Range("K9")
          With .Parent.Buttons.Add(.Left, .Top, .Width, .Height)
            .Font.Bold = True
            .Caption = "DELETE"
          End With
        End With
      End With
    End Sub
    Be aware that if you rerun the code, it will recreate the buttons ON TOP OF the previous. It does not delete the any old buttons.

    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    mrg99 (2013-10-06)

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Another note about the code. you could assign the macro you want to run to the code by adding a line in the WITH .. END WITH of the Button Add, for example like:

    .OnAction = "MyEdit"

    Where "MyEdit" is the name of the macro you want to assign. This is equivalent to right-clicking and selecting the code from the "Assign Macro.." dialog

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    mrg99 (2013-10-06)

  10. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    I find the best way to deal with this is to create the buttons first, assign names to the buttons (using the range 'naming' box to the left of the formula bar), format the buttons text size, colour etc and assign relevant macros to them.

    Then use code like this..
    Sheets("PART NUMBER").Select
    ActiveSheet.Shapes("btnEdit").Visible = True
    ..to show the button (I named this one "btnEdit")
    and like this to hide a button..
    Sheets("PART NUMBER").Select
    ActiveSheet.Shapes("btnDelete").Visible = False

    see attached workbook for example

    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    mrg99 (2013-10-06)

  12. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..using my method means you don't need to worry about recreating buttons 'on top' of previous buttons etc.
    So, although your question was about 'creating buttons in vba', perhaps my answer may give you what you actually want.

    zeddy

  13. #10
    New Lounger
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks all for your help!
    Steve,
    Thanks for your code, it works perfectly
    I added code to the beginning of the sub to delete any buttons that were previously created.
    I can change the button font or font color but I can't seem to find any code to change the button color, is this not possible?
    It's not a deal breaker, but it would be nice if the "EDIT" button were blue and the "DELETE" button were red.
    I'd have to do this through a macro because each time the create_buttons sub is run there could be a different number of rows in the worksheet that would have the "EDIT" and "DELETE" buttons.
    Thanks again!

  14. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Using bottons from the forms menu has limited formatting. if you need more formatting you need to use the buttons from the control toolbox. They have more options but are morw difficult to use. You find that formatting options and ease of use are opposites. data validation is easiest with least options, forms options have more options, and contol toolbox has the most options.

    Steve

  15. The Following User Says Thank You to sdckapr For This Useful Post:

    mrg99 (2013-10-06)

  16. #12
    New Lounger
    Join Date
    Sep 2013
    Posts
    5
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Steve,
    I'm going to use different font colors for the EDIT and DELETE buttons which I can assign in VBA.
    Thanks for all your help.
    Greatly appreciated!
    Last edited by mrg99; 2013-10-06 at 21:36.

  17. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi mrg99

    OK, if you want blue and red buttons, check out the attached updated file.

    The [create Buttons] routine will create the required buttons in columns [J] and [K] on the current cellpointer row.
    You should be able to amend this as required to whatever particular row you want.

    Also, edit the vba code to assign the appropriate .OnAction macros for the [Edit] and [Delete] buttons.

    zeddy
    Attached Files Attached Files

  18. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Zeddy,
    When I try to open the file, I get an "Unreadable" content warning of corruption. Could you upload a new copy?

    Steve

  19. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Steve

    When I save my attached file from post#13, I can open it without that message.
    But I have posted another copy here.
    Tested OK on my system.

    zeddy
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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