Forums Últimos mensajes - Powered by IBM
 
Tags Palabras claves

Cambiar datos en hoja con eventos de otra

11/03/2006 - 04:40 por klomkbock | Informe spam
Ayuda Hacer una pregunta
Hola a todos

Estoy intentando que con eventos del modulo de una hoja me cambie
determinados datos de esta y de otra hoja del mismo libro. Lo he intentado
con SelctedChange y con Change a secas pero no hay manera. He intentado
adaptar el codigo que en un modulo normal funciona bien pero me topo con
algo que produce disparates,o, como ahora mismo, directamente no hace nada.

DISCULPAD, PERO HE DADO AL INTRO SIN QUERER Y CREO QUE YA LLEVO POR LO
MENOS DOS MENSAJES.

Continúo. Se trata de un libro con dos hojas. En el ejemplo, en la primera
hay una lista de articulos con referencia unica y los stocks de tienda,
almacen(en este ejemplo no interviene para nada), taras y variacion de
stock en tienda(columnas A; B; C; D; G), nombre de articulo y PVP (col. E;
F). En la segunda hoja, que haria la funcion de formulario, estarian los
campos: Ref, Vendidos, Devueltos, Baja, Articulos y PVP, columnas A; B; C;
D; E y F respectivamente.
Lo que intento es que al introducir en la columna A de la 2ª hoja la Ref
del articulo me ponga en las columnas E y F el nombre y el Pvp, y que a la
vez me reste al stock de tienda en la hoja 1 las cantidades vendidas y las
bajas, y que me añada a este stock de tienda las devueltas y al de taras
las bajas. En la hoja 2 (formulario) pueden repetirse los registro y
normalmente solo "usan" uno de los campos (vendido, devuelto y baja
normalmente solo).
Bueno no se si me he explicado ni si podreis ayudarme, pero por si acaso
hay va el ultimo codigo que he intentado y el que me funciona ejecutandolo
desde un modulo normal.

Muchas gracias de antemano.

Un saludo y hasta pronto.
Ivan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim prueba_ventas As Workbook
Dim Inventario As Worksheet, Ventas As Worksheet
Dim Celda As Range, Ref_I As Range
Set prueba_ventas = Application.Workbooks("prueba ventas.xls")
Set Inventario = prueba_ventas.Sheets("Hoja1")
Set Ventas = prueba_ventas.Sheets("Hoja2")
Set Ref_I = Inventario.Range("a2:a20")
Dim i As Integer
Application.ScreenUpdating = False
For Each Celda In Ref_I
With Celda
If Target.Column = i Then
Select Case i
Case 1
If .Value = Target Then _
Target.Offset(, 4) = .Offset(, 4) And _
Target.Offset(, 5) = .Offset(, 5)
Case 2
.Offset(, 1) = .Offset(, 1) - Target
.Offset(, 6) = -Target
Case 3
.Offset(, 1) = .Offset(, 1) + Target
.Offset(, 6) = .Offset(, 6) + Target
Case 4
.Offset(, 1) = .Offset(, 1) - Target
.Offset(, 3) = .Offset(, 3) + Target
End Select
End If
End With
Next Celda
End Sub



Sub CopiarDatos()
Set prueba_ventas = Application.Workbooks("prueba ventas.xls")
Set Inventario = prueba_ventas.Sheets("Hoja1")
Set Ventas = prueba_ventas.Sheets("Hoja2")
Set Inv1 = Inventario.Range("a2:a20")
Set Inv2 = Ventas.Range("a2:a20")
Application.ScreenUpdating = False

For Each Celda2 In Inv2
Ref2 = Celda2
For Each Celda1 In Inv1
Ref1 = Celda1
If Ref2 = Ref1 And IsNumeric(Celda2.Offset(, 1)) _
And IsNumeric(Celda2.Offset(, 3)) Then
With Celda2
.Offset(, 4) = Celda1.Offset(, 4)
.Offset(, 5) = Celda1.Offset(, 5)
End With
With Celda1

