PDA

View Full Version : Solved: Flashing cell (sheet_activate)



joelle
02-05-2007, 12:43 PM
Hello VBA Gurus,

If your team is Colts - CHEERS again!! (mine is)
Unusual (& bright) start from CB heh? but who laughs last laughs best.

Anyhow, my VBA quest has little or none to do with football :)

Please what is some simple code to make a cell flash (C2) when a sheet is activated, say, sheet "vendor"
I saw similar posts but most have to do with a cell value -- I saw none with a particular sheet activation.

As always, many thanks for helping the newbie (I recently become a little dangerous, a tiny little so I still beg for your help).

Nee

xld
02-05-2007, 12:57 PM
In a standard code module




Public RunWhen As Double

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1

Next i

End With

End Sub

Public Sub Test()

End Sub

Sub StartBlink()
If Range("A1").Font.ColorIndex = 2 Then
Range("A1").Font.ColorIndex = xlColorIndexAutomatic
Else
Range("A1").Font.ColorIndex = 2
End If
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink", , True
End Sub

Sub StopBlink()
Range("A1").Font.ColorIndex = xlColorIndexAutomatic
Application.OnTime RunWhen, "StartBlink", , False
End Sub


In a worksheet code module




Private Sub Worksheet_Activate()
StartBlink
End Sub

Private Sub Worksheet_Deactivate()
StopBlink
End Sub

joelle
02-05-2007, 01:09 PM
Hello XLD,

Thats quick turnaround - TY!
But i'm sorry I dont understand what you mean by standard code module??

So, what I did was:
1)
Alt+F11
Double click on "this workbook"
Paste your 1st set of code
then
2)
Alt+F11
Double click on the sheet I want to have C2 flash
Paste in your 2nd set of code

3) I run it by clicking on the tab "vendor". I got a compile error saying:
"Sub or Function not defined"

Sorry, as a newbie I dont know what I did not understand correctly. Pls help.

Nee

xld
02-05-2007, 01:12 PM
1)
Alt+F11
Double click on "this workbook"
Paste your 1st set of code

This is the problem. You should go into the VBIDE (Alt-F11), and insert a new module, Insert>Module, and paste it there.

CBrine
02-05-2007, 02:17 PM
xld,
Was this supposed to be part of your post?


Public RunWhen As Double

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1

Next i

End With

End Sub

Public Sub Test()

End Sub

Can't figure out where it belongs? No calls in the start/stop blink code.

Cyberdude
02-05-2007, 02:25 PM
Bob, perhaps Nee doesn't understand the difference between a "standard code module" and the modules for specific sheets.
Sid

joelle
02-05-2007, 02:29 PM
Hello Board Admin,

May I close this post myself because I figure I wasnt at all clear about
the help I'm asking. I will need to start a new post with a new question.

Thanks.
Nee

joelle
02-05-2007, 03:40 PM
Hello Experts,

There's some confusion my part from an earlier post of mine (Flashing cell (ws_activate))
so I think I better close that post and start this one since this is a different vba that I need help with.

Within my workbook I have a sheet called "vendor". In cell C2 of this tab "vendor", the text there says, "Please enter your vendor ID". Obviously, people forget or bypass this msg all the time and leave it blank.

Now my goal is to flash this text (cell C2, tab "vendor") everytime the workbook is open and it remains flashing until they type over this cell.
Is this do-able. Please I'm a vba newbie so your help is greatly appreciated.

Nee

johnske
02-05-2007, 04:06 PM
Nee, there is no real need to start a new thread for this so I have merged these two threads.

The code for both requisites is substantially the same, there just needs to be a modification of XLDs code rather than someone coming along and having to re-invent the wheel because they didn't see the other thread...

xld
02-05-2007, 04:21 PM
xld,
Was this supposed to be part of your post?


Public RunWhen As Double

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow 'iLastRow to 1 Step -1

Next i

End With

End Sub

Public Sub Test()

End Sub
Can't figure out where it belongs? No calls in the start/stop blink code.

It is redundant, it is standard code in new workbooks for me. I meant to remove it, but omitted that step. Won't cause any problem though, just superfluous.

xld
02-05-2007, 04:28 PM
Option Explicit

Private Sub Workbook_Open()
StartBlink
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Sh.Name = TARGET_SHEET Then
If Not Intersect(Target, Me.Range(TARGET_CELL)) Is Nothing Then
With Target
StopBlink
End With
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code




Option Explicit

Public RunWhen As Double
Public Const TARGET_SHEET As String = "Vendor"
Public Const TARGET_CELL As String = "C2"


Sub StartBlink()
With Worksheets(TARGET_SHEET).Range(TARGET_CELL)
If .Font.ColorIndex = 2 Then
.Font.ColorIndex = xlColorIndexAutomatic
Else
.Font.ColorIndex = 2
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink", , True
End Sub

Sub StopBlink()
With Worksheets(TARGET_SHEET).Range(TARGET_CELL)
.Range("A1").Font.ColorIndex = xlColorIndexAutomatic
End With
Application.OnTime RunWhen, "StartBlink", , False
End Sub


Put this is a standard code module, Insert>Module, in theVBIDE.

joelle
02-05-2007, 04:54 PM
Hello XLD,

Thanks for coming back!!

Now the code works and the text in C2 blinks; however, the goal is for user to type over the flashing text (enter their ID) in C2. But as soon as I enter an ID, the ".Range" part from the line below is highlighted, and the error msg is "Method or Data member not found" :help

Nee

If Not Intersect(Target, Me.Range(TARGET_CELL)) Is Nothing Then

xld
02-05-2007, 05:53 PM
Nee,

Change the Me in that line to sh



If Not Intersect(Target, Sh.Range(TARGET_CELL)) Is Nothing Then

joelle
02-06-2007, 10:32 AM
Hello XLD,

Thanks for fixing that line of code and the text in C2 of the "vendor" sheet now blinks fine. Strangely enough though, this vba also creates an endless loop to open this workbook of mine.

Here is what happens:
I close the wb without saving -- it closes -- but immediately opens the same wb for me and asks if I want to enable the macro. If I enable macros, it does the same thing over again, meaning it lets me close with or without saving then immediately reopens the workbook for me.
To kill this loop, I have to say "disable macros", then close the wb, then its gone without coming back ????

I'm confused at this point. Sorry.

Nee

xld
02-06-2007, 11:02 AM
Nee,

This will hapen if you try to close the workbook having not changed cell C2. It is this that stops the next scheduled event; a scheduled event will happen even if you close the workbook. The code is predicated on the premise that C2 will be changed sometime in the session.

As this seems wrong, it is necessary to force a change. Add this code to the Thisworkbook code module


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
StopBlink
On Error GoTo 0
End Sub

joelle
02-06-2007, 11:36 AM
Hello XLD,

Your added code, Workbook_BeforeClose, does it nicely!!
Many many thanks again for your time, your patience, and generosity with a slowmo newbie.
Oh, and thanks for the precious tutorial -- now I can well differentiate what is a standard code module vs a workbook event !!! Made my day!

:bow:

Nee