+ Reply to Thread
Results 1 to 9 of 9

Create MAcro Button with VB code

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Create MAcro Button with VB code

    Hello:

    I need a VB Code which will do the following:

    VB code to create lots of small VB Macro with Command Buttons.

    Lets say i want to create 10 command Buttons.
    Each needs to be lable as text in cell A5:A15
    please refer to attached sheet.

    Now need to create macro for each of these buttons as follows:

    Sub InsertX() where X is the value from column A5 down.
    Range("F1").Value = X
    End Sub

    Let me know if any questions.
    Riz
    Attached Files Attached Files

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Create MAcro Button with VB code

    It sounds like you want to create a button for each cell in A5:A15. You want each button labeled with the text that is in each of those cells.

    When a button is pressed you want the value from the matching cell in A1:A15 to be put in F1. i.e. you want the button caption to be put in F1.

    Is that correct?

    Why don't you just do it by hand rather than automating this process.
    Alternatly, rather than searching for the right button to press, you could select the cell from column A and press one button and have that value put in F1.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Create MAcro Button with VB code

    Here is some code that should do what you want
    Please Login or Register  to view this content.
    Or you could do without buttons and put this in the sheet's code module.
    Double clicking on a cell in column A will put that cells contents into F1.

    Please Login or Register  to view this content.
    Last edited by mikerickson; 01-25-2015 at 11:03 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Create MAcro Button with VB code

    Hi Mike:

    I like your second solution.
    Hovever, it is malfunctioning.
    It is jumping from Store 5 to Store 11 when i click at Store1.
    Would you be able to make it so cell is bigger and under each text in cell there will be underline so that i can put tit to click.
    Thanks and let me know if any questions.
    Tiz

  5. #5
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Create MAcro Button with VB code

    Amazing Solution Mike;

    But for some reason I was getting an object does not support property error. It was strange because I could see the property in the watch.

    I fixed it up and this is the new code in case some else stumbles upon thins and needs it.

    Please Login or Register  to view this content.
    Thanks

  6. #6
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Create MAcro Button with VB code

    You are using the double click code.
    It sounds like you are double clicking on A5 (Store1). But I don't understand what you mean by "jumping from Store5 to Store11"

    Changing the row height and underlining the cells is something that can be done by your right now.

    I don't understand what the auto spell correct rendered as "put tit to click".

  7. #7
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Create MAcro Button with VB code

    Quote Originally Posted by fredlo2010 View Post
    Amazing Solution Mike;

    But for some reason I was getting an object does not support property error. ...
    Which property? the .ShapeRange property? My guess is that its from my declaring newButton as an Object, while your code uses Button.

  8. #8
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Create MAcro Button with VB code

    Quote Originally Posted by mikerickson View Post
    Which property? the .ShapeRange property? My guess is that its from my declaring newButton as an Object, while your code uses Button.
    No I changed it to button so I could use intelisence since I am not too familiar with the object. It was giving me error before. My .ShapeRange.TextFrame does not have a characters collection.

    Capture.PNG



    And regarding the problem with the jumping cells. Here is the answer; it happened to me to.

    http://superuser.com/questions/18983...different-cell

    Thanks

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Create MAcro Button with VB code

    Hi Both:

    Very smart solution, will sure work for me and learning more and more.
    Love it.
    Thanks a lot
    Riz

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code to create Macro Button and Assigning Macro code
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-10-2014, 11:30 AM
  2. Macro to Create Button and assign code
    By swatsp0p in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-06-2014, 12:21 PM
  3. code needed to create macro for button
    By tryingtolearn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-17-2012, 08:24 AM
  4. Macro to create Command Button and assign code
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2011, 09:19 AM
  5. A macro to create a form button, assign a macro and name the button
    By cl361 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2008, 01:07 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1