How to create user forms in Office Scripts (4 ways)

One of the biggest complaints about Office Scripts is the lack of UserForms. UserForms exist in VBA and display an additional window for collecting user input. Unfortunately, there is no equivalent feature in Office Scripts. However, there are other methods to obtain user inputs that do work with Office Scripts. So, in this post, we are looking at how to create user forms in Office Scripts.

NOTE: I’m not suggesting that Office Scripts are as feature-rich as VBA in this area, but I want to demonstrate that Office Scripts are not as limited as many might think.

We are looking at this area in 4 ways:

• Shape user forms – using shapes on a worksheet to create the appearance of a user form
• Range user forms – using hidden ranges on a worksheet that can be unhidden to display a user form
• Worksheet user forms – using additional worksheets to operate as a user form
• Dynamic user forms – creating shape, range, or worksheet user forms on the fly with an Office Script

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch the video on YouTube.

Examples

All the examples in this post follow a similar pattern.

There are three buttons:

• Clicking the pencil icon reveals the user form
• Clicking Done enters the value into the cells and closes the user form
• Clicking Close closes the user form without doing anything with the values

The code for all the examples is included below.

Shape user forms

Shape user forms are just shape objects placed on the face of the worksheet. We control the visibility of the shapes through the getVisibility() method.

Take a look at the example below:

Shape properties

All the shapes are set with the following properties:

• Don’t move or size with cells
• Locked: checked
• Lock text: checked

The text boxes are the exception; these are set to:

• Don’t move or size with cells
• Locked: checked
• Lock text: unchecked

This means we can change the text even on a protected worksheet.

NOTE: Shapes can only accept text input, so their usage may be limited in many scenarios.

Office Script Button interactions

The Office Script to control the user forms as possible.

ToggleShapeUserForm

The Pencil and the Close buttons run the following script.

function main(workbook: ExcelScript.Workbook) {

  //Get the sheet
  let ws = workbook.getActiveWorksheet()

  //Get the list of shapes in the form
  let shpList = "frm1Box|frm1Name|frm1LoveScripts|frm1TextName|frm1TextLoveScripts|frm1BtnClose|frm1BtnDone"
  
  //Get the lists of shape names and cells
  let inputList = "frm1TextName|frm1TextLoveScripts"
  let inputRange = "C3|C5"

  //Split the list into an array
  let shpListArr = shpList.split("|")
  let inputListArr = inputList.split("|")
  let inputRangeArr = inputRange.split("|")

  //Test the first shape to see if visible
  let isVisible = ws.getShape(shpListArr[0]).getVisible()

  //Loop through all shapes and toggle visibility
  for (let i = 0; i < shpListArr.length; i++) {
    ws.getShape(shpListArr[i]).setVisible(!isVisible)
  }

  //Loop through shapes and set default values
  for (let i = 0; i < inputListArr.length; i++) {
    ws.getShape(inputListArr[i]).getTextFrame().getTextRange().setText(ws.getRange(inputRangeArr[i]).getValue())
  }

}

The comments in the code provide more detail about each section. To add more shapes to the script, we only need to change the shpList, inputList, and inputRange strings.

AcceptShapeUserForm

The Done button runs the following script.

function main(workbook: ExcelScript.Workbook) {

  //Get the sheet
  let ws = workbook.getActiveWorksheet()

  //Get the list of shapes in the form
  let shpList = "frm1Box|frm1Name|frm1LoveScripts|frm1TextName|frm1TextLoveScripts|frm1BtnClose|frm1BtnDone"

  //Get the lists of shape names and cells
  let inputList = "frm1TextName|frm1TextLoveScripts"
  let inputRange = "C3|C5"

  //Split the list into an array
  let shpListArr = shpList.split("|")
  let inputListArr = inputList.split("|")
  let inputRangeArr = inputRange.split("|")

  //Test the first shape to see if visible
  let isVisible = ws.getShape(shpListArr[0]).getVisible()

  //Loop through all shapes and toggle visibility
  for (let i = 0; i < shpListArr.length; i++) {
    ws.getShape(shpListArr[i]).setVisible(!isVisible)
  }

  //Return the value to the protected cell
  ws.getProtection().unprotect()
  for (let i = 0; i < inputListArr.length; i++) {
    ws.getRange(inputRangeArr[i]).setValue(ws.getShape(inputListArr[i]).getTextFrame().getTextRange().getText())
  }
  ws.getProtection().protect()

}

Range user forms

Range user forms are cells on the worksheet initially set to hidden. Once a user clicks a button, the cells are revealed to show an area to collect user input.

Range properties

For this scenario, we need to set the Lock property of the cells correctly:

• Input cells: unlocked
• All other cells: locked

This method hides or unhides columns on a protected worksheet. This issue is that if a column contains any locked cells, we cannot hide or unhide the entire column. Therefore, unfortunately, we need to:

• Unprotect the worksheet
• Hide/Unhide range
• Protect the worksheet

It’s not ideal, but it works.

Office Script Button interactions

The Office Scripts to control the user forms are:

ToggleRangeUserForm

The Pencil and the Close buttons run the following script.