.Offset(, 1) = .Offset(, 1) - Celda2.Offset(, 1)
.Offset(, 6) = .Offset(, 6) - Celda2.Offset(, 1)
.Offset(, 1) = .Offset(, 1) + Celda2.Offset(, 2)
.Offset(, 6) = .Offset(, 6) + Celda2.Offset(, 2)
.Offset(, 1) = .Offset(, 1) - Celda2.Offset(, 3)
.Offset(, 6) = .Offset(, 6) - Celda2.Offset(, 3)
.Offset(, 3) = .Offset(, 3) + Celda2.Offset(, 3)
End With
End If
Next Celda1
Next Celda2


End Sub
email Siga el debateRespuesta 11 respuestasRespuesta Tengo una respuesta

Preguntas similare

Leer las respuestas

#6 Héctor Miguel
13/03/2006 - 05:59 | Informe spam
hola, Ivan !

... Supongo que jugar con los “eventos”... no debe ser demasiado bueno para la estabilidad mental…
... El codigo... creo que rellena todos los registros relacionados de la hoja 1 con el ultimo grupo de Target (o con el primero)
... lo he vuelto a estirar y he conseguido que funcione mas o menos bien, aunque supongo que el resultado
... deja mucho que desear y le sobran muchas cosas.
... me interesa borrar todos los registros de la Hoja2 antes de introducir los datos
... he creado un codigo con el evento open de ThisWorkbook... pero cuando le doy a vbSi del msgBox
... provoca un error 13 –no coinciden los tipos- en el case 1 del modulo de la hoja2, que no llego a descifrar
... Por otro lado he introducido mil y un bucles Do..Loop... pero de momento es la unica forma en que he conseguido que funcione
... he intentado que me avisara si la Ref. del articulo no existe, pero los “Changes” casi me vuelven loco otra vez (y al ordenador)...



1) el error 13 ocurre cuando se borran [en el evento '_open'] las celdas de la hoja2, debido al evento '_change' de la misma hoja :-(
la razon ? -> el codigo del evento '_change' NO esta 'preparado para manejar' cambios en multiples celdas [SOLO de una en una] :D

2) te sugiero el siguiente cambio en el evento '_open' [asumiendo que ambas hojas estan en el mismo libro] ;)
Private Sub Workbook_Open()
If MsgBox("¿Quieres borrar los registros anteriores?", _
vbYesNo, "Borrar registros") = vbNo Then Exit Sub
Application.EnableEvents = False
With Worksheets("Hoja2")
.Range(.Range("a2"), _
.Range("g2").End(xlDown)) _
.ClearContents
End With
Application.EnableEvents = True
End Sub

=> toma nota que ni siquiera es necesario 'seleccionar/activar/...' la hoja para 'operar' con sus objetos ;)

3) para 'el resto' del codigo en el evento '_change' de la hoja2 [me parece que]... es necesario conocer mas datos :-( [p.e.]
a) si los cambios [en la hoja2] 'suceden' de una en una celda... no 'veo' el sentido de manejar un bucle que 'actualize'...
-> cada celda 'relacionada' con el 'target' en la hoja 1 => For Each celda In <...>.Worksheets("Hoja1").Range("a2:a2000")
b) seria conveniente saber cuales son los 'titulos' de las columnas A:D y E:G [en ambas hojas] ya que segun se aprecia...
cambios en A modifican E y F mas cambios en B, C y D modifican G
c) ademas, las 'vueltas' a -posibles- modificaciones por los bucles Do...Loop -> que es lo que estamos 'actualizando' ???
d) es posible que pudieran surgir otros comentarios, pero sin poder hacer pruebas sobre datos 'reales'... [lo veo poco acertado] ;)

comentas lo que pudiera seguir en el tintero ?
saludos,
hector.

__ los codigos expuestos __
Private Sub Worksheet_Change(ByVal Target As Range)
Dim celda As Range
For Each celda In Workbooks("prueba ventas.xls") _
.Worksheets("Hoja1").Range("a2:a2000")
If Target.Row > 1 Then
With celda
Select Case Target.Column
Case 1
Do
If Target = 0 Then
Exit Do
ElseIf Target = "" Then
Exit Do
ElseIf .Value = Target Then
Target.Offset(, 4) = .Offset(, 4)
Target.Offset(, 5) = .Offset(, 5)
Exit Do
Else
Exit Do
End If
Loop While Target = .Value
Case 2
Do While Target.Offset(, -1) = .Value
If Target = 0 Then Exit Do
If Target = "" Then Exit Do
.Offset(, 1) = .Offset(, 1) - Target
Target.Offset(, 5) = 0 - Target
If Target.Offset(, -1) = .Value Then Exit Do
Loop
Case 3
Do While Target.Offset(, -2) = .Value
If Target = 0 Then Exit Do
If Target = "" Then Exit Do
.Offset(, 1) = .Offset(, 1) + Target
Target.Offset(, 4) = Target.Offset(, 4) + Target
If Target.Offset(, -2) = .Value Then Exit Do
Loop
Case 4
Do While Target.Offset(, -3) = .Value
If Target = 0 Then Exit Do
If Target = "" Then Exit Do
.Offset(, 1) = .Offset(, 1) - Target
.Offset(, 3) = .Offset(, 3) + Target
Target.Offset(, 3) = Target.Offset(, 3) - Target
If Target.Offset(, -3) = .Value Then Exit Do
Loop
End Select
End With
End If
Next celda
End Sub
Private Sub Workbook_Open()
Dim celda As Range, x As String
With Workbooks("prueba ventas.xls") _
.Worksheets("Hoja2")
.Activate
x = MsgBox("¿Quieres borrar los registros" & _
" anteriores?", vbYesNo, "Borrar registros")
If x = vbYes Then
.Range(Range("a2"), _
Range("g2").End(xlDown).Address) _
.ClearContents
.Range("a2").Select
Else
.Range("a2").Select
End If
End With
End Sub
Respuesta Responder a este mensaje
#7 Francisco Mty
14/03/2006 - 01:06 | Informe spam
Yo me perdi en algun tramo de la explicacion ya que partes de las respuestas
no las vi o de plano se excluyo al foro.

Me retiro del desarrollo, la solucion no solo esta en los eventos change.

Saludos
Respuesta Responder a este mensaje
#8 klomkbock
14/03/2006 - 01:38 | Informe spam
Hola Héctor Miguel, como siempre muchas gracias.

Te mando una explicacion del archivo un poco larga. Si la consideras
excesiva me gustaria que me lo dijeras para intentar evitarlo en lo
sucesivo.

Paso a describirte las hojas de trabajo, que efectivamente están en el
mismo libro (por cierto, supongo que con eventos de hoja también se podrá
“trabajar” sobre hojas de otros libros incluyendo su ruta,….¿incluso
aunque el de destino este cerrado?, ¿o supongo mal?). Bueno, a lo que iba:

En la hoja1 esta el listado de artículos de un comercio con las
existencias en el momento de trabajo. El campo principal, no repetido y si
es necesario ordenado, es la referencia(titulo de campo REF, columna A)
del artículo y es alfanumérica. En las columnas B, C y D están las
existencias del artículo en tienda(TIE), almacén(ALM) y taras(TAR)
respectivamente. Puede haber artículos sin ningún tipo de existencias. En
E y F están el nombre del articulo(ARTICULO) y el precio de venta al
publico(PVP).

La hoja2 se utilizaría a modo de formulario y corresponde a los artículos
vendidos cada día. En scolumnas A=REF. B=VEN: cantidad de artículos
vendidos de una ref. por venta, no por totales del día. Un artículo se
puede vender x veces al día, pero puede ser en ventas conjuntas o
individuales. CÞV: artículos devueltos por los clientes. DºJA: bajas
de artículos en la tienda por tara o rotura. E=ARTICULO ; F=PVP ; G=REP:
cantidad de artículos que se debería reponer a la tienda para que el stock
quedara igual que al inicio del día.

Hoja1: A1=REF ; B1=TIE ; C1=ALM ; D1=TAR ; E1=ARTICULO ; F1=PVP

Hoja2: A1=REF ; B1=VEN ; C1ÞV ; D1ºJA ; E1=ARTICULO ; F1=PVP ;G1=REP

Mi intención es que al introducir una REF en la hoja2 automáticamente me
coloque el ARTICULO y el PVP en su lugar, y que al introducir las unidades
en VEN, DEV y/o BAJA me reste(ven y baja) o añada(dev) del stock en TIE y
me añada en TAR las bajas, y que a la vez me diga cuantos artículos
debería llevar(REP) para tener el mismo stock inicial.

