CODEWITHSUNDEEP
pythonpython-3.xvbapywin32xlwings
renormalization
renormalization

Reputation: 13

How to add buttons for macro by using xlwings or pywin32

I'd like to know how to add buttons for macro by using xlwings or pywin32.

I found the way for shape objects(execute test("shape")), but test("button") failed because of no-attribute error in sheet1.api.Buttons.Add.

I simply implemented following because when I add a button manually in excel recording macro, I found ActiveSheet.Buttons.Add(288, 44.25, 151.5, 32.25).Select in VBA editor.

import xlwings as xw
def test_button(obj_type):
    
    wb = xw.books.add()
    wb.save("test.xlsm")
    
    sheet1 = wb.sheets["Sheet1"]
    
    if obj_type == "shape":
        # Add Shape
        sheet1.api.Shapes.AddShape(1, 100, 50, 150, 30)
        
        shape_names = []
        for shape in sheet1.shapes:
            if shape.name not in shape_names:
                shape_names.append(shape.name)
                shape.characters.api.Text = "Shape Name = {}".format(shape.name)
                shape.api.OnAction = "sample_sub"
        print("shape names list:")
        print(shape_names)
    elif obj_type == "button":
        button = sheet1.api.Buttons.Add(288, 44.25, 151.5, 32.25) # FIXME
        button.api.OnAction = "sample_sub" # FIXME
        button.api.Text = "sample button" # FIXME
    else:
        raise ValueError("Invalid obj_type : {}".format(obj_type))
    
    return wb

sample_sub is defined by:

@xw.sub
def sample_sub():
    wb = xw.Book.caller()
    sheet1 = wb.sheets["Sheet1"]
    sheet1.range("A1").value = "This is a test message."

Upvotes: 0

Views: 917

Answers (1)

newbie
newbie

Reputation: 311

According to MS documentation, there is no object named Buttons. You can create a button using FormControl, e.g. sheet.api.Shapes.AddFormControl(0,1,1,1,1), See xlformcontrol enumeration.

Upvotes: 0

Related Questions