CODEWITHSUNDEEP
exceloffice-js
Jean-Marc Flamand
Jean-Marc Flamand

Reputation: 989

Excel Office.js -add button on Workbook_Open() envent

I'm new on Office.js On my VBA add-in I use Workbook_Open() event that add add dynamically buttons over a cell location that perform function on worksheet. Did google search and get no tangible results for office.js. Question is it possible to add button over a cells and attach function with Office.js.

Upvotes: 3

Views: 1297

Answers (3)

Lex Sevenich
Lex Sevenich

Reputation: 1

Although this is a nice piece of code it doesn't address the problem that by clicking on the button you're actually selecting a cell and by that you lose the focus on maybe the cell/line/column you would like to perform the action on.

It would be better for Microsoft to add an alternative to the buttons that are now only available for VBA for JS.

Upvotes: 0

Gove
Gove

Reputation: 1794

While you cannot add buttons to a worksheet, you can format cells to look like buttons and make them run and Office-JS function when they are clicked. Here's what they look like

Cells formatted as buttons with Office-JS

Here's the code that builds them and makes them work

function build_buttons(){
  // format cells on the active sheet to look and act like buttons
  Excel.run(async function (context) {    
    const sheet = context.workbook.worksheets.getActiveWorksheet()

    // set column sizes for cells to look like buttons
    sheet.getRange("A1").format.columnWidth=10
    sheet.getRange("B1").format.columnWidth=50
    sheet.getRange("C1").format.columnWidth=10
    sheet.getRange("D1").format.columnWidth=50
    sheet.getRange("E1").format.columnWidth=10
    
    // set background color
     sheet.getRange("A1:E3").format.fill.color="khaki"
    
    // make cells look and act like buttons
    format_cell_as_button(sheet, "B2", "Button 1")
    format_cell_as_button(sheet, "D2", "Button 2")
    
    // set up sheet to respont to clicks
    sheet.onSingleClicked.add(click_handler)
    context.sync()
      
  })
}

function click_handler(event){
  // This function gets called every time a click happens 
  // on the sheet.  It decides which function to call based
  // on which cell received the click event
  
  switch(event.address){
    case "B2":
      button_1_click()
      break
    case "D2":
      button_2_click()
      break
    default:    
  }
}

function button_1_click(){
  // function that gets called when "B2" gets the click event
  Excel.run(async function (context) {    
    const sheet = context.workbook.worksheets.getActiveWorksheet()
    sheet.getRange("b4").values="Button 1 clicked"
    context.sync()
  })
}

function button_2_click(){
  // function that gets called when "D2" gets the click event
  Excel.run(async function (context) {    
    const sheet = context.workbook.worksheets.getActiveWorksheet()
    sheet.getRange("b4").values="Button 2 clicked"
    context.sync()
  })
}



function format_cell_as_button(sheet, cell_address, text){
  // configure a cell to look like a button
  // "sheet" must be a reference to a worksheet that has
  // context.sync() called on it after this function is
  // run.
  
  sheet.getRange(cell_address).format.horizontalAlignment = "Center"
  sheet.getRange(cell_address).values=text
  sheet.getRange(cell_address).format.fill.color="lightgrey"
  format_border(sheet.getRange(cell_address).format.borders, 
               ["EdgeBottom","EdgeRight"],"Continuous","darkGrey","thick")
  format_border(sheet.getRange(cell_address).format.borders, 
               ["EdgeTop","EdgeLeft"],"Continuous","whiteSmoke","thick")
}

function format_border(border_object, border_names, style, color, weight ){
  // border_object must have context.sync() called on it after
  // this function is run.
  for(const border of border_names){
    border_object.getItem(border).style=style
    border_object.getItem(border).color=color
    border_object.getItem(border).weight=weight
  }
}

This code is available at the following Gist:

Link to Gist with code

You can run and modify this code using the JavaScript Automation Development Environment (JADE) add-in for Excel. Just search for JADE in the add-store. Once installed, click "Import a Code Module" and paste this Gist ID: 055f3811ab7d0240a92df54523d493a9

Disclaimer: I wrote the JADE add-in

Upvotes: 2

Michael Saunders
Michael Saunders

Reputation: 2668

With Office.js it is not possible to add buttons on the spreadsheet surface itself. However, you can add buttons dynamically onto a task pane with HTML. Or you can declare buttons statically on the ribbon and contextual (right-click) menus.

Both of these solutions are described in the documentation on Office Add-in UI Elements: http://dev.office.com/docs/add-ins/design/ui-elements/ui-elements

Upvotes: 2

Related Questions