Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change
HI, Is it possible with worksheet change to fill a vba array and retain
those values in the array with pasting back to a worksheet. What I meen is, Cell changes in worksheet, Change fired, my array = 1, end sub, back to worksheet, cell changes, Change fired no myarray = myarray + 1. At the moment I assume because of end sub the array is empty. Regards Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change
Private Sub Worksheet_Change(ByVal Target As Range)
Static MyArray() As Variant Static idex As Long If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler If Len(Trim(Range("A1"))) = 0 Then If Target.Address < "$A$1" Then If idex = 0 Then idex = 1 Else idex = idex + 1 End If MsgBox idex ReDim Preserve MyArray(1 To idex) MyArray(idex) = Target.Value End If ElseIf InStr(1, Range("A1"), "write", vbTextCompare) Then Application.EnableEvents = False Range("M1").Resize(idex).Value = _ Application.Transpose(MyArray) ElseIf InStr(1, Range("A1"), "clear", vbTextCompare) Then Columns("M").ClearContents Erase MyArray idex = 0 End If ErrHandler: Application.EnableEvents = True End Sub If you type "write" in A1 it writes the array in column M. If you type "clear" in A1, it clears the array. If A1 is blank, it records the entry made in the array. If you want the array accessible from other procedures, then you need to declare it as a public variable at the top of a general module (insert=module) along with idex and remove the declarations from the Change event. -- Regards, Tom Ogilvy " wrote: HI, Is it possible with worksheet change to fill a vba array and retain those values in the array with pasting back to a worksheet. What I meen is, Cell changes in worksheet, Change fired, my array = 1, end sub, back to worksheet, cell changes, Change fired no myarray = myarray + 1. At the moment I assume because of end sub the array is empty. Regards Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change
Sorry Tom but I dont know what the code does, or how to use it. Sorry
Regards Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change
Scrap that last post Tom. Many thanks for this. Ive sussed it, and its
brilliant. Thank for your time with. All the Best Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change the pivot chart automaticaly as values in the worksheet change | Excel Worksheet Functions | |||
change formula in a shared worksheet without losing change history | Excel Worksheet Functions | |||
Change in one Worksheet Activates Another Worksheet Change | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |