Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculation Issues
Use the Worksheet_Change event, not calculation.
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1:H10" '<--- change to suit On Error GoTo ws_exit Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target 'your code End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click -- --- HTH Bob (change the xxxx to gmail if mailing direct) "PaulW" wrote in message ... I have a workbook that i want to update only when data is inputted. Witihin the macro i am moving data which is recalculating the sheet which then triggers the macro again which in effect loops the sheet. The two criteria i have are a date and then the trigger is when data is inputted. The aim is to move the inputted data from the input cell (always a particular column) to a new column. How can I get the sheet to only update with data input? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Calculation Issues
If you put the code in the sheet module as I said, you don't need to test
the sheet name. If you turn off events as I showed you, it will not re-trigger. Show us all the code, and tell us where you stored it. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "PaulW" wrote in message ... Thanks Bob, Here is the code that i've added into your example, unfortunatly it doesn't trigger at all now (I changed the range from H1:H10 to A1:AU100. Quite alot of this i'm trying to write from guesswork, and since i've been trying to modify it for a while its a bit messy. Basically, I have the information in 3 columns (not next to each other) and each of these 3 columns has a date. When the macro is triggered the active column needs to be 2, 8 or 14. else it exits the sub, else it assigns another variable to the next column. Then it checks if today is greater than the date for that column. If this cut off date has been reached it needs to move what has just been entered to the next column at the top, deleting where it was originally entered. Essentially, the dates for these columns represent deadlines, and when a new job is entered into a column a message needs to be shown showing that the deadline has already passed, and assign it to the next deadline. The main difficulty is that when the information is moved, with the initial worksheet_calculation then the sheet calculates again (I think) which triggers the macro to run again, and the cutoff date for that column is still invalid, resulting in a loop. If ActiveSheet.Name < "Manchester" Then Exit Sub rang = ActiveCell.Column roo = ActiveCell.Row Sheets("Manchester").Cells(roo - 1, rang).Select today = Range("B4").Value rang = ActiveCell.Column roo = ActiveCell.Row addres = ActiveCell.Address Valu = Sheets("Manchester").Cells(5, rang + 3).Value dat = Sheets("Manchester").Cells(6, rang + 1).Value dat2 = Sheets("Manchester").Cells(6, rang + 6).Value num = 0 If rang = 2 Then num = 8 If rang = 8 Then num = 14 If rang = 14 Then num = 2 If num = 0 Then Exit Sub sum1 = Sheets("Manchester").Cells(60, rang + 4).Value If sum1 = 1 Then Exit Sub If today dat Then MsgBox ("Manchester - The Cut off point has been reached for this slot. Please use the next available slot. (" & dat2 & ")") act = Range(addres).Value newc = Sheets("Manchester").Cells(10, num).Address Range(newc).Select With Range(newc) Value = act End With With Range(addres).ClearContents End With Else: End If End With End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtraction issues | Excel Worksheet Functions | |||
worksheet_calculation problem | Excel Programming | |||
Issues with VBA | Excel Programming | |||
C# VBA DLL issues | Excel Programming | |||
need help for several issues | Excel Programming |