J
Jenny B.
Good Afternoon Rick,
I'm currently using your below VB Bar Control to display the current time in
my spreadsheet (super idea by the way).
I'm wondering if there is a Macro I could add to this to change the current
time to accommodate other time zones. I'd like the other time zone to
continually increment just like the previously noted Bar Control if possible.
Thank you for your thoughts and appreciate any help achieving this task -
Jenny B.
________________________________________________________
You might consider adding a Microsoft Status Bar Control to your worksheet,
configure it with 2 panels and set their styles to 6-sbrDate and 5-sbrTime.
The time and date will automatically update with no intervention required.
Here is how I did this on my test worksheet (using Excel 2003). If you don't
already have the Visual Basic Toolbar visible, make it so (select
View/Toolbars/VisualBasic from Excel's menu). Then, click the icon on that
toolbar that puts it into Design Mode; then click the icon for the Control
Toolbox. Now click the bottom right icon on the Toolbox (it's tooltip says
"More Controls"). Click on the Microsoft Status Bar Control 6.0 item in the
list to activate it and then click/drag an area on the worksheet to place
the Status Bar there. I would delay resizing it until you add the panels
with the date and time (each of which will have to be resized according to
the font being used). Okay, double-click on the Status Bar you just placed
(make sure you are still in Design Mode). That will take you to the VB
editor with the Status Bar control already selected in the Properties Box.
Click on the (Custom) entry in the Properties Box and then on the button
with 3 dots that appears. This take you to the Property Page for the
control. Click on the Panels tab and then click on the Insert Panel button
to add 2 panels on the Status Bar. Set the Index to 1 and change the Style
drop down to 6-sbrDate. Now set the Index to 2 and change the Style drop
down to 5-sbrTime. At this point, you will have play around with the
Minimum/Actual Width fields for each panel to size them to display all of
their text. Once you have done that, you can resize the actual Status Bar
back on the worksheet to at least a width large enough to display both
panels. For aesthetics, you can get rid of the borders for each panel by
changing their Bevel properties to 0-sbrNoBevel. Finally, click the VB icon
you originally clicked to exit Design Mode. The Status Bar should continue
to update itself automatically from here on out.
Rick
I'm currently using your below VB Bar Control to display the current time in
my spreadsheet (super idea by the way).
I'm wondering if there is a Macro I could add to this to change the current
time to accommodate other time zones. I'd like the other time zone to
continually increment just like the previously noted Bar Control if possible.
Thank you for your thoughts and appreciate any help achieving this task -
Jenny B.
________________________________________________________
You might consider adding a Microsoft Status Bar Control to your worksheet,
configure it with 2 panels and set their styles to 6-sbrDate and 5-sbrTime.
The time and date will automatically update with no intervention required.
Here is how I did this on my test worksheet (using Excel 2003). If you don't
already have the Visual Basic Toolbar visible, make it so (select
View/Toolbars/VisualBasic from Excel's menu). Then, click the icon on that
toolbar that puts it into Design Mode; then click the icon for the Control
Toolbox. Now click the bottom right icon on the Toolbox (it's tooltip says
"More Controls"). Click on the Microsoft Status Bar Control 6.0 item in the
list to activate it and then click/drag an area on the worksheet to place
the Status Bar there. I would delay resizing it until you add the panels
with the date and time (each of which will have to be resized according to
the font being used). Okay, double-click on the Status Bar you just placed
(make sure you are still in Design Mode). That will take you to the VB
editor with the Status Bar control already selected in the Properties Box.
Click on the (Custom) entry in the Properties Box and then on the button
with 3 dots that appears. This take you to the Property Page for the
control. Click on the Panels tab and then click on the Insert Panel button
to add 2 panels on the Status Bar. Set the Index to 1 and change the Style
drop down to 6-sbrDate. Now set the Index to 2 and change the Style drop
down to 5-sbrTime. At this point, you will have play around with the
Minimum/Actual Width fields for each panel to size them to display all of
their text. Once you have done that, you can resize the actual Status Bar
back on the worksheet to at least a width large enough to display both
panels. For aesthetics, you can get rid of the borders for each panel by
changing their Bevel properties to 0-sbrNoBevel. Finally, click the VB icon
you originally clicked to exit Design Mode. The Status Bar should continue
to update itself automatically from here on out.
Rick