Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


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

UserForm with Office Scripts

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:

Office Script Shape User Forms

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
Shape Properties

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 - User Forms in Office Scripts

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 User Forms in Office Scripts

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


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *