|
|
|
|
|
|
Excel
|
Updated StatusBar Message From Online Workbook
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002
|
Submitted by:
|
Justinlabenne
|
Description:
|
Links to an Excel workbook on a website obtaining an message stored in a cell, that is then delivered to the user via the statusbar.
|
Discussion:
|
An easy way to provide updated messages to the users of your Excel files. The message is scrolled 3 times across the statusbar after it is retrieved.
Can be used to provide info about a new version of the workbook being available or to give the user alerts about something relevent to the workbook. There are many possible uses that can be customized to suit the need.
|
Code:
|
instructions for use
|
Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub GetUpdatedMessage()
With Application
.ScreenUpdating = False
.EnableCancelKey = xlDisabled
On Error GoTo ErrorProcedure
.Workbooks.Open ("http://www.jlxl.net/JLXLVersionControl.xls")
Dim szNewMsg As String
szNewMsg = Range("A3").Value
.Workbooks("JLXLVersionControl.xls").Close savechanges:=False
.EnableCancelKey = xlInterrupt
.ScreenUpdating = True
End With
Dim lMsgLen As Long
lMsgLen = Len(szNewMsg)
Dim lCount As Long
Do
Dim i As Long
For i = 1 To lMsgLen * 2
Sleep 80&
DoEvents
Application.StatusBar = Mid$(Space(lMsgLen) & szNewMsg, i, lMsgLen)
Next i
lCount = lCount + 1
Loop Until lCount = 3
Application.StatusBar = Empty
Exit Sub
ErrorProcedure:
MsgBox Err.Description
Application.ScreenUpdating = True
Application.EnableCancelKey = xlInterrupt
End Sub
|
How to use:
|
- Open an Excel Workbook
- Copy the code
- Press Alt + F11 to open the Visual Basic Editor (VBE)
- Select INSERT > MODULE from the menubar
- Paste code into the right pane
- Press Alt+Q to return to Excel
- Save workbook before any other changes
|
Test the code:
|
- The example file runs on the Opening of the Workbook
-
- You can run at any time by going to
- TOOLS > MACRO > MACROS
- Selecting {GetUpdatedMessage} from the dialog box
- Pressing {Run}
- A message will soon scroll across the StatusBar
|
Sample File:
|
StatBar_UpdatedMsg.zip 11.05KB
|
Approved by mdmackillop
|
This entry has been viewed 177 times.
|
|