Good Morning All,

I'm currently using the below VBA provided to someone in an earlier thread
by Rick (great idea by the way).

My question is - would there be a way to have this set up to accommodate
different time zones? I use it in a current spreadsheet to show real time
that continually increments, but I also need it to calculate and show the
real times for different time zones. I have location in Australia, Japan and
Korea that I'm also looking to display with their current times.

I had tried this original with the NOW function (which I know does not
update until you press F9 or open the sheet), but that function would not
allow me to add or subtract hours to it to display the needed previously
mentioned countries in real time.

Thank you for your review and any thoughts or help is greatly appreciated

- 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


Was this post helpful to you?

Why should I

I'm not aware of the time function/macro given you but if it shows local time
then in another cell add/subtract the necessary hours from that to display
other time zones.

Mike

"Jenny B." wrote:


Good Morning All,

I'm currently using the below VBA provided to someone in an earlier thread
by Rick (great idea by the way).

My question is - would there be a way to have this set up to accommodate
different time zones? I use it in a current spreadsheet to show real time
that continually increments, but I also need it to calculate and show the
real times for different time zones. I have location in Australia, Japan and
Korea that I'm also looking to display with their current times.

I had tried this original with the NOW function (which I know does not
update until you press F9 or open the sheet), but that function would not
allow me to add or subtract hours to it to display the needed previously
mentioned countries in real time.

Thank you for your review and any thoughts or help is greatly appreciated

- 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


Was this post helpful to you?

Why should I

Good Morning Mike,

I am unable to use a simple cell - addition formula since this is a VBA
generated box (shape) that holds the time value not a cell.

The other method I used perviously - NOW() - is an actual formula that goes
in a cell. Unfortunately, It would not allow me to construct a simple
formula to add time to it to give me a different time value (gave me an error
and said I was using a "circular reference and that's why I went with the VBA
method instead).

Thanks for your thoughts and any additional information - Jenny B.


*** P.S. I'm not looking for static time. These will all increment just
like the VBA box, only they will show the different values for each country
(i.e; Australia will be 8 hrs ahead ...etc.)

"Jenny B." wrote:


Good Morning All,

I'm currently using the below VBA provided to someone in an earlier thread
by Rick (great idea by the way).

My question is - would there be a way to have this set up to accommodate
different time zones? I use it in a current spreadsheet to show real time
that continually increments, but I also need it to calculate and show the
real times for different time zones. I have location in Australia, Japan and
Korea that I'm also looking to display with their current times.

I had tried this original with the NOW function (which I know does not
update until you press F9 or open the sheet), but that function would not
allow me to add or subtract hours to it to display the needed previously
mentioned countries in real time.

Thank you for your review and any thoughts or help is greatly appreciated

- 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


Was this post helpful to you?

Why should I