martes, 1 de diciembre de 2009

Deshabilitar controles con VBA en Excel 2003.

Desarrollaremos en esta ocasión, empleando VBA, una macro que Habilite o Deshabilite los controles de Guardar y de Guardar como... en Excel 2003, condicionándolo a que algunas celdas estén completadas.
Esto es lo que nos pedía un lector:

...agradecería mucho me pudieran ayudar para conocer las opciones que tengo para poder condicionar el guardar un archivo en excel. Actualmente tengo una hoja en excel con celdas protegidas, no protegidas y validaciones en las mismas, para que el usuario no pueda modificar formato solo llenar con información, la intención es que no pueda guardar el archivo sin antes completar en su totalidad las celdas que debe llenar.


Supongamos un informe que requiere ser rellenado, en algunos campos, por un usuario final, el cual tiene Protegido la Hoja de trabajo, además de con celdas validadas; pretendemos que el usuario no pueda Guardar el fichero hasta que complete la información requerida. Algo sencillo y cómodo de explicar sería plantear una hoja de liquidación de los gastos de viaje, para lo que se requiere al menos identificar a la persona que ha realizado el viaje y el mes al que corresponden los gastos:


Nuestras celdas obligatorias para rellenar son la celda A1 y la celda A2, mientras ambas no estén completadas nuestra Macro no habilitará las opciones de Guardar o Guardar como...
El conjunto de macros a desarrollar, dentro de la ventana de código de la Hoja de trabajo concreta-Hoja1-, serán entonces:

'creamos una macro que deshabilite los controles de Guardar y Guardar como...
Private Sub Deshabilitar()
Dim cCtl As CommandBarControl

With CommandBars("file")
Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del menú archivo
If Not cCtl Is Nothing Then cCtl.Enabled = False
Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como... dentro del menú archivo
If Not cCtl Is Nothing Then cCtl.Enabled = False
End With

With CommandBars("document")
Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del Documento
If Not cCtl Is Nothing Then cCtl.Enabled = False
Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como.. dentro del Documento
If Not cCtl Is Nothing Then cCtl.Enabled = False
End With

With CommandBars("standard")
Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro de la barra de herramientas Standard
If Not cCtl Is Nothing Then cCtl.Enabled = False
End With

Set cCtl = Nothing
Application.OnKey "^g", "" 'deshabilita el método abreviado de guardar Ctrl+g
End Sub


Esta primera macro nos deshabilitará las opciones de Guardar y Guardar como... de los diferentes lugares en que puede aparecer dentro de Excel, ya que hemos indicado la instrucción Enabled =False en todos ellos.
La siguiente macro será:

'creamos una macro que habilite los controles de Guardar y Guardar como...
Private Sub Habilitar()
Dim cCtl As CommandBarControl

With CommandBars("file")
Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del menú archivo
If Not cCtl Is Nothing Then cCtl.Enabled = True
Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como... dentro del menú archivo
If Not cCtl Is Nothing Then cCtl.Enabled = True
End With

With CommandBars("document")
Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro del Documento
If Not cCtl Is Nothing Then cCtl.Enabled = True
Set cCtl = .FindControl(ID:=748) 'ID 748 para Guardar como.. dentro del Documento
If Not cCtl Is Nothing Then cCtl.Enabled = True
End With

With CommandBars("standard")
Set cCtl = .FindControl(ID:=3) 'ID 3 para Guardar dentro de la barra de herramientas Standard
If Not cCtl Is Nothing Then cCtl.Enabled = True
End With

Set cCtl = Nothing
Application.OnKey "^g" 'habilita el método abreviado de guardar Ctrl+g
End Sub


Esta macro nos habilitará las opciones de Guardar y Guardar como... de los diferentes lugares en que puede aparecer dentro de Excel, ya que hemos indicado la instrucción Enabled =True en todos ellos.
Lo último que nos queda por programar es una llamada a las dos anteriores, condicionada a que las celdas A1 y A2 tengan algún valor editado:

'creamos una macro que 'llame' a las dos anteriores siempre que cambie el contenido de las celdas A1 y A2
Private Sub Worksheet_Change(ByVal Target As Range)
'si A1 o A2 no tienen ningún valor introducido
If Range("A1").Value = "" Or Range("A2").Value = "" Then
'ejecuta la macro anterior llamada Deshabilitar
Deshabilitar
Else
'de lo contrario ejecuta la macro Habilitar
Habilitar
End If

End Sub


Probamos su funcionamiento y vemos que, efectivamente, cuando alguno de los valores exigidos no está completado las opciones de Guardar y Guardar como... están deshabilitadas:

22 comentarios:

  1. y al momento de cerrar, que manda la opcion de desea guardar los cambios.. habra alguna manera de de evitar que le puedan dar que si ???

    ResponderEliminar
  2. Hola,
    lo puedes hacer también desde el Editor de VBA.
    Si no quieres que pregunte y que ademas no considere los ultimos cambios, entonces deberás hacer lo siguiente:
    En el editor, en la ventanita de 'Explorador de proyectos, haz doble click en "ThisWorkbook"; en la ventana en blanco de código que aparece escribe este código:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Saved = True
    End Sub
    Con esto deberías desactivar la opción de 'Guardar cambios'.
    combinándolo con el código de la entrada seguro conseguiras lo que quieres.
    Slds

    ResponderEliminar
  3. Hola,
    hay algun código que me permita inhabilitar Guardar como en el menú Archivo para las hojas de gráficos (no una hoja de cálculo con gráfico sino las hojas que son gráficas). Queda inhabilitado el botón de la barra de herramientas pero no el comando Guardar como del menú Archivo.
    Gracias.
    PD: tampoco me inhabilita el menú Herramientas.

    ResponderEliminar
  4. Habría quue encontrar el ID que ejecuta el 'Guardar como' pero en una hoja de gráfico...
    Buscaré a ver si encuentro el código en cuestión.
    Slds

    ResponderEliminar
  5. HOLA AYUDENME
    COMO ACTIVO OTRA VEZ ESTOS COMANDOS
    GRACIAS
    ROCIOCSANCHEZM@HOTMAIL.COM

    ResponderEliminar
  6. Hola Rocio,
    en la parte de abajo de este mismo post, encontrarás el código necesario para habilitar estos comandos... si bien, debes tener claro que estos códigos eran explicitamente para la versión de Excel 2003.
    Slds

    ResponderEliminar
  7. Me dicen por favor como y dónde se colocan los códigos paso a paso. Gracias

    ResponderEliminar
    Respuestas
    1. Hola, como estás?.
      Recuerda que esto es sólo válido para Excel 2003 !!!.

      El lugar está explicado en el post, debes incluirlo en el explorador de VBA en la Hoja concreta que vaya asociada la condición.
      Un cordial saludo

      Eliminar
  8. Hola como puedo deshabilitar la barra de menus de excel incluido el menu de archivo?

    ResponderEliminar
    Respuestas
    1. Hola que tal!
      necesitaría saber en que versíon trabajas... si fuera posible hacer tal cosa, nunca sería en versiones 2007 +
      Slds

      Eliminar
  9. Buenas tengo la siguiente linea como hago para que tome todas la extensiones de libros

    archivo = archivo & Format(CDate(DTPicker1.Value), "dd-mm-yyyy") & ".xlsx"

    Gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola,
      no entiendo a que te refieres... has creado una variable como Cadena de Texto de una variable 'archivo' junto a una fecha con formato y al final la extensión .xlsx
      Esa es la extensión del Libro que has definido... ¿pretendes recorrer todas las posibles extensiones existentes de Excel???

      Prueba con ... & ".xls*" quizá con el comodin lo admita.
      Slds

      Eliminar
    2. Buenas :
      Si hay date time picker que al elegir una fecha
      Ej 06-11-2013
      Hay un libro que se llama 06-11-2013 y lo abre!! Siempre y cuando se con la extension xlsx ..
      La pregunta como se puede hacer para que sea cual sea la extension de todas formas abra el archivo. por que puede haber un libro que se llame 06-11-2013.xlsx o uno q se llame 06-11-2013.xls
      Por favor
      Graias..

      Eliminar
    3. No me resulto ocupando el comodin!!

      Eliminar
    4. Bien.. entonces simplemente no le pongas extensión al final
      archivo = archivo & Format(CDate(DTPicker1.Value), "dd-mm-yyyy")

      te abrirá el fichero con la extensión que tenga...
      Saludos

      Eliminar
    5. Buenas sabe que no resulta al dejarlo sin la extension
      Esta es la linea
      archivo = ThisWorkbook.Path
      If Right(archivo, 1) <> Application.PathSeparator Then archivo = archivo & Application.PathSeparator
      archivo = archivo & sFecha & ".xlsx"
      Saludos

      Eliminar
    6. Hola,
      tendría que ver el código completo, por que en las pruebas que hice antes de responderte funcionaba y abría perfectamente sin añadir la extendión.
      Verifica las variable sy cómo construyes ese 'archivo'

      Slds

      Eliminar
    7. Sub GetImportaDatos(sFecha As String)
      Dim archivo As String
      Dim appExcel As Object
      Dim hoja As String
      Dim hojas As String
      Dim extension As String
      Dim wb As Object
      Dim hj As Object
      Dim h As Object
      Dim fecha As Double
      Dim componente As Variant
      Dim i As Long
      Dim cierre As Boolean
      Dim uFil As Long
      Dim xFil As Long
      Dim iFil As Long
      Dim iCol As Long
      Dim xCol As Long
      Dim c As Range
      Dim d As Range
      Dim dato As String
      Dim nCel As String
      archivo = ThisWorkbook.Path 'Path de la carperta donde esta el libro a tomar los datos
      If Right(archivo, 1) <> Application.PathSeparator Then archivo = archivo & Application.PathSeparator
      archivo = archivo & sFecha & ".xlsx" 'nombre del libro a tomar los datos

      With CreateObject("Scripting.FileSystemObject")
      If Not .FileExists(archivo) Then
      If MsgBox("El archivo " & vbNewLine & archivo & vbNewLine & _
      "no fue localizado comprueba que no se renombro o cambio de ubicación" & vbNewLine & _
      "¿Deseas realizar la selección del archivo manualmente?", _
      vbCritical + vbYesNo, Application.OrganizationName) = vbNo Then
      Exit Sub

      Eliminar
    8. es parte del codigo

      Eliminar
    9. Hola,
      como te decía verifica como contruyes la variable 'archivo' y en especial qué genera 'sfecha'
      Si quieres puedes enviarme el código completo a
      excelforo@gmail.com

      Slds

      Eliminar
    10. Muchas gracias ya se soluciono el tema
      Saludos

      Eliminar
    11. ¿cuál era el problema...??, con qué tenía que ver???
      Saludos

      Eliminar