Cómo crear formularios de usuario en Office Scripts (4 formas)
Una de las mayores quejas sobre Office Scripts es la falta de UserForms. Los formularios de usuario existen en VBA y muestran una ventana adicional para recopilar la entrada del usuario. Desafortunadamente, no existe una característica equivalente en Office Scripts. Sin embargo, existen otros métodos para obtener entradas del usuario que funcionan con Office Scripts. Entonces, en esta publicación, veremos cómo crear formularios de usuario en Office Scripts.
NOTA: No estoy sugiriendo que los scripts de Office tengan tantas funciones como VBA en esta área, pero quiero demostrar que los scripts de Office no son tan limitados como muchos podrían pensar.
Estamos analizando esta área de 4 maneras:
- Dar forma a formularios de usuario : usar formas en una hoja de trabajo para crear la apariencia de un formulario de usuario
- Formularios de usuario de rango : uso de rangos ocultos en una hoja de trabajo que se pueden mostrar para mostrar un formulario de usuario
- Formularios de usuario de hoja de trabajo : uso de hojas de trabajo adicionales para operar como un formulario de usuario
- Formularios de usuario dinámicos : creación de formularios de usuario de formas, rangos u hojas de cálculo sobre la marcha con un script de Office
Tabla de contenido
- Ejemplos
- Dar forma a los formularios de usuario
- Propiedades de forma
- Interacciones del botón de Office Script
- Formularios de usuario de rango
- Propiedades de rango
- Interacciones del botón de Office Script
- Formularios de usuario de la hoja de trabajo
- Propiedades de la hoja de trabajo
- Interacciones del botón de Office Script
- Formularios de usuario dinámicos
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0132 Office Scripts UserForms.zip
Tener acceso
Todos los ejemplos de esta publicación siguen un patrón similar.
Hay tres botones:
- Al hacer clic en el icono de lápiz se muestra el formulario de usuario.
- Al hacer clic en Listo, se ingresa el valor en las celdas y se cierra el formulario de usuario.
- Al hacer clic en Cerrar se cierra el formulario de usuario sin hacer nada con los valores.
El código de todos los ejemplos se incluye a continuación.
Dar forma a los formularios de usuario
Los formularios de usuario de formas son simplemente objetos de formas colocados en la parte frontal de la hoja de trabajo. Controlamos la visibilidad de las formas mediante el método getVisibility().
Eche un vistazo al siguiente ejemplo:
Propiedades de forma
Todas las formas están configuradas con las siguientes propiedades:
- No mover ni dimensionar con celdas
- Bloqueado: marcado
- Texto de bloqueo: marcado
Los cuadros de texto son la excepción; estos están configurados para:
- No mover ni dimensionar con celdas
- Bloqueado: marcado
- Texto de bloqueo: sin marcar
Esto significa que podemos cambiar el texto incluso en una hoja de trabajo protegida.
NOTA: Las formas solo pueden aceptar entrada de texto, por lo que su uso puede estar limitado en muchos escenarios.
Interacciones del botón de Office Script
El Office Script para controlar los formularios de usuario como sea posible.
ToggleShapeUserForm
Los botones Lápiz y Cerrar ejecutan el siguiente 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()) }}Los comentarios en el código proporcionan más detalles sobre cada sección. Para agregar más formas al script, solo necesitamos cambiar las cadenas shpList , inputList y inputRange .
AceptarFormaUsuarioForm
El botón Listo ejecuta el siguiente 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()}Formularios de usuario de rango
Los formularios de usuario de rango son celdas de la hoja de trabajo inicialmente configuradas como ocultas. Una vez que un usuario hace clic en un botón, las celdas se revelan para mostrar un área para recopilar la entrada del usuario.
Propiedades de rango
Para este escenario, necesitamos configurar correctamente la propiedad Bloquear de las celdas:
- Celdas de entrada: desbloqueadas
- Todas las demás celdas: bloqueadas
Este método oculta o muestra columnas en una hoja de trabajo protegida. Este problema es que si una columna contiene celdas bloqueadas, no podemos ocultar ni mostrar toda la columna. Por lo tanto, lamentablemente necesitamos:
- Desproteger la hoja de trabajo
- Ocultar/Mostrar rango
- Proteger la hoja de trabajo
No es ideal, pero funciona.
Interacciones del botón de Office Script
Los Office Scripts para controlar los formularios de usuario son:
ToggleRangeUserForm
Los botones Lápiz y Cerrar ejecutan el siguiente 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()) } }}Los comentarios en el código proporcionan más detalles sobre cada sección. Para agregar más formas al script, solo necesitamos cambiar las cadenas inputList y inputRange .
Aceptar rango de formulario de usuario
El botón Listo ejecuta el siguiente 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()}
Formularios de usuario de la hoja de trabajo
Los formularios de usuario de la hoja de trabajo cambian la visibilidad de una hoja de trabajo. Esa hoja de trabajo contiene las celdas de entrada para recopilar la entrada del usuario.
Propiedades de la hoja de trabajo
En los ejemplos de código para este escenario, configuré la hoja de trabajo como muy oculta (para que no aparezca en la lista de hojas de trabajo). Pero dependiendo de tu situación, puedes decidir configurarlo como oculto.
Si se ha aplicado la protección del libro de trabajo, debemos:
- Desproteger el libro
- Ocultar/mostrar la hoja de trabajo
- Proteger el libro de trabajo
Prefiero no utilizar el método de desproteger y volver a proteger, pero a veces eso es lo que tenemos que hacer.
Interacciones del botón de Office Script
Los Office Scripts para controlar los formularios de usuario son:
ToggleWorksheetUserForm
Los botones Lápiz y Cerrar ejecutan el siguiente 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()) } }}Los comentarios en el código proporcionan más detalles sobre cada sección. Para agregar más celdas al script, solo necesitamos cambiar las cadenas inputList y inputRange .
Aceptar hoja de trabajo y formulario de usuario
El botón Listo ejecuta el siguiente 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)}Formularios de usuario dinámicos
El último tipo de formulario de usuario de Office Script es un formulario dinámico. Puede tener la forma, el rango o el tipo de hoja de trabajo mencionados anteriormente. Sin embargo, el script los crea sobre la marcha.
Actualmente no podemos crear un botón conectado a Office Script sobre la marcha. Por lo tanto, el botón debe existir de antemano y luego copiarse o moverse en el formulario de usuario generado.
Conclusión
Si bien los scripts de Office no son tan potentes como VBA, aún podemos crear una interactividad similar a la de los formularios de usuario en los scripts de Office. En esta publicación, utilizamos formas, rangos y hojas de trabajo como formularios de usuario. ¿Se te ocurre algún otro? Si es así, háganoslo saber en los comentarios a continuación.
Artículos Relacionados
- Cómo crear un bonito botón de Office Scripts (3 formas)
- Cómo ejecutar Power Automate desde Excel con Office Scripts o VBA
- Utilice scripts de Office con Power Automate
Deja una respuesta