Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alert
Hi, I have a data in which i enter time in a cell. I need a macro like when the cell's time is exceeding 45 mins form the system time the entire row should change into red. But when any data is entered in Cell E1 the alert needs to stop. M.No Date Time Place Outcome 3059934009 7/5/2009 15.03 P0 5LX In the above example the Time 15:03 is entered at real time i need an alert or the entire cell turns into red when the system time is 15:48 exactly after 45 mins of the job time(15:03). If the outcome data is filled in then the alert should get stop. Many thanks for the help -- Sathisc ------------------------------------------------------------------------ Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94274 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alert
Sathisc;337136 Wrote: Hi, I have a data in which i enter time in a cell. I need a macro like when the cell's time is exceeding 45 mins form the system time the entire row should change into red. But when any data is entered in Cell E1 the alert needs to stop. M.No Date Time Place Outcome 3059934009 7/5/2009 15.03 P0 5LX In the above example the Time 15:03 is entered at real time i need an alert or the entire cell turns into red when the system time is 15:48 exactly after 45 mins of the job time(15:03). If the outcome data is filled in then the alert should get stop. Many thanks for the helpYou would need to use the ontime method, starting the timer when the cell becomes active and ending the timer if the cell beomes filled or the row coloured red. It's not an exact science as the timer will restart should you move cells even if data hasn't been entered previously! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94274 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating timed alert for empty cell
Hi Simon, I am a beginner in Excel. Can you breif more on the ontime method. Cheers, Sathish -- Sathisc ------------------------------------------------------------------------ Sathisc's Profile: http://www.thecodecage.com/forumz/member.php?userid=187 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94274 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating timed alert for empty cell
Sathisc;337322 Wrote: Hi Simon, I am a beginner in Excel. Can you breif more on the ontime method. Cheers, SathishResearch the OnTime method, there's lots of info out there, however follow these instructions and you should be good to go: Paste this code in the worksheet code module that you are working with *How to Save a Worksheet Event Macro* 1. *Copy* the macro using *CTRL+C* keys. 2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab* for the Worksheet the macro will run on. 3. *Left Click* on *View Code* in the pop up menu. 4. *Paste* the macro code using *CTRL+V* 5. Make any custom changes to the macro if needed at this time. 6. *Save* the macro in your Workbook using *CTRL+S* Code: -------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim r2 As String r2 = Range("IV" & Range(MyRange).Row).End(xlToLeft).Address If Range(MyRange) < "" Then Range(MyRange & ":" & r2).Interior.ColorIndex = xlNone End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) MyRange = Target.Address If Target = "" Then GoTo Nxt If Target < "" Then Range(MyRange & ":" & Range("IV" & Range(MyRange).Row).End(xlToLeft).Address).Interio r.ColorIndex = xlNone End If Exit Sub Nxt: 'in this timeserial 0 = hrs, 0 = minutes, 0 = seconds it is set for 10 seconds at the moment Application.OnTime (Now + TimeSerial(0, 0, 10)), "ColourRow" Exit Sub End Sub -------------------- Next paste this in a standard code module: *How to add and run a Macro*1. *Copy* the macro below pressing the keys *CTRL+C* 2. Open your workbook 3. Press the keys *ALT+F11* to open the Visual Basic Editor 4. Press the keys *ALT+I* to activate the *Insert menu* 5. *Press M* to insert a *Standard Module* 6. *Paste* the code by pressing the keys *CTRL+V* 7. Make any custom changes to the macro if needed at this time. 8. *Save the Macro* by pressing the keys *CTRL+S* 9. Press the keys *ALT+Q* to exit the Editor, and return to Excel. *To Run the Macro...* To run the macro from Excel, open the workbook, and press *ALT+F8* to display the *Run Macro Dialog*. Double Click the macro's name to *Run* it. Code: -------------------- Public MyRange As String Sub ColourRow() Range(Activecell.Address & ":" & Range("IV" & Activecell.Row).End(xlToLeft).Address) _ .Interior.ColorIndex = 3 End Sub -------------------- The code should now do what you want, although i have set it to 10 seconds so you can see it working! -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=94274 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
alert | Excel Discussion (Misc queries) | |||
How to Create Sound Alert and Email Alert when Macro is Finish | Excel Programming | |||
How to Create Sound Alert and Email Alert when Macro is Finish | Excel Programming | |||
How to Create Sound Alert and Email Alert when Macro is Finished | Excel Programming | |||
Pop-up Alert Help | Excel Programming |