Bueno, en realidad estos campos son solo una parte de los que necesitaría
y su orden no es exactamente el real, pero los he ido poniendo sobre la
marcha mientras intentaba comprender los eventos de hoja y reflejan una
buena parte de mis necesidades en este caso. De hecho habría que incluir
otra hoja para las entradas de artículos en ALMACEN(pedidos recibidos).
Las bajas de art. podrían ser por tara o por otro motivo(regresarían al
stock de almacén).habría que comprobar si existen existencias en tienda, y
permitir o no su venta sin stock. Asimismo, y este quizás si conviniera
incluirlo en el ej., en la hoja2 debería haber un campo H1=RPTO (p.ej.)
que serian los artículos que realmente vamos a reponer a la tienda, previa
comprobación de existencias y posterior resta en stock almacén. también
habría que guardar copia de las ventas de cada día en el libro del mes
correspondiente, e imprimir un listado con los artículos para
reponer(RPTO) para buscarlos en el almacén real.
Como ves casi un TPV. Pero de momento me conformo con ir comprendiendo
poco a poco VBA de Excel.

En cuanto al uso del bucle For Each, probablemente tengas razón, sobre
todo porque puede ser muy arriesgado actualizar los stocks en tiempo real.
En caso de introducir mal los datos u otro tipo de errores quizás sea
complicado arreglarlo. podrían actualizarse los stocks con otro tipo de
evento una vez comprobada la validez de los datos. quizás BeforeSave o
BeforeClose. Incluso es probable que fuera mejor poner el código de la
hoja2 en un modulo normal y usar thisworkbook para las
automatizaciones???. también estoy probando los userforms, pero la cosa es
lenta y mi tiempo corto e intempestivo. El que mucho abarca poco aprieta,
y yo quizás intento abarcar demasiado.

En fin como siempre me temo que acabo siendo un plomo, pero te agradeceré
si me puedes dar algunas indicaciones.

Para remate dos últimas dudas. Se puede usar el método Undo con los
eventos(p.ej. Target.Application.Undo) , y de ser así que es lo que
desharía. Y por último ¿Cómo se vacían las asignaciones de las variables?

PD: si quieres puedo mandar un libro ej. pero no se como hacerlo. Tampoco
se como se rescatan los archivos que de vez en cuando se envían al foro
¿lenguaje XML?

Un saludo y hasta pronto
Ivan



Héctor Miguel wrote:

hola, Ivan !

> ... Supongo que jugar con los “eventos”... no debe ser demasiado bueno


para la estabilidad mental…
> ... El codigo... creo que rellena todos los registros relacionados de la


hoja 1 con el ultimo grupo de Target (o con el primero)
> ... lo he vuelto a estirar y he conseguido que funcione mas o menos bien,


aunque supongo que el resultado
> ... deja mucho que desear y le sobran muchas cosas.
> ... me interesa borrar todos los registros de la Hoja2 antes de introducir


los datos
> ... he creado un codigo con el evento open de ThisWorkbook... pero cuando


le doy a vbSi del msgBox
> ... provoca un error 13 –no coinciden los tipos- en el case 1 del modulo


de la hoja2, que no llego a descifrar
> ... Por otro lado he introducido mil y un bucles Do..Loop... pero de


momento es la unica forma en que he conseguido que funcione
> ... he intentado que me avisara si la Ref. del articulo no existe, pero


los “Changes” casi me vuelven loco otra vez (y al ordenador)...

1) el error 13 ocurre cuando se borran [en el evento '_open'] las celdas de


la hoja2, debido al evento '_change' de la misma hoja :-(
la razon ? -> el codigo del evento '_change' NO esta 'preparado para


manejar' cambios en multiples celdas [SOLO de una en una] :D

2) te sugiero el siguiente cambio en el evento '_open' [asumiendo que ambas


hojas estan en el mismo libro] ;)
Private Sub Workbook_Open()
If MsgBox("¿Quieres borrar los registros anteriores?", _
vbYesNo, "Borrar registros") = vbNo Then Exit Sub
Application.EnableEvents = False
With Worksheets("Hoja2")
.Range(.Range("a2"), _
.Range("g2").End(xlDown)) _
.ClearContents
End With
Application.EnableEvents = True
End Sub

