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
Bookmarks