function main(workbook: ExcelScript.Workbook) {

  //Get the sheet
  let ws = workbook.getActiveWorksheet()

  //The range of cells containing the user form
  let frmRange = "H2:K9"

  //Get the lists of input and target cells
  let inputRng = "J3|J5"
  let targetRng = "C3|C5"

  //Split the list into an array
  let inputRngArr = inputRng.split("|")
  let targetRngArr = targetRng.split("|")

  //Test if range is visible
  let isVisible = !ws.getRange(frmRange).getColumnHidden()

  //Hide or unhide the range
  ws.getProtection().unprotect()
  ws.getRange(frmRange).setColumnHidden(isVisible)
  ws.getProtection().protect()

  //Set the default values
  if (isVisible) {
    ws.getRange("C3").select()
  } else {
    ws.getRange("J3").select()
    for (let i = 0; i < inputRngArr.length; i++) {
      ws.getRange(inputRngArr[i]).setValue(ws.getRange(targetRngArr[i]).getValue())
    }
  }

}

The comments in the code provide more details about each section. To add more shapes to the script, we only need to change the inputList and inputRange strings.

AcceptRangeUserForm

The Done button runs the following script.

function main(workbook: ExcelScript.Workbook) {

  //Get the sheet
  let ws = workbook.getActiveWorksheet()

  //The range of cells containing the user form
  let frmRange = "H2:K9"

  //Get the lists of input and target cells
  let inputRng = "J3|J5"
  let targetRng = "C3|C5"

  //Split the list into an array
  let inputRngArr = inputRng.split("|")
  let targetRngArr = targetRng.split("|")
  
  //Test if range is visible
  let isVisible = !ws.getRange(frmRange).getColumnHidden()

  //Unprotect the worksheet
  ws.getProtection().unprotect()

  //Return the values ot the cells
  for (let i = 0; i < inputRngArr.length ; i++) {
    ws.getRange(targetRngArr[i]).setValue(ws.getRange(inputRngArr[i]).getValue())
  }

  //Hide the cells
  ws.getRange(targetRngArr[0]).select()
  ws.getRange(frmRange).setColumnHidden(isVisible)
  
  //Protect the worksheet
  ws.getProtection().protect()

}

Worksheet user forms

Worksheet user forms change the visibility of a worksheet. That worksheet contains the input cells to collect user input.

Worksheet properties

In the code examples for this scenario, I have set the worksheet as very hidden (so it doesn’t appear in the list of worksheets). But depending on your scenario, you may decide to set it as hidden.

If workbook protection has been applied, we need to:

• Unprotect the workbook
• Hide/unhide the worksheet
• Protect the workbook

I prefer not to use unprotect and re-protect method, but sometimes that’s what we’ve got to do.

Office Script Button interactions

The Office Scripts to control the user forms are:

ToggleWorksheetUserForm

The Pencil and the Close buttons run the following script.

function main(workbook: ExcelScript.Workbook) {

  //Get the worksheets
  let ws = workbook.getWorksheet("Start")
  let frmWs = workbook.getWorksheet("User Form")

  //Get the lists of input and target cells
  let inputRng = "D3|D5"
  let targetRng = "C3|C5"

  //Split the list into an array
  let inputRngArr = inputRng.split("|")
  let targetRngArr = targetRng.split("|")

  //Test if worksheet is visible
  let isVisible = frmWs.getVisibility()

  //Toggle the sheet visibility
  if (isVisible === "Visible") {
    frmWs.setVisibility(ExcelScript.SheetVisibility.veryHidden)
    ws.activate
  } else {
    frmWs.setVisibility(ExcelScript.SheetVisibility.visible)
    frmWs.activate()
    frmWs.getRange("D3").select()

    //Get the default values
    for (let i = 0; i < inputRngArr.length; i++) {
      frmWs.getRange(inputRngArr[i]).setValue(ws.getRange(targetRngArr[i]).getValue())
    }

  }

}

The comments in the code provide more details about each section. To add more cells to the script, we only need to change the inputList and inputRange strings.

AcceptWorksheeteUserForm

The Done button runs the following script.

function main(workbook: ExcelScript.Workbook) {

  //Get the worksheets
  let ws = workbook.getWorksheet("Start")
  let frmWs = workbook.getWorksheet("User Form")

  //Get the lists of input and target cells
  let inputRng = "D3|D5"
  let targetRng = "C3|C5"

  //Split the list into an array
  let inputRngArr = inputRng.split("|")
  let targetRngArr = targetRng.split("|")

  //Unprotect sheet
  ws.getProtection().unprotect()

  //Return values back to cells
  for (let i = 0; i < inputRngArr.length; i++) {
    ws.getRange(targetRngArr[i]).setValue(frmWs.getRange(inputRngArr[i]).getValue())
  }

  //Activate and protect the worksheet
  ws.activate()
  ws.getProtection().protect()

  //Hide the user form
  frmWs.setVisibility(ExcelScript.SheetVisibility.veryHidden)

}

Dynamic user forms

The final type of Office Script user form is a dynamic form. This can be of a shape, range, or worksheet type noted above. However, these are created on the fly by the script.

We can’t currently create an Office Script connected button on the fly. Therefore, the button must exist in advance and then be copied or moved or the generated user form.

Conclusion

While Office Scripts are not as powerful as VBA, we can still create similar interactivity as User Forms in Office Scripts. In this post, we used Shapes, Ranges, and Worksheets as user forms. Can you think of any others? If so, let us know in the comments below.

Related Posts

• How to create nice looking Office Scripts button (3 ways)
• How to run Power Automate from Excel with Office Scripts or VBA
• Use Office Scripts with Power Automate