=> toma nota que ni siquiera es necesario 'seleccionar/activar/...' la hoja


para 'operar' con sus objetos ;)

3) para 'el resto' del codigo en el evento '_change' de la hoja2 [me parece


que]... es necesario conocer mas datos :-( [p.e.]
a) si los cambios [en la hoja2] 'suceden' de una en una celda... no


'veo' el sentido de manejar un bucle que 'actualize'...
-> cada celda 'relacionada' con el 'target' en la hoja 1 => For Each


celda In <...>.Worksheets("Hoja1").Range("a2:a2000")
b) seria conveniente saber cuales son los 'titulos' de las columnas A:D


y E:G [en ambas hojas] ya que segun se aprecia...
cambios en A modifican E y F mas cambios en B, C y D modifican


G
c) ademas, las 'vueltas' a -posibles- modificaciones por los bucles


Do...Loop -> que es lo que estamos 'actualizando' ???
d) es posible que pudieran surgir otros comentarios, pero sin poder


hacer pruebas sobre datos 'reales'... [lo veo poco acertado] ;)

comentas lo que pudiera seguir en el tintero ?
saludos,
hector.
Respuesta Responder a este mensaje
#9 Héctor Miguel
14/03/2006 - 03:36 | Informe spam
hola, Ivan !

1) ya has detectado algunas de las [posibles] 'inconveniencias' de actualizar inventarios por eventos 'sin mas ni mas' :-((
[creo que] podrias 'echarle un vistazo' a un ejemplo [con macros] para adaptarlo a tus necesidades, en...
http://www.xl-logic.com/xl_files/fi...entory.zip

2) el metodo 'Undo' SOLAMENTE 'des-hace'... -> entradas del usuario [NO modificaciones por codigo] :-(

3) las asignaciones de variables [generalmente]... 'se mueren' al terminarse de ejecutar el codigo
-> excepto [p.e.] algunas en eventos o declaradas como 'estaticas' o si el codigo se termina 'inesperadamente' <= OJO

4) si tu tiempo es corto e intempestivo... por que no 'aprovechas' lo que ya esta escrito ? [punto 1] :))

si cualquier duda [o informacion adicional]... comentas ?
saludos,
hector.
=> ... explicacion del archivo un poco larga... Mi intencion es:
que al introducir una REF en la hoja2 automaticamente me coloque el ARTICULO y el PVP en su lugar
y que al introducir las unidades en VEN, DEV y/o BAJA me reste (ven y baja) o a#ada (dev) del stock en TIE
y me a#ada en TAR las bajas
y que a la vez me diga cuantos articulos deberia llevar (REP) para tener el mismo stock inicial...
... estos campos son solo una parte de los que necesitaria y su orden no es exactamente el real
... los he ido poniendo sobre la marcha mientras intentaba comprender los eventos de hoja [...]
... probablemente tengas razon... porque puede ser muy arriesgado actualizar los stocks en tiempo real.
... introducir mal los datos u otro tipo de errores quizas sea complicado arreglarlo.
... podrian actualizarse los stocks con otro tipo de evento una vez comprobada la validez de los datos [...]
... tambien estoy probando los userforms, pero la cosa es lenta y mi tiempo corto e intempestivo.
El que mucho abarca poco aprieta, y yo quizas intento abarcar demasiado.
... dos ultimas dudas. Se puede usar el metodo Undo con los eventos(p.ej. Target.Application.Undo)
y de ser asi que es lo que desharia.
Y por ultimo Como se vacian las asignaciones de las variables?
Respuesta Responder a este mensaje
#10 klomkbock
14/03/2006 - 04:05 | Informe spam
Hola Hector Miguel, muchas gracias

4) si tu tiempo es corto e intempestivo... por que no 'aprovechas' lo que ya


esta escrito ? [punto 1] :))

Realmente es corto e intempestivo pero en este caso no se trata de
necesidad, sino mas bien de vicio.

En cualquier caso espero poder seguir contando con tu inestimable ayuda y
la de todo el foro. Se aprende mas con vosotros que con cualquier manual.

Muchas gracias a todos por estar ahi.

un saludo y hasta pronto.
Ivan.
Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaSiguiente AnteriorRespuesta Tengo una respuesta
Search Busqueda sugerida