Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
I have the following VB script which changes the color of the cell based on
its value. When the value changes, the color doesn't change at the moment. I need to double click on the cell, for the color to change. How can i make it refresh automatically? Is there a problem with the code? This is my code. Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:AQ120")) Is Nothing Then Select Case Target Case Is = "A" icolor = 38 Case Is = "B" icolor = 38 Case Is = "C" icolor = 35 Case Is = "D" icolor = 36 Case Is = "E" icolor = 39 Case Is = "F" icolor = 35 Case Is = "G" icolor = 37 Case Is = "H" icolor = 34 Case Is = "I" icolor = 40 Case Is = "J" icolor = 40 Case Is = "K" icolor = 34 Case Is = "L" icolor = 34 Case Is = "M" icolor = 34 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
Hi,
Try following to correct : Application.EnableEvents = True HTH Cheers Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
Hi,
Where do i need to put this? In the code or as a seperate button?? Regads "Carim" wrote: Hi, Try following to correct : Application.EnableEvents = True HTH Cheers Carim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
In a separate macro, just to reinitiate your excel ...
As a matter of fact, your macro has always worked ... but an incident has very recently occured, while debuging another macro... for example .... it is this incident which has interrupted the EnableEvents mode ... HTH Carim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
A good place to put that line is in an auto exec macro like:
'======================== Sub Auto_Open() Application.EnableEvents = True End Sub ======================== ....the above will fire when the workbook is opened on any other user's machine. Rgds J Carim wrote: In a separate macro, just to reinitiate your excel ... As a matter of fact, your macro has always worked ... but an incident has very recently occured, while debuging another macro... for example ... it is this incident which has interrupted the EnableEvents mode ... HTH Carim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
sorry to say, but nothing seems to be working. Any other smart option?
"WhytheQ" wrote: A good place to put that line is in an auto exec macro like: '======================== Sub Auto_Open() Application.EnableEvents = True End Sub ======================== ....the above will fire when the workbook is opened on any other user's machine. Rgds J Carim wrote: In a separate macro, just to reinitiate your excel ... As a matter of fact, your macro has always worked ... but an incident has very recently occured, while debuging another macro... for example ... it is this incident which has interrupted the EnableEvents mode ... HTH Carim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
Close Excel and re-open it ...
Carim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
I did a real stupid code. Just recorded a macro for double clicking the cells
with look-up. And then copied the code for other cells. Now i run this macro to refresh. IS there a easier or smaller code to replace this. This code is really STUPID. Sub Refresh() ' ' Refresh Macro ' Macro recorded 13/10/2006 by XXX ' ' Range("C6").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C11").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C1124").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C29").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" End Sub "Carim" wrote: Close Excel and re-open it ... Carim |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
Sub Refresh()
Range("C6,C11,C1124,C29").FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" End Sub -- Regards, Tom Ogilvy "Rajula" wrote in message ... I did a real stupid code. Just recorded a macro for double clicking the cells with look-up. And then copied the code for other cells. Now i run this macro to refresh. IS there a easier or smaller code to replace this. This code is really STUPID. Sub Refresh() ' ' Refresh Macro ' Macro recorded 13/10/2006 by XXX ' ' Range("C6").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C11").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C1124").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C29").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" End Sub "Carim" wrote: Close Excel and re-open it ... Carim |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto refresh for VB script to take effect.
I have around 200 cells in the range, not just the 4.
Any other solution. "Tom Ogilvy" wrote: Sub Refresh() Range("C6,C11,C1124,C29").FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" End Sub -- Regards, Tom Ogilvy "Rajula" wrote in message ... I did a real stupid code. Just recorded a macro for double clicking the cells with look-up. And then copied the code for other cells. Now i run this macro to refresh. IS there a easier or smaller code to replace this. This code is really STUPID. Sub Refresh() ' ' Refresh Macro ' Macro recorded 13/10/2006 by XXX ' ' Range("C6").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C11").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C1124").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" Range("C29").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)" End Sub "Carim" wrote: Close Excel and re-open it ... Carim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Excel functions formula and auto refresh refresh | Excel Worksheet Functions | |||
How can I auto refresh a column that has an auto filter in place | Excel Discussion (Misc queries) | |||
How can I auto-refresh auto-filters when data changes? | Excel Worksheet Functions | |||
Auto-run macro after auto-query refresh (Excel2000,sr1) | Excel Programming | |||
Auto Date Script.. | Excel Discussion (Misc queries) |