Excel VBA create a button beside cell

Question

for my question I would like to create a button beside the cell that is not NULL or "". The caption for the button must follow the value in the cell beside.

For example:

1. I typed 'EMPLOYEE' in Range("D3")
2. I want the macro to create a button named "EMPLOYEE" in the Range("C3")
3. However I want the macro to be dynamic so that every time I type in the value in the column 'D', the cell on the left side - C3 will comes out a button.

Therefore, I've figured out that I needed to code for the CommandButton manually is that right?

Nevertheless, million thanks in advance for all.

Answer 1

Try this out.

Public Sub Worksheet_Change(ByVal Target As Range)
    Dim col As Integer
    Dim row As Integer

    col = Target.Column
    row = Target.row

    If Not IsNull(Target.Value) And Not IsEmpty(Target.Value) Then
        Application.EnableEvents = False
        Buttons.Add Cells(row, col - 1).Left, Cells(row, col - 1).Top, Cells(row, col - 1).Width, Cells(row, col - 1).Height
        Application.EnableEvents = True
    End If
    End Sub

Open up the Developer Tab --> Visual Basic, double click "Sheet1", then paste this code in there. Test it by typing text in a cell on Sheet1 then moving away from that cell (e.g. by pressing Enter).

Answer 2

You may record a macro by adding a command button to see how it's created and then incorporate the fancy parts. Note on properties of OLE Command button object, pay more attention to them.

e.g. theButton.Name yet for caption is set via theButton.Object.Caption etc.

Here is a code snippet to get you going:-

Option Explicit

Sub createButtons()
Dim theButton As OLEObject
Dim rngRange As Range
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set rngRange = Sheets(2).Range("B2")

    For i = 0 To 9
        If rngRange.Offset(i, 0).Value <> "" Then
        With rngRange.Offset(i, 1)
            Set theButton = ActiveSheet.OLEObjects.Add _
                (ClassType:="Forms.CommandButton.1", _
                Left:=.Left, _
                Top:=.Top, _
                Height:=.Height, _
                Width:=.Width)

                theButton.Name = "cmd" & rngRange.Offset(i, 0).Value
                theButton.Object.Caption = rngRange.Offset(i, 0).Value

                '-- you may edit other properties such as word wrap, font etc..
      End With
      End If
    Next i

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Output: