AMBITO DE UNA MACRO EN VARIOS LIBROS

[Josep]

Saludos a todos. Muy brevemente me presento como una persona ya jubilada que me entretiene y distrae programar macros en VBA para Excel.

El problema que trato de consultar en este post  es el siguiente:

En una macro dentro de un formulario intento acceder a una tabla en una hoja de su correspondiente libro , y no tengo problema alguno. El problema aparece cuando ejecuto la misma macro pero el libro activo -que no contiene ninguna hoja con la tabla que se intenta accesar- es otro diferente. Entonces obtengo el error siguiente : "Error en el metodo 'Select' del objeto 'Worsheet'. Creo que se produce porque el libro activoal ser otro, y no ppoder encontrar la Hoja que contiene la Tabla, la el error indicado.

Pongo el codigo que estoy utilizando.

Private Sub CBX_Activo_Change()
    Dim HojaDatos As Worksheet
    Dim Tabla As ListObject
    Set HojaDatos = Workbooks("Cartera de Valores.xlsm").Sheets("Listas")
    Set Tabla = HojaDatos.ListObjects("TablaActivos")
    HojaDatos.Select
    Tabla.DataBodyRange.Columns(1).Find(What:=Me.CBX_Activo.Value, LookAt:=xlWhole).Activate
    LBL_Nombre_Activo = ActiveCell.Offset(0, 1).Value
End Sub

Caso de precisar alguna informacion adicional, no duden en pedirmela.

Agradecido de antemano.

Saludos,

José Miguel.

[Antoni]

Has de evitar, en la medida de lo posible, el uso de Select y Activate, inténtalo así:

Private Sub CBX_Activo_Change()
    Dim HojaDatos As Worksheet
    Dim Tabla As ListObject
    Dim NombreActivo As Range
    Set HojaDatos = Workbooks("Cartera de Valores.xlsm").Sheets("Listas")
    Set Tabla = HojaDatos.ListObjects("TablaActivos")
    Set NombreActivo = Tabla.DataBodyRange.Columns(1).Find(What:=Me.CBX_Activo.Value, LookAt:=xlWhole)
    LBL_Nombre_Activo = NombreActivo.Offset(0, 1).Value
End Sub

Se da por supuesto, que el valor a buscar existe, de lo contrario dará error.

[Josep]

Hola Antoni. Gracias por tu ayuda. He probado el codigo y se detiene en la siguiente sentencia:

Set HojaDatos = Workbooks("Cartera de Valores.xlsm").Sheets("Listas")

Por lo que interpreto que no es capaz de asignar el Worksheet correctamente.

Respondiendo a tu ultimo comentario, te confirmo que si existe el valor seleccionado en el ComboBox, ya que previamente han sido cargado este desde la Tabla, todos los registros de la Columna 1, y simplemente se trata de localizarlo el valor que seleccionamos en el Combo, moviendose mediante el Offset una columna, y asi recoger en la Tabla el nombre completo del activo seleccionado desde el Combo.

Saludos y buenas noches.

José Miguel

[Antoni]

A ver si es esto, porqué no acabo de entender el problema, sin ver el archivo es difícil.

Private Sub CBX_Activo_Change()
    Dim HojaDatos As Worksheet
    Dim Tabla As ListObject
    Dim NombreActivo As Range
    Set HojaDatos = ThisWorkbook.Sheets("Listas")
    Set Tabla = HojaDatos.ListObjects("TablaActivos")
    Set NombreActivo = Tabla.DataBodyRange.Columns(1).Find(What:=Me.CBX_Activo.Value, LookAt:=xlWhole)
    LBL_Nombre_Activo = NombreActivo.Offset(0, 1).Value
End Sub

 

[Josep]

Hola Antoni. Saludos de nuevo.

Desafortunadamente, continua sin funcionar. ¿Existe la posibilidad de que pueda enviarte el archivo por privado ? Gracias.

José Miguel.

[Antoni]

Lo siento pero no contesto de forma privada.

[Josep]

Saludos de nuevo Antoni. No te preocupes .. no pasa nada. Intentare explicarte con mas detalle el origen del problema.

La rutina en cuestión se encarga de localizar un Tabla que buscará un registro coincidente y ademas existente, dentro de una Hoja alojada dentro de un Libro que a su vez contiene el codigo dentro de un objeto formulario. (Todo en una solo Libro, en este caso Libro1).

El problema no aparece -en mi rutina, pero si en la que tu me pasas-, si solo esta abierto el Libro 1 que es donde se aloja la Tabla a gestionar.

Si por el contrario existen mas libros abiertos y en concreto corro la macro, por ejemplo con el Libro3 activo, entonces se produce el error tanto en mi codigo como en el tuyo. Eso indica que no encuentra la Tabla porque no es el Libro adecuado(Libro 1).

En consecuencia y salvo mejor opinion tuya, no deberia de utilizarse el Objeto ThisWorkBook, salvo que solo existiera un libro o al menos el libro activo.

Yo utilizo las setenecias ...

    Dim HojaDatos As Worksheet
    Set HojaDatos = Workbooks("Cartera de Valores.xlsm").Sheets("Listas")

... que deberian ser suficientes para identifica el Libro y Hoja donde se ubica la Tabla en Libro1, pero no asi en cualquier otro Libro que este activo en ese momento. De ahi el metodo Select..

En cambio, si el libro activo es diferente a Libro1 y corro la macro, aparece el error en todas las rutinas. Creo que es, al menos en alguna de ellas, porque al utilizar el objeto "ThisWorkbook", laTabla no existe al ser un Libro dferente a Libro 1 y por eso no la encuentra.

La solucion creo que pasa por definir inequivcamente el Libro, Hoja y Tabla donde la rutina debe actuar, de tal manera que teniendo activo otro libro diferente al Libro1, se ejecute la macro sin incidencia alguna.

Espero y deseo haber explicado la problematica existente con el mayor detalle posible.

Recibe un cordial saludo y gracias por tu paciencia,

Saludos,

José Miguel.