Results 1 to 5 of 5

Creating a Command button and assigning code to it

Threaded View

  1. #1
    Registered User
    Join Date
    10-17-2013
    Location
    Orlando
    MS-Off Ver
    Excel 2007
    Posts
    3

    Creating a Command button and assigning code to it

    I'm working on a project for work and could really use some help...

    I have a command button that when clicked, a message box pops up and asks to enter a name ('userinput') for a new item. This name is then inserted into a specific cell.

    What I'm trying to do is to (within the same command button procedure), create/copy another command button. And then add some code to the new command button that will input the same 'userinput' name value in the cell everytime its clicked.

    Two main things I would like to achieve

    1) Being able to click on the first button multiple times to create buttons with different names (i.e. userinput1, userinput2, etc.)
    2) Each time a new command button is created, it types in the unique 'userinput' value into the cell and is also captioned with that same name

    Please bear with me, I've only been playing with VBA for about 2 weeks specifically for this project I have for work and what I've learned so far has come from Google lol. Thanks in advance!

    Here is the code that I'm using, not sure what makes sense in there, it certainly doesn't work. Please add/delete as you deem necessary....it's really a cluster$#^$

    Sub Additembutton()
    userinput = InputBox("Enter New item Name")
    Sheets("2013 sheet").Range("Item").Value = userinput
    
    ' Declare variables
        Dim i As Long, p As Long, Hght As Long
        Dim Name As String, NName As String
         ' Set the button properties
        i = i + 1
        Hght = 305.25
         ' Set the name for the button
        NName = userinput & i
         ' Test if there is a button already and if so, increment its name
        For Each OLEObject In ActiveSheet.OLEObjects
            If Left(OLEObject.Name, 9) = "newitembtn" Then
                Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9)
                If Name >= i Then
                    i = Name + 1
                End If
                NName = "userinput" & i
                Hght = Hght + 27
            End If
        Next
         ' Add button
        Dim myCmdObj As String
        Set myCmdObj = ActiveSheet.Buttons.Add(90, 30, 90, 30).Select
        myCmdObj.Name = userinput
         ' Define buttons caption
        myCmdObj.Object.Caption = userinput
         ' Inserts code for the button
        With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
            n = .CountOfLines
            .InsertLines n + 1, "Private Sub " & NName & "_Click()"
            .InsertLines n + 2, vbNewLine
            .InsertLines n + 3, "Sheets ("2013 Sheet").Range ("item").Value = userinput"
            .InsertLines n + 4, vbNewLine
            .InsertLines n + 5, "End Sub"
        End With
    End Sub
    Last edited by neallp31; 10-25-2013 at 03:25 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Create a command button with code with a command button
    By jakara in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2013, 01:28 PM
  2. Code for a master command button to change the backcolor of multiple command buttons?
    By panttherm5 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-16-2012, 10:11 PM
  3. [SOLVED] vba code, command bar, command bar button, one button works but not two
    By amazingg64 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2012, 09:55 AM
  4. Assigning macros to a command button
    By Impartial Derivative in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2011, 02:13 PM
  5. Help in assigning a command button
    By Fybo in forum Excel General
    Replies: 1
    Last Post: 09-19-2005, 03:05 PM

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